如何引用表中作为主键和候选键的多个外键？解决建表外键约束报错

• MySQL要求外键引用的父表列必须有对应索引：你当前给firstName、lastName、dateOfBirth分别设置单独外键，但PERSON表中这三个列仅存在person_ckey1联合唯一索引，没有单独的单列索引，因此单独引用lastName列设置外键时找不到对应索引，触发1822报错。
• 现有外键设计逻辑存在缺陷：CHEF作为PERSON的子实体，四个关联字段理应对应同一条PERSON记录，分开设置单列外键会出现字段匹配不同PERSON记录的逻辑错误。

方案1：仅保留passportNumber外键（最推荐） #

passportNumber是PERSON表的主键，仅需这一个外键即可保证CHEF记录和PERSON记录的唯一关联，无需在CHEF表冗余存储firstName、lastName、dateOfBirth三个字段，需要使用时关联查询PERSON表即可。
修改后的建表语句如下：

CREATE TABLE PERSON(  
    passportNumber VARCHAR(20) NOT NULL,  
    firstName VARCHAR(30) NOT NULL,  
    lastName VARCHAR(30) NOT NULL,  
    dateOfBirth DATE NOT NULL,  
    gender CHAR(1) NOT NULL,  
    CONSTRAINT person_pkey PRIMARY KEY(passportNumber),  
    CONSTRAINT person_ckey1 UNIQUE(firstName, lastName, dateOfBirth)
);

CREATE TABLE CHEF(  
    culinaryCerts VARCHAR(300) NOT NULL,  
    competitionEXpr VARCHAR(300) NULL,  
    passportNumber VARCHAR(20) NOT NULL,
    CONSTRAINT chef_pkey PRIMARY KEY(passportNumber),
    CONSTRAINT chef_fkey1 FOREIGN KEY(passportNumber)
                        REFERENCES PERSON(passportNumber) ON DELETE CASCADE
);

方案2：设置联合外键引用候选键 #

如果业务要求必须在CHEF表冗余存储firstName、lastName、dateOfBirth三个字段，可以设置联合外键引用PERSON表的联合唯一候选键，既能满足外键约束，也能保证四个字段对应同一条PERSON记录。
修改后的建表语句如下：

CREATE TABLE PERSON(  
    passportNumber VARCHAR(20) NOT NULL,  
    firstName VARCHAR(30) NOT NULL,  
    lastName VARCHAR(30) NOT NULL,  
    dateOfBirth DATE NOT NULL,  
    gender CHAR(1) NOT NULL,  
    CONSTRAINT person_pkey PRIMARY KEY(passportNumber),  
    CONSTRAINT person_ckey1 UNIQUE(firstName, lastName, dateOfBirth)
);

CREATE TABLE CHEF(  
    culinaryCerts VARCHAR(300) NOT NULL,  
    competitionEXpr VARCHAR(300) NULL,  
    passportNumber VARCHAR(20) NOT NULL,  
    firstName VARCHAR(30) NOT NULL,  
    lastName VARCHAR(30) NOT NULL,  
    dateOfBirth DATE NOT NULL,  
    CONSTRAINT chef_pkey PRIMARY KEY(passportNumber),  
    CONSTRAINT chef_ckey1 UNIQUE(firstName, lastName, dateOfBirth),  
    CONSTRAINT chef_fkey1 FOREIGN KEY(passportNumber)
                        REFERENCES PERSON(passportNumber) ON DELETE CASCADE,
    -- 联合外键引用PERSON表的联合唯一候选键
    CONSTRAINT chef_fkey2 FOREIGN KEY(firstName, lastName, dateOfBirth)
                        REFERENCES PERSON(firstName, lastName, dateOfBirth) ON DELETE CASCADE
);

不推荐的兼容方案 #

如果一定要保留原有四个单独外键的写法（会存在数据逻辑不一致的风险，强烈不建议），可以在PERSON表给三个单列分别添加独立索引：

-- 提前执行以下语句再建CHEF表即可解决报错
ALTER TABLE PERSON ADD UNIQUE INDEX idx_firstName(firstName);
ALTER TABLE PERSON ADD UNIQUE INDEX idx_lastName(lastName);
ALTER TABLE PERSON ADD UNIQUE INDEX idx_dateOfBirth(dateOfBirth);

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