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:
- Verify the data type of column should be matched like both have INT Type.
- Verify the data type length should be matched.
- 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.
- If you do not have a primary index then you need to create another index on both columns