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

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
);

CodePudding user response：

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

CodePudding user response：

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.