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

Leave a Reply

Discover more from SmartTechWays - Innovative Solutions for Smart Businesses

Subscribe now to keep reading and get access to the full archive.

Continue reading