MySQL报错1824无法打开引用表 SQL表关系设置问题求助

错误原因排查 #

MySQL报错1824 failed to open the referenced table 的核心逻辑是：创建外键约束时，被引用的父表必须已经在当前库中完成创建，你的脚本存在表创建顺序、主键、插入语句等多类问题，具体如下： #

具体问题清单 #

• 表创建顺序完全错误，违反外键依赖逻辑：
  1. 创建ORDER_ITEM时外键关联了还未创建的SKU_DATA表
  2. 创建SKU_DATA时外键关联了还未创建的BUYER表
     正确创建顺序应该先建无外部依赖的父表，再建子表：BUYER → SKU_DATA → RETAIL_ORDER → ORDER_ITEM
• ORDER_ITEM主键设置错误：
  你将OrderNumber单独设为主键，但一个订单会对应多个订单项，后续插入同订单不同SKU的记录时会触发主键冲突，应该改为OrderNumber+SKU的联合主键。
• 插入语句写错表名：
  最后插入BUYER表的5条语句，全部误写为插入SKU_DATA，字段类型完全不匹配。
• 数据一致性错误：
  1. SKU_DATA表中买家名称为Peter Hansen，但BUYER表中对应名称为Pete Hansen，外键关联会失败
  2. BUYER表插入数据顺序错误，Supervisor字段外键关联本表的BuyerName，需要先插入无上级的经理记录，再插入下属记录
  3. 拼写错误：Jerry Martin的上级误写为Cindo Lo，正确应为Cindy Lo

修正后可正常运行的完整SQL脚本 #

DROP DATABASE IF EXISTS `sql_retail`;
CREATE DATABASE `sql_retail`; 
USE `sql_retail`;

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;

-- 先建BUYER表，无外部依赖
CREATE TABLE `BUYER` (
  `BuyerName` varchar(15) NOT NULL,
  `Department` varchar(15) NOT NULL,
  `Position` varchar(15) NOT NULL,
  `Supervisor` varchar(15) NULL, -- 最高级主管没有上级，设为NULL避免约束失败
  PRIMARY KEY (`BuyerName`),
  FOREIGN KEY(`Supervisor`) REFERENCES BUYER(`BuyerName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 先插入最高级主管，再插入下属
INSERT INTO `BUYER` VALUES ('Mary Smith', 'Purchasing', 'Manager', NULL);
INSERT INTO `BUYER` VALUES ('Cindy Lo', 'Purchasing', 'Buyer 2', 'Mary Smith');
INSERT INTO `BUYER` VALUES ('Peter Hansen', 'Purchasing', 'Buyer 3', 'Mary Smith');
INSERT INTO `BUYER` VALUES ('Jerry Martin', 'Purchasing', 'Buyer 1', 'Cindy Lo');
INSERT INTO `BUYER` VALUES ('Nancy Meyers', 'Purchasing', 'Buyer 1', 'Peter Hansen');

-- 再建SKU_DATA表，依赖BUYER
CREATE TABLE `SKU_DATA` (
  `SKU` int(11) NOT NULL,
  `SKU_Description` varchar(50) NOT NULL,
  `Department` varchar(20) NOT NULL,
  `Buyer` varchar(15) NOT NULL,
  PRIMARY KEY (`SKU`),
  FOREIGN KEY(`Buyer`) REFERENCES BUYER(`BuyerName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `SKU_DATA` VALUES (100100, 'Std. Scuba Tank, Yellow', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100200, 'Std. Scuba Tank, Magenta', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100300, 'Std. Scuba Tank, Light Blue', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100400, 'Std. Scuba Tank, Dark Blue', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100500, 'Std. Scuba Tank, Light Green', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100600, 'Std. Scuba Tank, Dark Green', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (101100, 'Dive Mask, Small Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO `SKU_DATA` VALUES (101200, 'Dive Mask, Med Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO `SKU_DATA` VALUES (201000, 'Half dome tent', 'camping', 'Cindy Lo');
INSERT INTO `SKU_DATA` VALUES (202000, 'Half dome tent vestibule', 'camping', 'Cindy Lo');
INSERT INTO `SKU_DATA` VALUES (203000, 'Half dome tent vestibule wide', 'camping', 'Cindy Lo');
INSERT INTO `SKU_DATA` VALUES (301000, 'Light fly climbing harness', 'climbing', 'Jerry Martin');
INSERT INTO `SKU_DATA` VALUES (302000, 'Locking Carabiner, Oval', 'climbing', 'Jerry Martin');

-- 再建RETAIL_ORDER表，无外部依赖
CREATE TABLE `RETAIL_ORDER` (
  `OrderNumber` int(4) NOT NULL,
  `StoreNumber` int(2) NOT NULL,
  `StoreZip`    char(9) NOT NULL,
  `OrderMonth`  char(12) NOT NULL,
  `OrderYear`   int(4) NOT NULL,
  `OrderTotal`  float(10) NOT NULL,
  PRIMARY KEY (`OrderNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `RETAIL_ORDER` VALUES (1000, 10, '98110', 'December', 2017, 445.00);
INSERT INTO `RETAIL_ORDER` VALUES (2000, 20, '02335', 'December', 2017, 310.00);
INSERT INTO `RETAIL_ORDER` VALUES (3000, 10, '98110', 'January', 2018, 480.00);

-- 最后建ORDER_ITEM表，依赖RETAIL_ORDER和SKU_DATA
CREATE TABLE `ORDER_ITEM` (
  `OrderNumber` int(11) NOT NULL,
  `SKU` int(7) NOT NULL,
  `Quantity` int(6) NOT NULL,
  `Price` float(10) NOT NULL,
  `Extended Price` float(10) NOT NULL,
  PRIMARY KEY (`OrderNumber`, `SKU`), -- 联合主键，避免同订单多商品冲突
  FOREIGN KEY(`OrderNumber`) REFERENCES RETAIL_ORDER(`OrderNumber`),
  FOREIGN KEY(`SKU`) REFERENCES SKU_DATA(`SKU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `ORDER_ITEM` VALUES (1000, 201000, 1, 300.00, 300.00);
INSERT INTO `ORDER_ITEM` VALUES (1000, 202000, 1, 130.00, 130.00);
INSERT INTO `ORDER_ITEM` VALUES (2000, 101100, 4, 50.00, 200.00);
INSERT INTO `ORDER_ITEM` VALUES (2000, 101200, 2, 50.00, 100.00);
INSERT INTO `ORDER_ITEM` VALUES (3000, 100200, 1, 300.00, 300.00);
INSERT INTO `ORDER_ITEM` VALUES (3000, 101100, 2, 50.00, 100.00);
INSERT INTO `ORDER_ITEM` VALUES (3000, 101200, 1, 50.00, 50.00);

内容的提问来源于stack exchange，提问作者David Garcia Jr.