设置3NF表插入数据时遇UNIQUE约束失败，求问题排查

问题现象 #

• 配置符合3NF的数据库表时，插入数据持续触发UNIQUE constraint failed错误
• 尝试插入包含重复16-SMITH的记录到Employee表，但操作失败
• 怀疑1NF转3NF过程存在错误，或主键/外键设置不符合业务逻辑

插入语句 #

INSERT INTO Employee VALUES ('16', 'Smith'), ('33', 'Smith'),('30', 'Jenny'), ('16', 'Smith')

当前表结构 #

CREATE TABLE "Employee" (
    "EMP_ID"    INTEGER,
    "EMP_FNAME"    TEXT,
    PRIMARY KEY("EMP_ID")
);

CREATE TABLE "Project" (
    "PROJ_ID"    INTEGER,
    "JOB_TYPE"    INTEGER,
    "EMP_ID"    INTEGER,
    PRIMARY KEY("PROJ_ID","EMP_ID")
);

CREATE TABLE "HOURLY PAY" (
    "JOB_TYPE"    INTEGER,
    "HOUR_RATE"    INTEGER,
    PRIMARY KEY("JOB_TYPE"),
    FOREIGN KEY("JOB_TYPE") REFERENCES "Project"("JOB_TYPE")
);

问题根源 #

1. Employee表主键约束直接冲突 #

Employee表将EMP_ID设为主键，而主键的核心规则就是唯一不可重复。你试图插入两条EMP_ID=16的记录，直接违反了这个约束，这是触发错误的直接原因。

2. 3NF转换逻辑与业务需求不匹配 #

你期望保留重复的员工记录，说明原始1NF表中EMP_ID并非唯一标识员工（可能是同一员工多次参与不同项目/任务的场景），但当前设计把EMP_ID设为Employee表主键，这和原始数据的业务逻辑矛盾。

正确的3NF设计思路应该是：

• Employee表仅存储唯一的员工基础信息，EMP_ID作为主键，每个员工只存一次
• 员工参与项目的重复记录，应该放在Project表中，通过EMP_ID外键关联Employee表，而非重复存储员工信息

3. 外键关联方向错误 #

HOURLY PAY表的外键JOB_TYPE引用Project表的JOB_TYPE，但Project表中JOB_TYPE既不是主键也没有唯一约束，这会导致外键逻辑失效——数据库无法保证HOURLY PAY中的JOB_TYPE在Project中有唯一匹配项，不符合3NF的依赖规则。合理的逻辑应该是先定义JOB_TYPE对应的薪资标准（HOURLY PAY表），再让Project表的JOB_TYPE引用它。

修正建议 #

1. 调整Employee表设计：

   • 如果业务上确实允许EMP_ID重复（这种场景非常少见，建议先确认业务逻辑），则不能将EMP_ID设为主键，可新增自增主键列（如EMP_RECORD_ID），或设置联合主键（如EMP_ID+其他能区分记录的字段）。
   • 如果是业务逻辑错误，员工信息应唯一，则删除插入语句中的重复EMP_ID记录，Employee表只存唯一员工，重复的项目参与记录放到Project表中。

2. 修正外键关联方向：

   CREATE TABLE "HOURLY PAY" (
       "JOB_TYPE"    INTEGER,
       "HOUR_RATE"    INTEGER,
       PRIMARY KEY("JOB_TYPE")
   );
   
   CREATE TABLE "Project" (
       "PROJ_ID"    INTEGER,
       "JOB_TYPE"    INTEGER,
       "EMP_ID"    INTEGER,
       PRIMARY KEY("PROJ_ID","EMP_ID"),
       FOREIGN KEY("JOB_TYPE") REFERENCES "HOURLY PAY"("JOB_TYPE")
   );
   

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