Why is adding a foreign key on the pivot table with different integer types not possible?

Question

Let us assume that we have a products table and a orders table. The products table have a primary key (tinyint) and the orders table have a primary key (int).

Try adding the foreign key:

alter table `products_orders`
add constraint `products_orders_products_id_foreign` foreign key (`products_id`)
references `products` (`id`) on delete cascade;

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

You should include the create table statements, or meaningful abbreviations of them, for both the `products_orders` and `products` tables. This error could be caused by a handful of things. — Tim Biegeleisen, Aug 18 '18 at 16:40

score 1 · Answer 1 · answered Aug 18 '18 at 16:48

1

Foreign key reference columns data types must be exactly the same (including not null and unsigned flags etc). This avoids any ambiguity when it comes to comparison.

I would recommend that any foreign key columns should be type Int and unsigned.

answered Aug 18 '18 at 16:48

Simon R

3,732
4
31
39

You mean the type of the column on the parent table referenced in the child table must be the same? – Oluwatobi Samuel Omisakin Aug 18 '18 at 20:28
Yes that's what I mean. – Simon R Aug 18 '18 at 20:29
One of the reasons I suspect is that, when the parent id grows bigger, and a reference is needed in the child table, then since the child table holds smaller size column it cannot save the relation thus breaking the possibility of adding the relation. – Oluwatobi Samuel Omisakin Aug 18 '18 at 20:34
Nullability may be different (null is a value, not a type) – Shadow Aug 18 '18 at 22:32

Why is adding a foreign key on the pivot table with different integer types not possible?

1 Answers1