执行SQL建表插入报UNIQUE constraint failed错误排查

问题根因 #

报错直接触发点在B表的主键定义：

• 你将B表的a2字段设为主键，而关系型数据库的主键自带全局唯一、非空的强制约束，同一张表里主键值不能重复。但你插入B表的数据中，a2字段值s重复插入3次、m重复插入2次、b重复插入4次，第一次插入('s',12)成功后，后续再插入a2='s'的记录就会直接触发唯一约束冲突。
• 除了显式的唯一约束报错，这段SQL还有一个隐藏的外键定义错误：B表设置的外键FOREIGN KEY (a2) REFERENCES A(a2)，引用的是A表的a2字段，但这个字段既不是A表的主键，也没有创建唯一索引，且A表中a2本身就存了大量重复值，绝大多数数据库引擎会直接拒绝创建这个外键约束，哪怕解决了主键冲突，这一步也会执行失败。

修正方案 #

从你现有插入的数据逻辑看，A表的a2是分类字段（存在s、m、b三个分类值，每个分类对应多条A表记录），A和B是典型的一对多关系，正确的建模逻辑应该是外键关联父表的主键，而非重复的分类字段：

1. 给B表设置独立的自增主键，避免主键值重复
2. 调整外键字段为关联A表主键ka，而非重复的a2字段
3. 调整B表的插入语句，匹配新的表结构

修正后的可执行SQL如下：

CREATE TABLE A (
ka INT PRIMARY KEY,
a2 VARCHAR(1)
);

INSERT INTO A VALUES (1,'s');
INSERT INTO A VALUES (2,'s');
INSERT INTO A VALUES (3,'s');
INSERT INTO A VALUES (4,'m');
INSERT INTO A VALUES (5,'m');
INSERT INTO A VALUES (6,'b');
INSERT INTO A VALUES (7,'b');
INSERT INTO A VALUES (8,'b');
INSERT INTO A VALUES (9,'b');

CREATE TABLE B (
kb INTEGER PRIMARY KEY AUTOINCREMENT,
ka INTEGER NOT NULL,
b2 INT,
FOREIGN KEY (ka) REFERENCES A(ka)
);

INSERT INTO B (ka, b2) VALUES (1,12);
INSERT INTO B (ka, b2) VALUES (2,23);
INSERT INTO B (ka, b2) VALUES (3,34);
INSERT INTO B (ka, b2) VALUES (4,45);
INSERT INTO B (ka, b2) VALUES (5,56);
INSERT INTO B (ka, b2) VALUES (6,67);
INSERT INTO B (ka, b2) VALUES (7,78);
INSERT INTO B (ka, b2) VALUES (8,89);
INSERT INTO B (ka, b2) VALUES (9,90);

如果你业务上确实需要B表以a2作为主键，那必须保证B表a2值全局唯一，同时A表被引用的a2字段也必须全局唯一，这就需要你删掉A、B表中所有a2重复的记录，每个a2值仅保留1条，但这和你当前批量插入重复值的需求完全冲突，不推荐采用。

内容的提问来源于stack exchange，提问作者Gert Wiebach