Failed to add the foreign key constraint in MySQL: error 3780

Question

I am getting the error:

Error Code: 3780. Referencing column 'category' and referenced column 'category_id' in foreign key constraint 'product_ibfk_1' are incompatible.

drop table if exists Provider;
drop table if exists Category;
drop table if exists Product;

create table Provider
 (
 privider_id serial not null primary key, 
 login_password varchar(20) not null
        constraint passrule3 check(login_password sounds like '[A-Za-z0-9]{6,20}'),
 fathersname varchar(20) not null,
 name_of_contact_face varchar(10) not null,
 surname varchar(15), 
 e_mail varchar(25) unique
        constraint  emailrule2 check(e_mail sounds like  '[A-Za-z0-9]{10,10})\@gmail.com\s?')
 );
 
create table Category
(
title varchar(20),
category_id serial not null primary key
);
create table Product
(
 barecode serial not null primary key, 
 provider_id bigint not null, 
 manufacturer varchar(25) not null, 
 category_id bigint not null, 
 dimensions varchar(10) not null, 
 amount int not null,
 date_of_registration datetime not null,
 #constraint 'provider_for_product'
 foreign key (provider_id) references Provider (provider_id) on delete restrict on update cascade,
 foreign key (category_id) references Category (category_id) on delete restrict on update cascade
);

Does this answer your question? [MySQL Creating tables with Foreign Keys giving errno: 150](https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — philipxy, Nov 13 '22 at 22:43

score 0 · Answer 1 · answered Nov 13 '22 at 21:34

0

The datatypes of the two columns referenced in a foreign key constraint need to match

answered Nov 13 '22 at 21:34

NickW

8,430
2
6
19

score 0 · Answer 2 · answered Nov 13 '22 at 22:22

In MySQL, SERIAL is an alias for BIGINT UNSIGNED AUTO_INCREMENT.

To make a foreign key that references this column, it must be BIGINT UNSIGNED, not a signed BIGINT.

You might like to view a checklist of foreign key mistakes I contributed to: https://stackoverflow.com/a/4673775/20860

I also cover foreign key mistakes in more detail in a chapter of my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Failed to add the foreign key constraint in MySQL: error 3780

2 Answers2