Can’t create table ‘tablename’ (errno: 150 “Foreign key constraint is incorrectly formed”) MariaDB

While I tried to execute a alter command for the MariaDB database to create a foreign key constraint. I am getting the error:

Can't create table 'tablename' (errno: 150 "Foreign key constraint is incorrectly formed") MariaDB

Example: I am try to create an foreign key like:
alter table Employees add constraint fk_type foreign key(emp_id,comp_id) 
    references salary(emp_id,comp_id)
    on delete cascade
    on update cascade;

But i am getting the same error: 

Reason: In my case the primary index that was created on employee table is (comp_id,emp_id). The sequence of column is different that why i am getting error. Error occurred other reasons like data type different, index not present if using other coloumn as primary key

Cause: On checking on the net, found that it has caused due to any datatype or structure change, If it does not have a primary key then it should have an index on this column which involved the foreign key

Solution:

  1. Verify the data type of column should be matched like both have INT Type.
  2. Verify the data type length should be matched.
  3. If you are using a composite key then both columns have the same used sequence in the index plus in the foreign key alter command. The sequence of columns should be the same.
  4. If you do not have a primary index then you need to create another index on both columns
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.