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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

I got the same error  The cause in my case was    I created a backup of a database via phpmyadmin by copying the whole database  I created a new db with the same name the old db had und selected it  I started an SQL script to create updated tables and data  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

User · Answer

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

User · Answer

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

User · Answer

Check following rules     First checks whether names are given right for table names Second right data type give to foreign key

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

NOTE  The following tables were taken from some site when I was doing   some R amp 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

User · Answer

Try to use the same type of your primary keys - int 11  - on the foreign keys - smallint 5  - as well   Hope it helps

User · Answer

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

User · Answer

One additional cause of this error is when your tables or columns contain reserved keywords   Sometimes one does forget these

User · Answer

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

[mysql] MySQL Cannot Add Foreign Key Constraint

Examples related to mysql

Examples related to sql

Examples related to foreign-keys