Islam E Islam E - 1 year ago 55
MySQL Question

Insert values into a table with a foreign key using mysql

I want to insert values in a table containing foreign keys, but when I add the foreign keys manually (for example, addin the id of the foreign key manually by writing it's number), it doesn't work and it me the error of a wrong syntax.


#1064 - 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 'FOREIGN KEY,

id_livreur int FOREIGN KEY,

id_plat int FOREIGN KEY,

id_dessert ' at line 4


Here is my table that contains foreign keys :

CREATE TABLE Commande
(
id_commande int NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_client int FOREIGN KEY,
id_livreur int FOREIGN KEY,
id_plat int FOREIGN KEY,
id_dessert int FOREIGN KEY ,
prix_total int,
heure_estime time,
FOREIGN KEY (id_client) REFERENCES Client(id_client),
FOREIGN KEY (id_livreur) REFERENCES Livreur(id_livreur),
FOREIGN KEY (id_plat) REFERENCES Plat(id_plat),
FOREIGN KEY (id_dessert) REFERENCES Dessert(id_dessert)
) ENGINE=InnoDB;


And here are my inserts :

INSERT INTO Commande VALUES (1,1,1,1,1,45,now() + INTERVAL 20 minute);
INSERT INTO Commande VALUES (2,2,2,2,2,55,now() + INTERVAL 20 minute);
INSERT INTO Commande VALUES (3,3,3,3,3,75,now() + INTERVAL 20 minute);
INSERT INTO Commande VALUES (4,4,4,4,4,45,now() + INTERVAL 20 minute);

Answer Source

remove "FOREIGN KEY" after the column definition

CREATE TABLE Commande
(
id_commande int NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_client int,
id_livreur int,
id_plat int ,
id_dessert int,
prix_total int,
heure_estime time,
FOREIGN KEY (id_client) REFERENCES Client(id_client),
FOREIGN KEY (id_livreur) REFERENCES Livreur(id_livreur),
FOREIGN KEY (id_plat) REFERENCES Plat(id_plat),
FOREIGN KEY (id_dessert) REFERENCES Dessert(id_dessert)
) ENGINE=InnoDB;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download