Issue with MySQL, cannot add foreign key constraint

Question

I'm trying to create a table named appointment, though when I try to create it I receive the error:

Cannot add foreign key constraint

My SQL code is as follows:

 CREATE TABLE APPOINTMENT(
    APNo VARCHAR(5),
    PNo VARCHAR(5), 
    DNo VARCHAR(5),
    APDATE DATETIME
);

ALTER TABLE APPOINTMENT
    ADD PRIMARY KEY (APNo),
    ADD FOREIGN KEY (PNo) REFERENCES PATIENT(PNo),
    ADD FOREIGN KEY (DNo) REFERENCES DOCTOR(DNo)
;

Possible duplicate of [Add multiple constraints in one statement](https://stackoverflow.com/questions/24593101/add-multiple-constraints-in-one-statement) — abestrad, Mar 02 '19 at 20:30

score 0 · Answer 1 · answered Mar 02 '19 at 21:42

You syntax looks ok. You are not showing the DDL for referrenced tables PATIENT and DOCTOR, however it is likely that the error happens because one of MySQL foreign keys requirements is not met.

Quotes from the documentation:

Corresponding columns in the foreign key and the referenced key must have similar data types.

You must ensure that both DOCTOR(DNo) and PATIENT(PNo) are VARCHAR(5).

MySQL requires indexes on foreign keys and referenced keys. [...] Such an index is created on the referencing table automatically if it does not exist.

Ideally, DOCTOR(DNo) and PATIENT(PNo) should be the primary key of their respective tables. Else, an index must exist for each of them (it could be a multi-column index where the referrenced column appears once).

See this db fiddle for a working example:

CREATE TABLE PATIENT(PNo VARCHAR(5) PRIMARY KEY);
CREATE TABLE DOCTOR(DNo VARCHAR(5) PRIMARY KEY);

CREATE TABLE APPOINTMENT(
    APNo VARCHAR(5),
    PNo VARCHAR(5), 
    DNo VARCHAR(5),
    APDATE DATETIME
);

ALTER TABLE APPOINTMENT
    ADD PRIMARY KEY (APNo),
    ADD FOREIGN KEY (PNo) REFERENCES PATIENT(PNo),
    ADD FOREIGN KEY (DNo) REFERENCES DOCTOR(DNo)
;

Issue with MySQL, cannot add foreign key constraint

1 Answers1