CODEWITHSUNDEEP
mysql
spoofskay
spoofskay

Reputation: 1

MYSQL help not letting me create table

I'm Confused as to why my code isnt working.

CREATE TABLE DELIVERY (
VEHICLE_VEH_ID INT NOT NULL,
DRIVER_DR_ID INT NOT NULL,
DEL_DATE DATETIME NOT NULL,
DEL_TIME DATETIME NOT NULL,
PRIMARY KEY (VEHICLE_VEH_ID , DRIVER_DR_ID)
INDEX (DRIVER_DR_ID),
INDEX (VEHICLE_VEH_ID),
CONSTRAINT FK_VEHICLE_HAS_DRIVER_VEHICLE FOREIGN KEY (VEHICLE_VEH_ID) REFERENCES VEHICLE (VEH_ID) 
CONSTRAINT FK_VEHICLE_HAS_DRIVER_DRIVER FOREIGN KEY (DRIVER_DR_ID) DRIVER (DR_ID));

CREATE TABLE DRIVER (
 DR_ID INT NOT NULL PRIMARY KEY,
 DR_TITLE VARCHAR(15) NOT NULL,
 DR_FNAME VARCHAR(45) NOT NULL,
 DR_LNAME VARCHAR(45) NOT NULL,
 DR_DOB DATETIME NOT NULL,
 DR_LICENCENO VARCHAR(45) NOT NULL,
 DR_PHONE VARCHAR(15) NOT NULL,
 DR_EMAIL VARCHAR(45) NOT NULL);

CREATE TABLE VEHICLE (
 VEH_ID INT NOT NULL PRIMARY KEY,
 VEH_REG VARCHAR(15) NOT NULL,
 VEH_MAKE VARCHAR(45) NOT NULL,
 VEH_MODEL VARCHAR(45) NOT NULL,
 VEH_MILEAGE INT NOT NULL,
 VEH_MOTDATE DATETIME NOT NULL,
 VEH_SERVICEDATE DATETIME NOT NULL);





mysql> CREATE TABLE DELIVERY (
    -> VEHICLE_VEH_ID INT NOT NULL,
    -> DRIVER_DR_ID INT NOT NULL,
    -> DEL_DATE DATETIME NOT NULL,
    -> DEL_TIME DATETIME NOT NULL,
    -> PRIMARY KEY (VEHICLE_VEH_ID , DRIVER_DR_ID)
    -> INDEX (DRIVER_DR_ID),
    -> INDEX (VEHICLE_VEH_ID),
    -> CONSTRAINT FK_VEHICLE_HAS_DRIVER_VEHICLE , CONSTRAINT FK_VEHICLE_HAS_DRIVER_DRIVER  FOREIGN KEY (VEHICLE_VEH_ID) REFERENCES VEHICLE (VEH_ID) FOREIGN KEY (DRIVER_DR_ID) DRIVER (DR_ID));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDEX (DRIVER_DR_ID),
INDEX (VEHICLE_VEH_ID),
CONSTRAINT FK_VEHICLE_HAS_DRIVER_V' at line 7
mysql>

Im trying to create a table but it says i have an error when i dont think i do. I'll be very greatful if you can see what i've done wrong. Thanks everyone that helps!

Upvotes: 0

Views: 74

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You first need to create these two tables:

CREATE TABLE DRIVER (
 DR_ID INT NOT NULL PRIMARY KEY,
 DR_TITLE VARCHAR(15) NOT NULL,
 DR_FNAME VARCHAR(45) NOT NULL,
 DR_LNAME VARCHAR(45) NOT NULL,
 DR_DOB DATETIME NOT NULL,
 DR_LICENCENO VARCHAR(45) NOT NULL,
 DR_PHONE VARCHAR(15) NOT NULL,
 DR_EMAIL VARCHAR(45) NOT NULL);

CREATE TABLE VEHICLE (
 VEH_ID INT NOT NULL PRIMARY KEY,
 VEH_REG VARCHAR(15) NOT NULL,
 VEH_MAKE VARCHAR(45) NOT NULL,
 VEH_MODEL VARCHAR(45) NOT NULL,
 VEH_MILEAGE INT NOT NULL,
 VEH_MOTDATE DATETIME NOT NULL,
 VEH_SERVICEDATE DATETIME NOT NULL);

Then use this sql code to create the last one, that references the two tables above:

CREATE TABLE DELIVERY (
VEHICLE_VEH_ID INT NOT NULL,
DRIVER_DR_ID INT NOT NULL,
DEL_DATE DATETIME NOT NULL,
DEL_TIME DATETIME NOT NULL,
PRIMARY KEY (VEHICLE_VEH_ID , DRIVER_DR_ID),
INDEX (DRIVER_DR_ID),
INDEX (VEHICLE_VEH_ID),
CONSTRAINT FK_VEHICLE_HAS_DRIVER_VEHICLE FOREIGN KEY (VEHICLE_VEH_ID) REFERENCES VEHICLE (VEH_ID) ,
CONSTRAINT FK_VEHICLE_HAS_DRIVER_DRIVER FOREIGN KEY (DRIVER_DR_ID) REFERENCES DRIVER (DR_ID));

Your code is also missing two commas plus the REFERENCES keyword in the FK_VEHICLE_HAS_DRIVER_DRIVER foreign key constraint.

Demo here

Upvotes: 2

Darwin von Corax
Darwin von Corax

Reputation: 5256

You forgot a comma.

...
PRIMARY KEY (VEHICLE_VEH_ID , DRIVER_DR_ID), -- Need a comma here
INDEX (DRIVER_DR_ID),
...

Upvotes: 2

Related Questions