MySQL Cannot Add Foreign Key Constraint

Question

So I'm trying to add Foreign Key constraints to my database as a project requirement and it worked the first time or two on different tables, but I have two tables on which I get an error when trying to add the Foreign Key Constraints. The error message that I get is:

ERROR 1215 (HY000): Cannot add foreign key constraint

This is the SQL I'm using to create the tables, the two offending tables are Patient and Appointment.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `doctorsoffice` DEFAULT CHARACTER SET utf8 ;
USE `doctorsoffice` ;

-- -----------------------------------------------------
-- Table `doctorsoffice`.`doctor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`doctor` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`doctor` (
  `DoctorID` INT(11) NOT NULL AUTO_INCREMENT ,
  `FName` VARCHAR(20) NULL DEFAULT NULL ,
  `LName` VARCHAR(20) NULL DEFAULT NULL ,
  `Gender` VARCHAR(1) NULL DEFAULT NULL ,
  `Specialty` VARCHAR(40) NOT NULL DEFAULT 'General Practitioner' ,
  UNIQUE INDEX `DoctorID` (`DoctorID` ASC) ,
  PRIMARY KEY (`DoctorID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`medicalhistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`medicalhistory` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`medicalhistory` (
  `MedicalHistoryID` INT(11) NOT NULL AUTO_INCREMENT ,
  `Allergies` TEXT NULL DEFAULT NULL ,
  `Medications` TEXT NULL DEFAULT NULL ,
  `ExistingConditions` TEXT NULL DEFAULT NULL ,
  `Misc` TEXT NULL DEFAULT NULL ,
  UNIQUE INDEX `MedicalHistoryID` (`MedicalHistoryID` ASC) ,
  PRIMARY KEY (`MedicalHistoryID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`Patient`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Patient` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`Patient` (
  `PatientID` INT unsigned NOT NULL AUTO_INCREMENT ,
  `FName` VARCHAR(30) NULL ,
  `LName` VARCHAR(45) NULL ,
  `Gender` CHAR NULL ,
  `DOB` DATE NULL ,
  `SSN` DOUBLE NULL ,
  `MedicalHistory` smallint(5) unsigned NOT NULL,
  `PrimaryPhysician` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`PatientID`) ,
  UNIQUE INDEX `PatientID_UNIQUE` (`PatientID` ASC) ,
  CONSTRAINT `FK_MedicalHistory`
    FOREIGN KEY (`MEdicalHistory` )
    REFERENCES `doctorsoffice`.`medicalhistory` (`MedicalHistoryID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_PrimaryPhysician`
    FOREIGN KEY (`PrimaryPhysician` )
    REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`Appointment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Appointment` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`Appointment` (
  `AppointmentID` smallint(5) unsigned NOT NULL AUTO_INCREMENT ,
  `Date` DATE NULL ,
  `Time` TIME NULL ,
  `Patient` smallint(5) unsigned NOT NULL,
  `Doctor` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`AppointmentID`) ,
  UNIQUE INDEX `AppointmentID_UNIQUE` (`AppointmentID` ASC) ,
  CONSTRAINT `FK_Patient`
    FOREIGN KEY (`Patient` )
    REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_Doctor`
    FOREIGN KEY (`Doctor` )
    REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`InsuranceCompany`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`InsuranceCompany` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`InsuranceCompany` (
  `InsuranceID` smallint(5) NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(50) NULL ,
  `Phone` DOUBLE NULL ,
  PRIMARY KEY (`InsuranceID`) ,
  UNIQUE INDEX `InsuranceID_UNIQUE` (`InsuranceID` ASC) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `doctorsoffice`.`PatientInsurance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`PatientInsurance` ;

CREATE  TABLE IF NOT EXISTS `doctorsoffice`.`PatientInsurance` (
  `PolicyHolder` smallint(5) NOT NULL ,
  `InsuranceCompany` smallint(5) NOT NULL ,
  `CoPay` INT NOT NULL DEFAULT 5 ,
  `PolicyNumber` smallint(5) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`PolicyNumber`) ,
  UNIQUE INDEX `PolicyNumber_UNIQUE` (`PolicyNumber` ASC) ,
  CONSTRAINT `FK_PolicyHolder`
    FOREIGN KEY (`PolicyHolder` )
    REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_InsuranceCompany`
    FOREIGN KEY (`InsuranceCompany` )
    REFERENCES `doctorsoffice`.`InsuranceCompany` (`InsuranceID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `doctorsoffice` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Quietly

Answer 1

To find the specific error run this:

SHOW ENGINE INNODB STATUS;

And look in the LATEST FOREIGN KEY ERROR section.

The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT.

Also, you should run the query set foreign_key_checks=0 before running the DDL so you can create the tables in an arbitrary order rather than needing to create all parent tables before the relevant child tables.

Galactose

Thanks, both the data type inconsistency and foreign_key_checks fixed the issue! – Quietly

Was caused by a different collation on the tables for me, one was UTF-8 and the other was latin1. – Dali

Too Good to add ' set foreign_key_checks=0; ' and done. – Yodel

Also had to make sure I had checked "unsigned" since this was an unsigned INT even though my types and length matched. – Quantum

My tables were automatically being created with the MyISAM engine! Thanks Ike. – Squinch

Thanks. I was trying to set null on delete, but the column was not null. – Verret

ah this was useful. They could return this error directly instead of that super useful generic one... – Gunar

In my case, the database was created with a "utf8" default character set and "utf8_unicode_ci" collation. However, I received the error when creating a new table WITHOUT the COLLATE clause. I hadn't had to include the COLLATE clause in the past. Did something change? The version I am using is: Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64) – Sear

I receive no output from this command, even though both tables use InnoDB. – Bloxberg

SHOW WARNINGS; Also give you distilled answer! – Doings

Ran into that because DBeaver likes to create varchar(100) as default and I missed to set it to INT UNSIGNED. – Ailina

The column should have been indexed in the parent table. You are genius. I had seen answers suggesting going through hell large log files but, this took less than a minute. – Frager

Thanks a lot, my issue was that the data type for the child column was not matching the parent column. – Katricekatrina

Answer 2

I had set one field as "Unsigned" and other one not. Once I set both columns to Unsigned it worked.

Goethe

lol same. MySQL could use more precise error handling on this kind of stuff. – Norbertonorbie

For me the target table was blocking the foreign key. I had to set Auto-Increment (AI) on the table the Foreign-Key was pointing to. – Jennajenne

Answer 3

• Engine should be the same e.g. InnoDB
• Datatype should be the same, and with same length. e.g. VARCHAR(20)
• Collation Columns charset should be the same. e.g. utf8
  Watchout: Even if your tables have same Collation, columns still could have different one.
• Unique - Foreign key should refer to field that is unique (usually primary key) in the reference table.

Celinacelinda

Best Answer Ever, After trying almost everything, it turned out that I have to explicitly add unique to the reference table column even though it's a Primary Key!! – Rogozen

Yes, best Answer ever ... in particular fir first point! In my case I made a migration (booked 2.5.14 to bookd 2.7.2), where the migration script did not change the database engine, so when creating new tables I got this error. – Limber

Best answer me too. – Teresaterese

Would be even more awesome with tips for how to check/change. For me it was a column-level Collation difference (thanks for the idea!) and this gave me the fix: #1294617 – Hypozeugma

I had datatype issue. ID on the parent table had BigInt(20) whereas the column on child table had Int(10). I changed Int(10) to BigInt(20) and was then able to create the foreign-key constraint. – Breakwater

In a legacy database structure, the table that I was pointing to had MyIsam as Engine, thanks for the info. – Groggery

For me, all of these points check out (ig), and I still cannot add foreign key constraint, I am not really sure what the mistake is – Blastula

Answer 4

Try to use the same type of your primary keys - int(11) - on the foreign keys - smallint(5) - as well.

Hope it helps!

Tartar

mysql> create unique index index_bar_id on foos(bar_id); ... mysql> alter table foos add constraint index_bar_id foreign key (bar_id) references bars (id); sixarm.com/about/… – Postglacial

Answer 5

Confirm that the character encoding and collation for the two tables is the same.

In my own case, one of the tables was using utf8 and the other was using latin1.

I had another case where the encoding was the same but the collation different. One utf8_general_ci the other utf8_unicode_ci

You can run this command to set the encoding and collation for a table.

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I hope this helps someone.

Beverie

IMPORTANT not all utf8 encoding is the same. This answer is important. Make very sure that the character set is EXACTLY the same before trying to add the foreign key, because if altering the table fails on a production database, fixing it will probably involve downtime. – Colp

Answer 6

To set a FOREIGN KEY in Table B you must set a KEY in the table A.

In table A: INDEX id (id)

And then in the table B,

CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `table-A` (`id`)

Octavia

i'm not sure exactly what you are saying, but I found that my syntax was incorrect. I was doing: alter table aircraft add constraint fk_somehting_unique foreign key (operator_id) references organisation, but should have done: alter table aircraft add constraint fk_somehting_unique foreign key (operator_id) references organisation (id); – Burnham

Answer 7

I had same problem and the solution was very simple. Solution : foreign keys declared in table should not set to be not null.

reference : If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL. (ref )

Gaia

Answer 8

Check following rules :

• First checks whether names are given right for table names

• Second right data type give to foreign key ?

Bordiuk

Answer 9

Please ensure that both the tables are in InnoDB format. Even if one is in MyISAM format, then, foreign key constraint wont work.

Also, another thing is that, both the fields should be of the same type. If one is INT, then the other should also be INT. If one is VARCHAR, the other should also be VARCHAR, etc.

Elma

Answer 10

I faced the issue and was able to resolve it by making sure that the data types were exactly matching .

I was using SequelPro for adding the constraint and it was making the primary key as unsigned by default .

Elide

Answer 11

Check the signing on both your table columns. If the referring table column is SIGNED, the referenced table column should be SIGNED too.

Lait

Answer 12

My problem was that I was trying to create the relation table before other tables!

So you have two ways to fix it:

• change the order of MSQL commands

• run this before your queries:

  SET foreign_key_checks = 0;

Taction

SET foreign_key_checks = 0; – Witkowski

Answer 13

NOTE: The following tables were taken from some site when I was doing some R&D on the database. So the naming convention is not proper.

For me, the problem was, my parent table had the different character set than that of the one which I was creating.

Parent Table (PRODUCTS)

products | CREATE TABLE `products` (
  `productCode` varchar(15) NOT NULL,
  `productName` varchar(70) NOT NULL,
  `productLine` varchar(50) NOT NULL,
  `productScale` varchar(10) NOT NULL,
  `productVendor` varchar(50) NOT NULL,
  `productDescription` text NOT NULL,
  `quantityInStock` smallint(6) NOT NULL,
  `buyPrice` decimal(10,2) NOT NULL,
  `msrp` decimal(10,2) NOT NULL,
  PRIMARY KEY (`productCode`),
  KEY `productLine` (`productLine`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Child Table which had a problem (PRICE_LOGS)

price_logs | CREATE TABLE `price_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` varchar(15) DEFAULT NULL,
  `old_price` decimal(20,2) NOT NULL,
  `new_price` decimal(20,2) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
);

MODIFIED TO

price_logs | CREATE TABLE `price_logs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productCode` varchar(15) DEFAULT NULL,
  `old_price` decimal(20,2) NOT NULL,
  `new_price` decimal(20,2) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `productCode` (`productCode`),
  CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Ketose

Answer 14

One additional cause of this error is when your tables or columns contain reserved keywords:

Sometimes one does forget these.

Britisher

Answer 15

In my case, there was a syntax error which was not explicitly notified by MySQL console upon running the query. However, SHOW ENGINE INNODB STATUS command's LATEST FOREIGN KEY ERROR section reported,

  Syntax error close to:

  REFERENCES`role`(`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8

I had to leave a whitespace between REFERENCES and role to make it work.

Ventilation

Answer 16

If you are getting this error with PhpMyAdmin, disable foreign key checks before importing the SQL file.

Martinmartina

Answer 17

For me the target table was blocking the foreign key. I had to set Auto-Increment (AI) on the table the Foreign-Key was pointing to.

Jennajenne

Answer 18

I had a similar error in creating foreign key in a Many to Many table where the primary key consisted of 2 foreign keys and another normal column. I fixed the issue by correcting the referenced table name i.e. company, as shown in the corrected code below:

create table company_life_cycle__history -- (M-M)
(
company_life_cycle_id tinyint unsigned not null,
Foreign Key (company_life_cycle_id) references company_life_cycle(id) ON DELETE    CASCADE ON UPDATE CASCADE,
company_id MEDIUMINT unsigned not null,
Foreign Key (company_id) references company(id) ON DELETE CASCADE ON UPDATE CASCADE,
activity_on date NOT NULL,
PRIMARY KEY pk_company_life_cycle_history (company_life_cycle_id, company_id,activity_on),
created_on datetime DEFAULT NULL,
updated_on datetime DEFAULT NULL,
created_by varchar(50) DEFAULT NULL,
updated_by varchar(50) DEFAULT NULL
);

Yahoo

Answer 19

I had similar error with two foreign keys for different tables but with same key names! I have renamed keys and the error had gone)

Dianoia

Answer 20

Had a similar error, but in my case I was missing to declare the pk as auto_increment.

Just in case it could be helpful to anyone

Stav

Answer 21

I got the same error. The cause in my case was:

1. I created a backup of a database via phpmyadmin by copying the whole database.
2. I created a new db with the same name the old db had und selected it.
3. I started an SQL script to create updated tables and data.
4. I got the error. Also when I disabled foreign_key_checks. Altough the database was completely empty.

The cause was: Since i used phpmyadmin to create some foreign keys in the renamed database - the foreign keys where created with a database name prefix but the database name prefix was not updated. So there were still references in the backup-db pointing to the newly created db.

Ashburn

Answer 22

My solution is maybe a little embarrassing and tells the tale of why you should sometimes look at what you have in front of you instead of these posts :)

