餐厅数据库SQL报错：同一客户新增多条预订触发主键重复问题

核心错误是外键约束配置逻辑完全不符合业务需求：
你为reservation表定义的外键fk_reservation_customer_1，错误地将reservation表的主键reservationID关联到了customer表的customerID，而没有使用预留的客户关联字段customerForeign。
该约束强制要求每一条预订记录的主键reservationID必须等于关联客户的customerID，而主键天然全局唯一，直接导致同一个客户只能存在1条预订记录：为ID为1的客户新增第二条预订时，如果你指定reservationID=1会触发主键重复报错，如果你使用新的自增reservationID会触发外键约束不匹配报错。
此外其余表的外键配置也存在相同的逻辑错误，不符合实际业务：

• order表错误将主键orderID关联customerID，导致一个客户只能创建1条订单
• bill表错误将主键billID关联orderID，限制一个订单只能对应1条账单
• meal表错误将主键mealID关联orderID，限制一个订单只能关联1份餐品
• table表错误将主键tableID关联reservationID，导致一个餐桌只能被预订1次

首先删除所有错误的外键约束，再为各表新增专属外键字段后重新配置正确的关联关系：

步骤1：删除错误外键 #

ALTER TABLE `bill` DROP FOREIGN KEY `fk_bill_order_1`;
ALTER TABLE `meal` DROP FOREIGN KEY `fk_meal_order_1`;
ALTER TABLE `order` DROP FOREIGN KEY `fk_order_customer_1`;
ALTER TABLE `reservation` DROP FOREIGN KEY `fk_reservation_customer_1`;
ALTER TABLE `table` DROP FOREIGN KEY `fk_table_reservation_1`;

步骤2：新增专属外键字段 #

-- 账单表新增关联订单的字段
ALTER TABLE `bill` ADD COLUMN `orderID` INT NOT NULL;
-- 餐品表新增关联订单的字段
ALTER TABLE `meal` ADD COLUMN `orderID` INT NOT NULL;
-- 订单表新增关联客户、关联预订的字段
ALTER TABLE `order` ADD COLUMN `customerID` INT NOT NULL;
ALTER TABLE `order` ADD COLUMN `reservationID` INT NOT NULL;
-- 餐桌表新增关联预订的字段，空值代表未被预订
ALTER TABLE `table` ADD COLUMN `reservationID` INT NULL;

步骤3：添加正确的外键约束 #

ALTER TABLE `bill` ADD CONSTRAINT `fk_bill_order_1` FOREIGN KEY (`orderID`) REFERENCES `order` (`orderID`);
ALTER TABLE `meal` ADD CONSTRAINT `fk_meal_order_1` FOREIGN KEY (`orderID`) REFERENCES `order` (`orderID`);
ALTER TABLE `order` ADD CONSTRAINT `fk_order_customer_1` FOREIGN KEY (`customerID`) REFERENCES `customer` (`customerID`);
ALTER TABLE `order` ADD CONSTRAINT `fk_order_reservation_1` FOREIGN KEY (`reservationID`) REFERENCES `reservation` (`reservationID`);
-- 预订表使用预留的customerForeign字段关联客户
ALTER TABLE `reservation` ADD CONSTRAINT `fk_reservation_customer_1` FOREIGN KEY (`customerForeign`) REFERENCES `customer` (`customerID`);
ALTER TABLE `table` ADD CONSTRAINT `fk_table_reservation_1` FOREIGN KEY (`reservationID`) REFERENCES `reservation` (`reservationID`);

验证效果 #

修正后可直接为ID为1的客户新增多条预订，无需手动指定主键：

INSERT INTO `reservation` (`checkIn`, `checkOut`, `customerForeign`) 
VALUES ('2024-05-20 18:00:00', '2024-05-20 20:30:00', 1);

此时reservationID会自动递增，不会触发主键或外键报错，满足同一客户创建多条预订的需求。

内容的提问来源于stack exchange，提问作者Daniel Radosa