MYSQL HELP Linking the Foreign Keys

Sergio45 · February 27, 2017

Having issues with creating Foreign Keys. How would I end up doing the linking of the FK with PHPMyAdmin?

I get errors when I do it using constraints.

Thanks for the Help!

Create database cardealerships;
use cardealerships;

CREATE TABLE tblDealerShip
(
    dealerShipId int(11) NOT NULL AUTO_INCREMENT,
    dealerShipName varchar (15) DEFAULT NULL,
    dealerShipState char(2) DEFAULT NULL,
    dealersName varchar(50) DEFAULT NULL,
    dealersPhoneNumber varchar (10) DEFAULT NULL,
    PRIMARY KEY (dealerShipId)
);
/*
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (1,'Texans Can Cars','West Virginia','Alicia C. Marinez','239-513-1597');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (2,'Americas Motorsports','Illinois','Dorothy S. Bates','425-437-5686');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (3,'Moose Island Marine','Texas','Alicia C. Marinez','620-386-9845');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (4,'Stadium Lincoln Mercury','New York','Sergio Ochoa','704-602-5603');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (5,'Road Track & Trail','Maine','Chuck Norris','618-975-2136');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (6,'James Motor Cars','Florida','Mike Bay','931-557-1481');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (7,'Thul Auto Stores','Arizona','Allen Woody','205-329-6108');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (8,'Don Roberto Motor Cars','Alaska','Jim Joe','919-625-2474');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (9,'A & W Truck Sales','Pennsylvania','Brittney Mouse','605-584-6291');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (10,'Texas Car','Minnesota','Zoey Dez','585-356-0780');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (11,'Car R Us','West Virginia','Joe Burger','814-490-3312');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (12,'New Cars Four You','West Virginia','Wendy Marks','810-210-1007');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (13,'Car Trades','Connecticut','Mike Mown','239-465-9989');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (14,'BMW Dealers','Montana','Lee Phil','629-751-6598');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (15,'Wheelers','Arizona','Mac Chester','692-914-6523');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (16,'Deals on Wheels','Michigan','Wall lee','256-964-9987');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (17,'Trucks R US','California','Eva Eve','698-985-5641');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (18,'Sporty Sales','Virginia','Crystal Johnson','239-569-5648');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (19,'Used and Mis-used','Washington','Letter Maine','715-412-6957');
INSERT INTO `tbldealership`(`dealerShipId`, `dealerShipName`, `dealerShipState`, `dealersName`, `dealersPhoneNumber`) VALUES (20,'We buy Cars','Ohio','Cris Powers','218-205-0529');
*/



CREATE TABLE tblCars
(
    carId int (11) NOT NULL AUTO_INCREMENT,
    carMake varchar (50) DEFAULT NULL,
    carModel varchar (50) DEFAULT NULL,
    carYear int (4) DEFAULT NULL,
    dealerShipId int (11) DEFAULT NULL,
    carSoldDate date DEFAULT NULL,
    customerId int (11) DEFAULT NULL,
    PRIMARY KEY (carId)
);

CREATE TABLE tblCustomerInfomation
(
    customerId int (11) NOT NULL AUTO_INCREMENT,
    customerFirstName varchar (50) DEFAULT NULL,
    customerLastName varchar (50) DEFAULT NULL,
    customerPhoneNumber varchar (10) DEFAULT NULL,
    customerState char (2) DEFAULT NULL,
    customerAddress varchar (50) DEFAULT NULL,
    carId int (11) DEFAULT NULL,
    dealerShipId int (11) DEFAULT NULL,
    PRIMARY KEY (customerId)
    
);

CREATE TABLE tblCarMaintainceServices
(
    carShopId int (11) NOT NULL AUTO_INCREMENT,
    carShopName varchar(50) DEFAULT NULL,
    carShopState char (2) DEFAULT NULL,
    carShopAddress varchar (50) DEFAULT NULL,
    carShopServices varchar (50) DEFAULT NULL,
    carId int (11) DEFAULT NULL,
    carShopDateService date DEFAULT NULL,
    PRIMARY KEY (carShopId)
    
);

CREATE TABLE tblDealerInventory
(
    dealerLotId int (11) NOT NULL AUTO_INCREMENT,
    carId int (11) DEFAULT NULL,
    dealerShipId int (11) DEFAULT NULL,
    PRIMARY KEY (dealerLotId)
);