Unable to create foreign key mysql

Question

Error code: 1215: cannot add foreign key constraint

I have tried all options like changing the default storage engine using

SET default_storage_engine=ENGINE

But I'm unable to create this foreign key constraints. I'm using Mysql 5.5. Anybody can help me out.

create table if not exists pallets(
    palletId serial,
    goodsType varchar(25),
    desitination varchar(25),
    primary key (palletId)
);
create table if not exists storage(
    id serial,
    palletId integer,
    primary key (id),
    constraint FK _Pallet foreign key (palletId) REFERENCES pallets(palletId)
);

The 2 columns have to have the same datatype. `Integer` is not the same as `serial`. So try `bigint unsigned` instead of `integer` (or use `integer` in `pallets`). — Solarflare, Jan 09 '18 at 22:01

score 3 · Accepted Answer · answered Jan 09 '18 at 22:14

The datatype of the foreign key column(s) must match the datatype of the referenced (usually primary key) column(s) in the referenced table.

Error 1215 is the expected behavior when we attempt to create a foreign key constraint that would violate this restriction.

According to the MySQL Reference Manual https://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

So the table definition for pallets is equivalent to:

create table pallets
( palletId   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
  --         ^^^^^^^^^^^^^^^  
, ...
, primary key (palletId)
);

A foreign key reference will need to be of the same datatype BIGINT UNSIGNED

e.g.

create table storage
( id ...
, palletId   BIGINT UNSIGNED 
  --         ^^^^^^^^^^^^^^^
, primary key (id)
, constraint FK_storage_pallets foreign key (palletId) REFERENCES pallets(palletId)
);

score 0 · Answer 2 · answered Sep 28 '20 at 04:59

If you use SERIAL datatype as primary key, the foreign key should be BIGINT UNSIGNED

create table if not exists pallets(
    palletId serial,
    goodsType varchar(25),
    desitination varchar(25),
    primary key (palletId)
);
create table if not exists storage(
    id serial,
    palletId bigint unsigned,
    primary key (id),
    constraint FK _Pallet foreign key (palletId) REFERENCES pallets(palletId)
);

Unable to create foreign key mysql

Error code: 1215: cannot add foreign key constraint

2 Answers2