MariaDB: ALTER TABLE syntax to add a FOREIGN KEY?

user_1774051 0
Viewed 30252

what'S wrong with the following statement?

ALTER TABLE submittedForecast
  ADD CONSTRAINT FOREIGN KEY (data) REFERENCES blobs (id);

The error message I am getting is

Can't create table `fcdemo`.`#sql-664_b` (errno: 150 "Foreign key constraint is incorrectly formed")
mariadb
5 Answers

I was getting the same issue and after looking at my table structure, found out that my child table's foreign key clause was not referencing the primary key of parent table. Once i changed it to reference to primary key of parent table, the error was gone.

user_39655240

I had the same error and is actually pretty easy to solve, you have name the constraint, something like this should do:

ALTER TABLE submittedForecast ADD CONSTRAINT `fk_submittedForecast` 
  FOREIGN KEY (data) REFERENCES blobs (id)

If you would like more cohesion also add at the end of the query

 ON DELETE CASCADE ON UPDATE RESTRICT

This could also be a different fields error so check if the table key and the foreign key are of the same type and have the same atributes.

user_108240370

I had the same problem, too. When checking the definition of the fields, I noticed that one field was defined as INT and the other as BIGINT. After changing the BIGINT type to INT, I was able to create my foreign key.

user_68697890
Related