Error Code 1215 Cannot Add Foreign Code Constraint

Question

My code:

CREATE TABLE Horse (
    ID              SMALLINT UNSIGNED AUTO_INCREMENT,
    RegisteredName  VARCHAR(15),
    PRIMARY KEY (ID)
);

CREATE TABLE Student (
    ID             SMALLINT UNSIGNED AUTO_INCREMENT,
    FirstName      VARCHAR(20),
    LastName        VARCHAR(30),
    PRIMARY KEY (ID)
);

CREATE TABLE LessonSchedule (
  HorseID SMALLINT UNSIGNED NOT NULL,
  StudentID SMALLINT UNSIGNED NOT NULL,
  LessonDateTime DATETIME NOT NULL,
  Primary Key (HorseID, StudentID, LessonDateTime),
  Foreign Key (HorseID) REFERENCES Horse(ID)
    ON DELETE CASCADE,
  Foreign Key (StudentID) REFERENCES Student(ID)
    ON DELETE SET NULL
);

I'm trying to create "LessonSchedule" with these requirements:

HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID)
StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID)
LessonDateTime - date/time, not NULL, partial primary key

If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.

If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.

The create horse table and create student table was given to me.

I am getting the error:

Query failed: ERROR 1215 (HY000) at line 15:
Cannot add foreign key constraint

Does this answer your question? [MySQL Error 1215: Cannot add foreign key constraint](https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) — philipxy, Nov 08 '21 at 21:04

score 0 · Answer 1 · answered Nov 08 '21 at 18:32

I tested your table creation statements, and then ran SHOW ENGINE INNODB STATUS. This includes a section that gives more specific information about the reason for the failure.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-11-08 10:31:11 0x700002686000 Error in foreign key constraint of table test/lessonschedule:
Foreign Key (StudentID) REFERENCES Student(ID) ON DELETE SET NULL ):
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

This means you can't define a foreign key with ON DELETE SET NULL if the column it is based on is defined with the NOT NULL option (as a PRIMARY KEY column must be).

DUH! thanks so much, that was it. I added the NOT NULL before submitting here because it was not working. StudentID was never supposed to be part of the primary key. I knew it was going to be something so obvious I was missing! — Daniel Batcho, Nov 08 '21 at 18:56

Error Code 1215 Cannot Add Foreign Code Constraint

1 Answers1