MySQL Error 1215: Cannot add foreign key constraint

I am trying to forward engineer my new schema onto my database server, but I can't figure out why I am getting this error.

I've tried to search for the answer here, but everything I've found has said to either set the database engine to InnoDB or to make sure the keys I'm trying to use as a foreign key are primary keys in their own tables. I have done both of these things, if I'm not mistaken. What else can I do?

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

-- -----------------------------------------------------
-- Table `Alternative_Pathways`.`Clients_has_Staff`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients_has_Staff` (
  `Clients_Case_Number` INT NOT NULL ,
  `Staff_Emp_ID` INT NOT NULL ,
  PRIMARY KEY (`Clients_Case_Number`, `Staff_Emp_ID`) ,
  INDEX `fk_Clients_has_Staff_Staff1_idx` (`Staff_Emp_ID` ASC) ,
  INDEX `fk_Clients_has_Staff_Clients_idx` (`Clients_Case_Number` ASC) ,
  CONSTRAINT `fk_Clients_has_Staff_Clients`
    FOREIGN KEY (`Clients_Case_Number` )
    REFERENCES `Alternative_Pathways`.`Clients` (`Case_Number` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Clients_has_Staff_Staff1`
    FOREIGN KEY (`Staff_Emp_ID` )
    REFERENCES `Alternative_Pathways`.`Staff` (`Emp_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Here is the SQL for the parent tables.

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients` (
  `Case_Number` INT NOT NULL ,
  `First_Name` CHAR(10) NULL ,
  `Middle_Name` CHAR(10) NULL ,
  `Last_Name` CHAR(10) NULL ,
  `Address` CHAR(50) NULL ,
  `Phone_Number` INT(10) NULL ,
  PRIMARY KEY (`Case_Number`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Staff` (
  `Emp_ID` INT NOT NULL ,
  `First_Name` CHAR(10) NULL ,
  `Middle_Name` CHAR(10) NULL ,
  `Last_Name` CHAR(10) NULL ,
  PRIMARY KEY (`Emp_ID`) )
ENGINE = InnoDB

Solution 1:

I'm guessing that Clients.Case_Number and/or Staff.Emp_ID are not exactly the same data type as Clients_has_Staff.Clients_Case_Number and Clients_has_Staff.Staff_Emp_ID.

Perhaps the columns in the parent tables are INT UNSIGNED?

They need to be exactly the same data type in both tables.

Solution 2:

Reasons you may get a foreign key constraint error:

1. You are not using InnoDB as the engine on all tables.
2. You are trying to reference a nonexistent key on the target table. Make sure it is a key on the other table (it can be a primary or unique key, or just a key)
3. The types of the columns are not the same (an exception is the column on the referencing table can be nullable even if it is not nullable in the referenced table).
4. If the primary key or foreign key is a varchar, make sure the collation is the same for both.
5. One of the reasons may also be that the column you are using for ON DELETE SET NULL is not defined to be null. So make sure that the column is set default null.

Check these.

Solution 3:

For others, the same error may not always be due to a column type mismatch. You can find out more information about a MySQL foreign key error by issuing the command

SHOW ENGINE INNODB STATUS;

You may find an error near the top of the printed message. Something like

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

Solution 4:

Error 1215 is an annoying one. Explosion Pill's answer covers the basics. You want to make sure to start from there. However, there are more, much more subtle cases to look out for:

For example, when you try to link up PRIMARY KEYs of different tables, make sure to provide proper ON UPDATE and ON DELETE options. E.g.:

...
PRIMARY KEY (`id`),
FOREIGN KEY (`id`) REFERENCES `t` (`other_id`) ON DELETE SET NULL
....

won't fly, because PRIMARY KEYs (such as id) can't be NULL.

I am sure, there are even more, similarly subtle issues when adding these sort of constraints, which is why when coming across constraint errors, always make sure that the constraints and their implications make sense in your current context. Good luck with your error 1215!