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.coms?')
);
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
);
2
Answers
The datatypes of the two columns referenced in a foreign key constraint need to match
In MySQL,
SERIAL
is an alias forBIGINT UNSIGNED AUTO_INCREMENT
.To make a foreign key that references this column, it must be
BIGINT UNSIGNED
, not a signedBIGINT
.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.