MySQL Create Schema with foreign keys throws error 1215

Question

I have the following basic schema of 3 tables I am creating in MySQL, one of which has two foreign keys (the documentreference table):

-- Dumping database structure
CREATE DATABASE IF NOT EXISTS `zeus`;
USE `zeus`;

-- ****** FHIR RESOURCES ******
CREATE TABLE IF NOT EXISTS `patients` 
(
    `Id` int(5) NOT NULL AUTO_INCREMENT,
    `Active` bool,
    `Name` JSON, -- JSON blob array of HumanName DataTypes
    `Telecom` JSON, -- JSON blob array of ContactPoint DataTypes
    `Gender` varchar(10),
    `BirthDate` datetime,
    `DeceasedBoolean` bool,
    `DeceasedDateTime` datetime,
    `Address` JSON, -- JSON array of Address DataTypes
    `MaritalStatus` JSON, -- JSON blob single CodeableConcept DataType
    `MultipleBirthBoolean` bool,
    `MultipleBirthInteger` int(5),
    `Communication` JSON, -- JSON array of languages & preferred bool i.e "[language: CodeableConcept(English), preferred: true, ...]"
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `documentreferences` 
(
    `Id` int(5) NOT NULL AUTO_INCREMENT,
    `Status` varchar(50),
    `DocStatus` varchar(50),
    `Type` JSON, -- single CodeableConcept DataType
    `Class` JSON, -- single CodeableConcept DataType
    `Created` datetime,
    `Indexed` datetime, 
    `Description` varchar(50),
    `SecurityLabel` JSON, -- array of CodeableConcept DataTypes
    `ContextEvent` JSON, -- array of CodeableConcept DataTypes
    `ContextPeriodStart` datetime,
    `ContextPeriodEnd` datetime,
    `ContextFacilityType` JSON, -- single CodeableConcept DataType
    `ContextPracticeSetting` JSON, -- single CodeableConcept DataType
    PRIMARY KEY (`id`),
    `SubjectId` int(5),
    INDEX subject_ind (SubjectId),
    FOREIGN KEY (SubjectId)
    REFERENCES patients(Id)
    ON DELETE CASCADE,
    -- ** FK below causes issue **
    `PractitionerId` int(5),
    INDEX practitioner_ind (PractitionerId),
    FOREIGN KEY (PractitionerId)
    REFERENCES practitioners(Id)
    ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `practitioners` 
(
    `Id` int(5) NOT NULL AUTO_INCREMENT,
    `Active` bool,
    `Name` JSON, -- JSON blob array of HumanName DataTypes
    `Telecom` JSON, -- JSON array of ContactPoint DataTypes
    `Address` JSON, -- JSON array of Address DataTypes
    `Gender` varchar(10),
    `BirthDate` datetime,
    `Qualification` JSON,
    `Communication` JSON, -- JSON array of CodeableConcept DataTypes
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

However I get the following error when running:

ERROR 1215: Cannot add foreign key constraint
SQL Statement:
CREATE TABLE IF NOT EXISTS documentreferences (
Id int(5) NOT NULL AUTO_INCREMENT,
Status varchar(50),
DocStatus varchar(50),
Type JSON, -- single CodeableConcept DataType
Class JSON, -- single CodeableConcept DataType
Created datetime,
Indexed datetime,
Description varchar(50),
SecurityLabel JSON, -- array of CodeableConcept DataTypes
ContextEvent JSON, -- array of CodeableConcept DataTypes
ContextPeriodStart datetime,
ContextPeriodEnd datetime,
ContextFacilityType JSON, -- single CodeableConcept DataType
ContextPracticeSetting JSON, -- single CodeableConcept DataType
PRIMARY KEY (id),
SubjectId int(5),
INDEX subject_ind (SubjectId),
FOREIGN KEY (SubjectId)
REFERENCES patients(Id)
ON DELETE CASCADE,
-- ** FK below causes issue **
PractitionerId int(5),
INDEX practitioner_ind (PractitionerId),
FOREIGN KEY (PractitionerId)
REFERENCES practitioners(Id)
ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

When removing the FK on the practitioners table, the schema runs. What is wrong with my current schema that would be causing the error? Any insight is appreciated

FOREIGN KEY (PractitionerId) REFERENCES practitioners(Id) ON DELETE CASCADE FOREIGN KEY (PractitionerId) it don't see a PractitionerId column within your documentreferences table? — Raymond Nijland
the PractitionerId column is defined in the documentreference table here right? : -- ** FK below causes issue ** PractitionerId int(5), INDEX practitioner_ind (PractitionerId), FOREIGN KEY (PractitionerId) REFERENCES practitioners(Id) ON DELETE CASCADE — Funsaized
right i see it now i was expecting the column between the other columns not between the indexes — Raymond Nijland
try to create table again with the FOREIGN KEY and use SHOW ENGINE INNODB STATUS after it might gives us a better error message. — Raymond Nijland
Issue was I created the practitioners table below the table I was referencing as a foreign key. Thanks for your help! — Funsaized

McBlarg McBlarg · Accepted Answer · 2018-03-23T14:40:14

SQL likes to have things in order. You need to declare all your tables prior to linking them with foreign key relations. Simply move the practitioners table creation above the documentreferences table and it'll work.

MySQL Create Schema with foreign keys throws error 1215

1 Answers