Menu
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 
865
How to fix: Modify the child table DDL so that it matches the character set and collation of the parent table/column (or ALTER the parent table to match the child's wanted definition.
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB. Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
Reasons you may get a foreign key constraint error: You are not using InnoDB as the engine on all tables. 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 )
Here's the syntax to create foreign key in MySQL. ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_name,...) REFERENCES parent_table(column_name,...); In the above query, table_name is the the table where you want to add foreign key.
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.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
