我目前正在处理这个表。我得到了表hosp_patient的约束错误，但其他一切都正常。错误代码为：1822.添加外键约束失败。引用表'hosp_department'中缺少约束'hosp_patient_deptname_fk'的索引。以下是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              ,
    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)
);


-- hosp_patient
-- need to fix last constraint

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;