mysql Foreign key constraint is incorrectly formed error

Question

I have two tables  table1 is the parent table with a column ID and table2 with a column IDFromTable1  not the actual name  when I put a FK on IDFromTable1 to ID in table1 I get the error Foreign key constraint is incorrectly formed error  I would like to delete table 2 record if table1 record gets deleted  Thanks for any help  ALTER TABLE  table2       ADD CONSTRAINT  FK1         FOREIGN KEY   IDFromTable1   REFERENCES  table1    ID          ON UPDATE CASCADE        ON DELETE CASCADE    Let me know if any other information is needed  I am new to mysql

User · Answer

I ran into the same issue just now  In my case  all I had to do is to make sure that the table I am referencing in the foreign key must be created prior to the current table  earlier in the code   So if you are referencing a variable  x 5  the system should know what x is  x must be declared in earlier lines of code   This resolved my issue  hope it ll help someone else

User · Answer

I was using HeidiSQL and to solve this problem I had to create an index in the referenced table with all the columns being referenced

User · Answer

Or you can use DBDesigner4 which has a graphical interface to create your database and linking them using FK  Right click on your table and select  Copy Table SQL Create  which creates the code

User · Answer

The problem is very simple to solve  e g  you have two table with names users and posts and you want create foreign key in posts table and you use phpMyAdmin  1  in post table add new column  name use id   type  like the id in user table   Length like the id in user table   Default NULL   Attributes unsigned   index INDEX    2 on Structure tab  go to relation view   Constraint name  auto set by phpmyAdmin   column name select user id  table users   key  id        It was simply solved     javad mosavi   iran urmia

User · Answer

This problem also occur in Laravel when you have the foreign key table table1 migration after the migration in which you reference it table2  You have to preserve the order of the migration in order to foreign key feature to work properly  database migrations 2020 01 01 00001 create table2 table php database migrations 2020 01 01 00002 create table1 table php  should be  database migrations 2020 01 01 00001 create table1 table php database migrations 2020 01 01 00002 create table2 table php

User · Answer

I face this problem the error came when you put the primary key in different data type like   table 1    Schema  create  products   function  Blueprint  table                 table- gt increments  id                 table- gt string  product name                  table 2   Schema  create  brands   function  Blueprint  table                 table- gt bigIncrements  id                 table- gt string  brand name                  the data type for id of the second table must be increments

User · Answer

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

User · Answer

Last Resent  Even if the field name and data type is the same but the collation is not the same  it will also result to that problem   For Example      nbsp  nbsp  nbsp  nbsp TBL   NAME nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp DATA   TYPE nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp      nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp COLLATION nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp        nbsp  nbsp  nbsp  nbsp ActivityID nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp INT nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp      nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp latin1 general ci    nbsp  nbsp  nbsp  nbsp ActivityID nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp INT nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp      nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp utf8 general ci   Try Changing it into      nbsp  nbsp  nbsp  nbsp TBL   NAME nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp DATA   TYPE nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp      nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp COLLATION nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp        nbsp  nbsp  nbsp  nbsp ActivityID nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp INT nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp      nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp latin1 general ci    nbsp  nbsp  nbsp  nbsp ActivityID nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp   nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp INT nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp      nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp latin1 general ci         This worked for me

User · Answer

thanks S Doerin    Just for completion  This error might be as well the case if you have a foreign key with VARCHAR     and the charset of the referenced table is different from the table referencing it  e g  VARCHAR 50  in a Latin1 Table is different than the VARCHAR 50  in a UTF8 Table    i solved this problem  changing the type of characters of the table  the creation have latin1 and the correct is utf8   add the next line  DEFAULT CHARACTER SET   utf8

User · Answer

You need check that both be same in all its properties  inclusive in  Collation

User · Answer

Try running following    show create table Parent    and check if type for both tables are the same  like myISAM or innoDB  etc   Other aspects to check with this error message  the columns used as foreign  keys must be indexed  they must be of the same type   if i e one is of type smallint 5  and the other of type smallint 6    it won t work   and  if they are integers  they should be unsigned     or check for charsets show variables like  character set database   show variables like  collation database      edited  try something like this ALTER TABLE table2 ADD CONSTRAINT fk IdTable2 FOREIGN KEY  Table1 Id  REFERENCES Table1 Table1 Id  ON UPDATE CASCADE  ON DELETE CASCADE

User · Answer

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

User · Answer

My case was that I had a typo on the referred column   MariaDB  blog  gt  alter table t user add FOREIGN KEY   country code   REFERENCES t country   coutry code    ERROR 1005  HY000   Can t create table  blog   t user   errno  150  Foreign key constraint is incorrectly formed     The error message is quite cryptic and I ve tried everything - verifying the types of the columns  collations  engines  etc   It took me awhile to note the typo and after fixing it all worked fine   MariaDB  blog  gt  alter table t user add FOREIGN KEY   country code   REFERENCES t country   country code    Query OK  2 rows affected  0 039 sec                Records  2  Duplicates  0  Warnings  0

User · Answer

if everything is ok  just add - gt unsigned    at the end of foregin key   if it does not work  check the datatype of both fields  they must be the same

User · Answer

For anyone facing this problem  just run SHOW ENGINE INNODB STATUS and see the LATEST FOREIGN KEY ERROR section for details

User · Answer

One more probable cause for the display of this error  The order in which I was creating tables was wrong  I was trying to reference a key from a table that was not yet created

User · Answer

