MySQL添加外键约束失败:错误3780

Question

我得到错误:

错误码:3780。外键约束"product_ibfk_1"中引用列"category"和引用列"category_id"不兼容。

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

Answer 1

外键约束中引用的两列的数据类型需要匹配

Answer 2

在MySQL中，SERIAL是BIGINT UNSIGNED AUTO_INCREMENT的别名

要创建引用该列的外键，必须是BIGINT UNSIGNED，而不是带符号的BIGINT。

您可能想查看我贡献的外键错误清单:https://stackoverflow.com/a/4673775/20860

我还在我的书《SQL反模式第1卷:避免数据库编程的陷阱》的一章中更详细地介绍了外键错误。