未能在MySQL中添加外键约束:错误3780

Question

我发现了一个错误：

错误代码: 3780。在外键约束'category_id‘中引用列’类别‘和引用列'product_ibfk_1’是不兼容的。

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

Answer 1

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

Answer 2

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

若要创建引用此列的外键，必须是BIGINT UNSIGNED，而不是签名的BIGINT。

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

我还在我的书“SQL Antipatterns第1卷:避免数据库编程的缺陷”的一章中更详细地介绍了外键错误。