Error 1822 failed to add the foreign key constraint hosp_patient_deptname

I'm currently working on this table. I'm getting constraint errors for the table hosp_patient but everything else works. The error reads Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'hosp_patient_deptname_fk' in the referenced table 'hosp_department' Here is the sql code

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;

CodePudding user response：

The short answer is that you've defined a foreign key constraint to a non-primary-key column in the referenced table. The PK of table hosp_department is dept_number, not dept_name.

Keep in mind that a foreign key must reference a unique row in the referenced table. Nothing in the schema stops you from assigning the same department name to two different department IDs; if that occurs, then the DB engine cannot know which department a patient actually refers to.

Some DBMS's (MS-SQL Server for one) allow you to create a FK to a UNIQUE field in the referenced table. In other words, you could create a FK to hosp_department.dept_name if you defined a UNIQUE constraint on that column, or if you defined a UNIQUE index on the column. I don't know if MySQL permits this or not, but IMO it's very bad practice either way.

FK's need to refer to primary keys in a good DB design unless there is a very good (and very unusual) reason otherwise. So, you should rethink this entire schema. Cheers.