创建hosp_patient表遇Error 1822：hosp_patient_deptname_fk外键约束失败

问题根源 #

创建hosp_patient表时，hosp_patient_deptname_fk外键约束引用了hosp_department表的dept_name列，但该列未创建任何索引。MySQL规定，外键引用的列必须存在索引（主键、唯一索引或普通索引），否则无法建立外键关联。

两种可行解决方案 #

方案1：给dept_name添加唯一索引（推荐） #

如果科室名称是业务上唯一的标识，直接给dept_name添加UNIQUE约束，MySQL会自动为该列创建唯一索引，同时保证数据唯一性：

CREATE TABLE hosp_department
(
    dept_number     INT         PRIMARY KEY,
    dept_name       VARCHAR(20)     NOT NULL UNIQUE, -- 新增UNIQUE约束
    dept_location   VARCHAR(20),
    dept_authorization VARCHAR(20)
);

方案2：给dept_name添加普通索引 #

若业务允许科室名称重复，可显式创建普通索引：

CREATE TABLE hosp_department
(
    dept_number     INT         PRIMARY KEY,
    dept_name       VARCHAR(20)     NOT NULL,
    dept_location   VARCHAR(20),
    dept_authorization VARCHAR(20),
    INDEX idx_dept_name (dept_name) -- 显式创建普通索引
);

完整修正后的SQL代码 #

USE user;
SET FOREIGN_KEY_CHECKS = 0; 
DROP TABLE IF EXISTS hosp_department;
CREATE TABLE hosp_department
(
    dept_number     INT         PRIMARY KEY,
    dept_name       VARCHAR(20)     NOT NULL UNIQUE,
    dept_location   VARCHAR(20),
    dept_authorization VARCHAR(20)
);

DROP TABLE IF EXISTS hosp_employee;
CREATE TABLE hosp_employee
(
    emp_id  INT     PRIMARY KEY     AUTO_INCREMENT,
    emp_fname       VARCHAR(20)     NOT NULL,
    emp_mname       VARCHAR(20),
    emp_lname       VARCHAR(20)     NOT NULL,
    emp_ssn         INT(9),
    emp_salary      INT             NOT NULL,
    emp_city        VARCHAR(50),
    emp_state       VARCHAR(50),
    emp_zip         INT,
    emp_supervisor_id   INT,
    emp_department_id INT,
    CONSTRAINT hosp_employee_supervisor_fk FOREIGN KEY (emp_supervisor_id) 
    REFERENCES hosp_employee(emp_id),
    CONSTRAINT hosp_employee_department_fk FOREIGN KEY (emp_department_id) REFERENCES 
    hosp_department(dept_number)
);

DROP TABLE IF EXISTS hosp_patient;
CREATE TABLE hosp_patient
(
    patient_id      INT         PRIMARY KEY AUTO_INCREMENT,
    patient_fname   VARCHAR(20)     NOT NULL,
    patient_mname   VARCHAR(20),
    patient_lname   VARCHAR(20)     NOT NULL, 
    patient_dob     INT,
    patient_sex     CHAR(1),
    patient_floornumber INT, 
    patient_deptname    VARCHAR(20),
    CONSTRAINT hosp_patient_floornumber_fk FOREIGN KEY (patient_floornumber) 
    REFERENCES hosp_department(dept_number),
    CONSTRAINT hosp_patient_deptname_fk FOREIGN KEY (patient_deptname)
    REFERENCES hosp_department(dept_name)
); 

SET FOREIGN_KEY_CHECKS = 1;

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