mysql - 多对多表 SQL

Question

我必须在餐 table MEAL 和餐 table RESTAURANT 之间做多对多的餐 table 。首先我创建了两个表:

CREATE TABLE RESTAURANT (
ID_Restaurant VARCHAR(10) NOT NULL, 
ID_Hotel VARCHAR(10) NOT NULL, 
Name VARCHAR(30) NOT NULL, 
Number_of_Tables INT(3) NOT NULL,
PRIMARY KEY (ID_Restaurant),
FOREIGN KEY (ID_Hotel) REFERENCES HOTEL (ID_Hotel));

CREATE TABLE MEAL(
ID_Meal VARCHAR(10) NOT NULL, 
Name VARCHAR(30) NOT NULL, 
Preparation_Time VARCHAR(20), 
Cooking_Time VARCHAR(20),
PRIMARY KEY (ID_Meal));

然后我创建了“联合”表:

CREATE TABLE MEAL_SERVED(
ID_Meal VARCHAR(10) NOT NULL,
ID_Restaurant VARCHAR(10) NOT NULL,
Price INT(5) NOT NULL, 
PRIMARY KEY (ID_Meal, ID_Restaurant, Price),
FOREIGN KEY(ID_Meal) REFERENCES MEAL(ID_Meal),
FOREIGN KEY(ID_Restaurant) REFERENCES RESTAURANT (ID_Restaurant));

我在前两个表中输入了一些数据:

INSERT INTO RESTAURANT(ID_Restaurant, ID_Hotel, Name, Number_of_Tables)
VALUES ('REST1', 'H1', 'Benares Indisk Restaurant', 26);
('REST2', 'H2', 'La Gaichel', 35),
('REST3', 'H3', 'Tapas Restaurant', 17),
('REST4', 'H4', 'Faubourg 101', 19),
('REST5', 'H5', 'Pizzeria Roma', 38);

INSERT INTO MEAL(ID_Meal, Name, Preparation_Time, Cooking_Time)
VALUES ('MEAL1', 'Croque-Monsieur', '5 min', '4 min'),
('MEAL2', 'Salad', '6 min', NULL),
('MEAL3', 'Hot Dog', '3 min', '2min'),
('MEAL4', 'Panini', '6 min', '5 min'),
('MEAL5', 'Coca-Cola', NULL, NULL);

直到，现在没有问题，但是当我尝试在第三个表中输入数据时:

INSERT INTO MEAL_SERVED(ID_Meal, ID_Restaurant, Price)
VALUES ('MEAL1', 'REST1', 50),
('MEAL4', 'REST1', 50),
('MEAL5', 'REST1', 35),
('MEAL1', 'REST2', 3.5),
('MEAL2', 'REST2', 3.5),
('MEAL4', 'REST2', 5);

例如，然后我有一条错误消息:

FOREIGN KEY constraint failed: INSERT INTO MEAL_SERVED(ID_Meal, ID_Restaurant, Price)

我不明白为什么会收到此消息以及如何更正它。提前致谢。

Answer 1

Shadow在评论区说的。

首先，修正您在一个 SQL 语句中的拼写错误:

INSERT INTO RESTAURANT(ID_Restaurant, ID_Hotel, Name, Number_of_Tables)
VALUES ('REST1', 'H1', 'Benares Indisk Restaurant', 26);
('REST2', 'H2', 'La Gaichel', 35),
('REST3', 'H3', 'Tapas Restaurant', 17),
('REST4', 'H4', 'Faubourg 101', 19),
('REST5', 'H5', 'Pizzeria Roma', 38);

应该是:

INSERT INTO RESTAURANT(ID_Restaurant, ID_Hotel, Name, Number_of_Tables)
VALUES ('REST1', 'H1', 'Benares Indisk Restaurant', 26),
('REST2', 'H2', 'La Gaichel', 35),
('REST3', 'H3', 'Tapas Restaurant', 17),
('REST4', 'H4', 'Faubourg 101', 19),
('REST5', 'H5', 'Pizzeria Roma', 38);

第二个:帮自己一个忙，使用整数作为主键，使用 auto_increment 选项，您可以忽略 id。

CREATE TABLE RESTAURANT (
`id` INT NOT NULL AUTO_INCREMENT,
`hotel` INT NOT NULL, 
`name` VARCHAR(30) NOT NULL, 
`numberOfTables` INT NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (hotel) REFERENCES HOTEL (`id`));

CREATE TABLE MEAL(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL, 
`preparationTime` VARCHAR(20), 
`cookingTime` VARCHAR(20),
PRIMARY KEY (`id`));

CREATE TABLE MEAL_SERVED(
`id` INT NOT NULL AUTO_INCREMENT,
`meal` INT NOT NULL,
`restaurant` INT NOT NULL,
Price FLOAT NOT NULL, 
PRIMARY KEY (`id`),
FOREIGN KEY(meal) REFERENCES MEAL(`id`),
FOREIGN KEY(restaurant) REFERENCES RESTAURANT (`id`));

数据:

INSERT INTO RESTAURANT(`hotel`, `name`, `numberOfTables`)
VALUES ('1', 'Benares Indisk Restaurant', 26),
(2, 'La Gaichel', 35),
(3, 'Tapas Restaurant', 17),
(4, 'Faubourg 101', 19),
(5, 'Pizzeria Roma', 38);

INSERT INTO MEAL(`name`, `preparationTime`, `cookingTime`)
VALUES ('Croque-Monsieur', '5 min', '4 min'),
('Salad', '6 min', NULL),
('Hot Dog', '3 min', '2min'),
('Panini', '6 min', '5 min'),
('Coca-Cola', NULL, NULL);

INSERT INTO MEAL_SERVED(`meal`, `restaurant`, `price`)
VALUES (1, 1, 50),
(4, 1, 50),
(5, 1, 35),
(1, 2, 3.5),
(2, 2, 3.5),
(4, 2, 5);

第三:INT(3) 没有效果。无论如何都会为 INT 分配空间。也许您可以使用 TINYINT 或 SMALLINT instad。看这里:https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

第四:请使用 Camel 式或蛇形式，但不要同时使用 ;-)