I had ran a forward engineer before, which failed, so that meant that my database already had a few tables, then i have been sitting trying to fix foreign key contraints failures trying to make sure that everything was perfect, but it ran up against the tables previously created, so it was to no prevail.

Kravits

Answer 23

For me it was - you can't omit prefixing the current DB table if you create a FK for a non-current DB referencing the current DB:

USE currrent_db;
ALTER TABLE other_db.tasks ADD CONSTRAINT tasks_fk FOREIGN KEY (user_id) REFERENCES currrent_db.users (id);

If I omit "currrent_db." for users table, I get the FK error. Interesting that SHOW ENGINE INNODB STATUS; shows nothing in this case.

Panic

Answer 24

My Solution!!

If we want to have column1 of table1 as a foreign key of table2, then column1 should be a key of table1.

For example, consider we have departments table, which has dept_id column.

Now let's say we have another table named employees which has emp_dept_id column.

If we want to use the dept_id column of the department table as a foreign key for the emp_dept_id column of emp, then the dept_id of department table SHOULD ATLEAST BE a key if not a primary key.

So make sure that dept_id of depratment is either a primary key or a unique key before using it as a foreign key for another table.

Sash

Answer 25

If your database is generated by some ORM and code is in development then you should compare character sets in database, table and columns.

Any different for creating FK will be fault.
For the first look it's fine but e.g. utf8_general_ci and utf8_general_ci it's not the same.
That was my case.

My steps:

Disable checking FK's

SET FOREIGN_KEY_CHECKS = 0;

Run queries

Copy and run all queries in your console:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS alter_table_statement
FROM information_schema.tables
WHERE table_schema = 'YOUR_DATABASE_NAME'

Enable checking FK's

SET FOREIGN_KEY_CHECKS = 0;

!!! Do not run this code on production :D

Shoring

Answer 26

I had this same issue then i corrected the Engine name as Innodb in both parent and child tables and corrected the reference field name FOREIGN KEY (c_id) REFERENCES x9o_parent_table(c_id)
then it works fine and the tables are installed correctly. This will be use full for someone.

Erythema