I had the same issue  both columns were INT 11  NOT NULL but I wan t able to create the foreign key  I had to disable foreign keys checks to run it successfully    SET FOREIGN KEY CHECKS OFF  ALTER TABLE     ADD CONSTRAINT     SET FOREIGN KEY CHECKS ON    Hope this helps someone

User · Answer

It s an old subject but I discovered something  While building a MySQL workbench  it also gets the relationships of the other table  just leave the pillars you relate to  Clear other automatically added columns  This works for me

User · Answer

Just for completion   This error might be as well the case if you have a foreign key with VARCHAR     and the charset of the referenced table is different from the table referencing it   e g  VARCHAR 50  in a Latin1 Table is different than the VARCHAR 50  in a UTF8 Table

User · Answer

Check that you ve specified name of the table in the proper case  if table names are case-sensitive in your database   In my case I had to change   CONSTRAINT  FK PURCHASE customer id  FOREIGN KEY   customer id   REFERENCES  customer    id   ON UPDATE CASCADE ON DELETE CASCADE   to   CONSTRAINT  FK PURCHASE customer id  FOREIGN KEY   customer id   REFERENCES  CUSTOMER    id   ON UPDATE CASCADE ON DELETE CASCADE   note the customer changed to CUSTOMER

User · Answer

I had the same problems   The issue is the reference column is not a primary key   Make it a primary key and problem is solved

User · Answer

I had issues using Alter table to add a foreign key between two tables and the thing that helped me was making sure each column that I was trying to add a foreign key relationship to was indexed  To do this in PHP myAdmin  Go to the table and click on the structure tab  Click the index option to index the desired column as shown in screenshot     Once I indexed both columns I was trying to reference with my foreign keys  I was able to successfully use the alter table and create the foreign key relationship  You will see that the columns are indexed like in the below screenshot     notice how zip code shows up in both tables

User · Answer

Check the tables engine  both tables have to be the same engine  that helped me so much

User · Answer

I had the same issue with Symfony 2 8   I didn t get it at first  because there were no similar problems with int length of foreign keys etc   Finally I had to do the following in the project folder   A server restart didn t help    app console doctrine cache clear-metadata   app console doctrine cache clear-query  app console doctrine cache clear-result

User · Answer

I lost for hours for that   PK in one table was utf8 in other was utf8 unicode ci

User · Answer

I had the same issue with Laravel 5 1 migration Schema Builder with MariaDB 10 1   The issue was that I had typed unigned instead of unsigned the s letter was missing  while setting the column   After fixing the typo error was fixed for me

User · Answer

make sure columns are identical of same type  and if reference column is not primary key  make sure it is INDEXED

User · Answer

Even i ran into the same issue with mysql and liquibase      So this is what the problem is      The table from which you want to reference a column of other table is different either in case of datatype or in terms of size of the datatype   Error appears in below scenario  Scenario 1  Table A has column id  type bigint Table B column referenced id type varchar this column gets the value from the id column of Table A   Liquibase changeset for table B        lt changeset id  XXXXXXXXXXX-1  author  xyz  gt               lt column name  referenced id    type  varchar    gt           lt  column gt               lt  changeset gt       lt changeSet id  XXXXXXXXXXX-2  author  xyz  gt                   lt addForeignKeyConstraint constraintName  FK table A                      referencedTableName  A    baseColumnNames  referenced id                        referencedColumnNames  id  baseTableName  B    gt       lt  changeSet gt   Table A changeSet        lt changeSet id  YYYYYYYYYY  author  xyz  gt        lt column   name  id      type  bigint    autoIncrement    autoIncrement   gt                       lt constraints primaryKey  true  nullable  false   gt                   lt  column gt       lt  changeSet gt   Solution   correct the type of table B to bigint because the referenced table has type bigint   Scenrario 2  The type might be correct but the size might not  e g    Table B   referenced column type  varchar 50  Table A   base column type   varchar 255   Solution change the size of referenced column to that of base table s column size

User · Answer

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

User · Answer

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

User · Answer

Although the other answers are quite helpful  just wanted to share my experience as well  I faced the issue when I had deleted a table whose id was already being referenced as foreign key in other tables  with data  and tried to recreate import the table with some additional columns  The query for recreation  generated in phpMyAdmin  looked like the following  CREATE TABLE  the table       id  int 11  NOT NULL                No PRIMARY KEY index         name  varchar 255  NOT NULL     name fa  varchar 255  NOT NULL     name pa  varchar 255  NOT NULL   ENGINE InnoDB DEFAULT CHARSET utf8          SOME DATA DUMP OPERATION     ALTER TABLE  the table    ADD PRIMARY KEY   id       PRIMARY KEY INDEX      ADD UNIQUE KEY  uk acu donor name    name     As you may notice  the PRIMARY KEY index was set after the creation  and insertion of data  which was causing the problem  Solution The solution was to add the PRIMARY KEY index on table definition query for the id which was being referenced as foreign key  while also removing it from the ALTER TABLE part where indexes were being set  CREATE TABLE  the table       id  int 11  NOT NULL PRIMARY KEY                 lt  lt    PRIMARY KEY INDEX ON CREATION         name  varchar 255  NOT NULL     name fa  varchar 255  NOT NULL     name pa  varchar 255  NOT NULL   ENGINE InnoDB DEFAULT CHARSET utf8

User · Answer

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

[mysql] mysql Foreign key constraint is incorrectly formed error

Examples related to mysql

Examples related to sql

Examples related to heidisql