Menu
SQL FOREIGN KEY ERROR (errno: 150 "Foreign key constraint is incorrectly formed") The problem is that you have the company table reference the branch and contact table before they are created.
Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table. Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
I ran into this same problem with HeidiSQL. The error you receive is very cryptic. My problem ended up being that the foreign key column and the referencing column were not of the same type or length.
The foreign key column was SMALLINT(5) UNSIGNED and the referenced column was INT(10) UNSIGNED. Once I made them both the same exact type, the foreign key creation worked perfectly.
I had the same problem when the parent table was created using MyISAM engine. It's a silly mistake, which I fixed with:
ALTER TABLE parent_table ENGINE=InnoDB;
For anyone facing this problem, just run
SHOW ENGINE INNODB STATUS
and see the LATEST FOREIGN KEY ERROR section for details.
make sure columns are identical(of same type) and if reference column is not primary_key, make sure it is INDEXED.
Syntax for defining foreign keys is very forgiving, but for anyone else tripping up on this, the fact that foreign keys must be "of the same type" applies even to collation, not just data type and length and bit signing.
Not that you'd mix collation in your model (would you?) but if you do, be sure your primary and foreign key fields are of the same collation type in phpmyadmin or Heidi SQL or whatever you use.
Hope this saves you the four hours of trial and error it cost me.
I had same problem, but solved it.
Just make sure that column 'ID' in 'table1' has UNIQUE index!
And of course the type, length of columns 'ID' and 'IDFromTable1' in these two tables has to be same. But you already know about this.
mysql error texts doesn't help so much, in my case, the column had "not null" constraint, so the "on delete set null" was not allowed
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