Reputation: 9398

mysql Foreign key constraint is incorrectly formed error

table1 is the parent table with a column ID and table2 has a column IDFromTable1.

Why when I put a FK on IDFromTable1 to ID in table1 do I get Foreign key constraint is incorrectly formed error?

(I would like to delete the table2 record if the table1 record gets deleted.)

ALTER TABLE `table2`  
   ADD CONSTRAINT `FK1` 
      FOREIGN KEY (`IDFromTable1`) REFERENCES `table1` (`ID`) 
      ON UPDATE CASCADE 
      ON DELETE CASCADE;

Both tables' engines are InnoDB. Both colmnns are type char. ID is the primary key in table1.

Upvotes: 312

Answers (30)

Ndi Cedric

Reputation: 73

Another problem could be that your foreign key contraint has inconsistencies in it.

Here is what happened in my case with laravel.

$table->id();
$table->foreignId('product_id')->constrained()->cascadeOnDelete();
$table->foreignId('category_id')->constrained()->nullOnDelete();
$table->timestamps();

This will trigger the error above because I am trying to set on delete to be null for category_id. But the column is not nullable.

To resolve this just add ->nullable() to the category_id column.

$table->foreignId('product_id')->constrained()->cascadeOnDelete();
$table->foreignId('category_id')->nullable()->constrained()->nullOnDelete();

Upvotes: 0

Jake Wilson

Reputation: 91233

I ran into this same cryptic error. My problem was 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

The referenced column was INT(10) UNSIGNED

Once I made them both the same exact type, the foreign key creation worked perfectly.

Upvotes: 672

Lee

Reputation: 487

Another Laravel issue can be the order in the down method, you have to drop the referencing table first. Hope that makes sence !

Upvotes: 0

Ruben Daddario

Reputation: 1122

errno: 150 "Foreign key constraint is incorrectly formed also appears when you try to reference a key from a partitioned table. Remember that you cannot have references to the pk of partitioned tables.

Upvotes: 0

C.F.G

Reputation: 1463

Table Name is CASE Sensitive:

In my case, the problem was incorrect table name. My table name was Users and I wrongly defined the foreign key as .. REFERENCES user(id). (Note the uppercase U in user).

Upvotes: 0

josef

Reputation: 972

if everything is ok, just add ->unsigned(); at the end of foreign key.

if it does not work, check the datatype of both fields. they must be the same.

Upvotes: 14

Sidonai

Reputation: 3716

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

Upvotes: 182

afshindadashnezhad

Reputation: 457

If U Table Is Myisum And New Table Is InoDb you Are Note Foreign You Must Change MyIsum Table To InoDb

Upvotes: 0

Luca C.

Reputation: 12594

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

Upvotes: 21

Sam Si Tayeb

Reputation: 83

For anyone struggling as I was with this issue, this was my problem:

I was trying to alter a table to change a field from VARCHAR(16) to VARCHAR(255) and this was referencing another table column where the datatype was still VARCHAR(16)...

Upvotes: 2

Emad Easa

Reputation: 89

One more solution which I was missing here is, that each primary key of the referenced table should have an entry with a foreign key in the table where the constraint is created.

Upvotes: 0

pollux1er

Reputation: 5929

I had the same error, and I discovered that on my own case, one table was MyISAM, and the other one INNO. Once I switched the MyISAM table to INNO. It solved the issue.

Upvotes: 0

Ivan Stasiuk

Reputation: 292

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

Upvotes: 7

Javad mosavi

Reputation: 59

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

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

Upvotes: 0

shaher11

Reputation: 140

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->increments('id');
            $table->string('product_name');
        });

table 2:

Schema::create('brands', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('brand_name');
        });

the data type for id of the second table must be increments

Upvotes: 2

Delian Krustev

Reputation: 2906

My case was that I had a typo on the referred column:

MariaDB [blog]> 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]> 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

Upvotes: 3

Alexander Nenartovich

Reputation: 826

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.

Upvotes: 0

Takman

Reputation: 1076

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.

Upvotes: 12

Jimwel Anobong

Reputation: 486

(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

TBL NAME | DATA TYPE | COLLATION

ActivityID | INT | latin1_general_ci ActivityID | INT | utf8_general_ci

Try Changing it into

TBL NAME | DATA TYPE | COLLATION

ActivityID | INT | latin1_general_ci ActivityID | INT | latin1_general_ci

....

This worked for me.

Upvotes: 7

Dexter

Reputation: 9344

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.

Upvotes: -1

Kaya Toast

Reputation: 5513

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.

Upvotes: 11

izogfif

Reputation: 7565

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

 CONSTRAINT `FK_PURCHASE_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `CUSTOMER` (`id`) ON UPDATE CASCADE ON DELETE CASCADE

note the customer changed to CUSTOMER.

Upvotes: -1

LukaszTaraszka

Reputation: 859

I lost for hours for that!

PK in one table was utf8 in other was utf8_unicode_ci!

Upvotes: 4

Ashish Kathait

Reputation: 316

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:

    <changeset id="XXXXXXXXXXX-1" author="xyz">
            <column name="referenced_id" **type="varchar"**>
        </column>
            </changeset>
    <changeSet id="XXXXXXXXXXX-2" author="xyz">
                <addForeignKeyConstraint constraintName="FK_table_A"
                    referencedTableName="A" **baseColumnNames="referenced_id**"
                    referencedColumnNames="id" baseTableName="B" />
    </changeSet>

Table A changeSet:

    <changeSet id="YYYYYYYYYY" author="xyz">
     <column **name="id"** **type="bigint"** autoIncrement="${autoIncrement}">
                    <constraints primaryKey="true" nullable="false"/>
                </column>
    </changeSet>

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.

Upvotes: -1

Doug

Reputation: 6497

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.

Upvotes: 1

mariobigboy

Reputation: 71

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

Upvotes: 7

Ahmad Baktash Hayeri

Reputation: 5880

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,            /* <<== 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;

Upvotes: 4

longluffy

Reputation: 21

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.

Upvotes: 2

Christopher Adams

Reputation: 1300

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.

Upvotes: 0

Bladimir Arias Sabogal

Reputation: 21

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;

Upvotes: 2

mysql Foreign key constraint is incorrectly formed error

Answers (30)

Table Name is CASE Sensitive:

Solution

Related Questions