MySQL Cannot Add Foreign Key Constraint

MySQL Cannot Add Foreign Key Constraint

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;  

Answer by Ike Walker for MySQL Cannot Add Foreign Key Constraint

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.

Answer by Felypp Oliveira for MySQL Cannot Add Foreign Key Constraint

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

Hope it helps!

Answer by bhaskar bhatt for MySQL Cannot Add Foreign Key Constraint

Check following rules :

• First checks whether names are given right for table names

• Second right data type give to foreign key ?

Answer by iltaf khalid for MySQL Cannot Add Foreign Key Constraint

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  );  

Answer by user3707075 for MySQL Cannot Add Foreign Key Constraint

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`)  

Answer by ???? ????????????? for MySQL Cannot Add Foreign Key Constraint

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)

Answer by Satsara Gunaratne for MySQL Cannot Add Foreign Key Constraint

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

Answer by Luiz Rolim for MySQL Cannot Add Foreign Key Constraint

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

Answer by Singh for MySQL Cannot Add Foreign Key Constraint

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. (http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html )

Answer by Vijay Srinivas for MySQL Cannot Add Foreign Key Constraint

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.

Answer by subramanian for MySQL Cannot Add Foreign Key Constraint

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.

Answer by lsblsb for MySQL Cannot Add Foreign Key Constraint

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.

Answer by Andrew for MySQL Cannot Add Foreign Key Constraint

• 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 private key) in the reference table.

Answer by Learner for MySQL Cannot Add Foreign Key Constraint

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 .

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72