SQL中如何引用主键为复合元组的表并创建关联外键

核心问题原因 #

外键约束要求关联字段必须和被关联表的主键/唯一键的字段组合完全匹配，你之前的写法是将三个字段分别单独关联film表的单个列，但这三个列单独在film表中没有唯一约束，因此无法通过外键校验。

方案1：不修改现有film表结构，直接定义复合外键 #

直接在review表的末尾统一声明复合外键，关联film表的完整复合主键即可，建表语句如下：

create table review 
(
    text varchar(100), 
    title varchar(20) not null, 
    year int not null, 
    creator int not null,
    -- 声明三个字段联合作为外键，匹配film的复合主键
    foreign key (title, year, creator) references film(title, year, creator)
);

方案2：优化表结构，新增单列主键（更推荐） #

工业界通常会给每张表加一个无业务意义的单列自增主键，把原来的业务复合主键改成唯一约束来保证数据唯一性，后续关联会更简洁：

1. 修改后的film表结构：

create table film 
(
    film_id int primary key auto_increment, -- 新增单列自增主键
    title varchar(20) not null, 
    year int not null, 
    creator int not null, 
    -- 原复合主键改为唯一约束，保证业务数据不会重复
    unique key uk_film_title_year_creator (title, year, creator)
);

2. 对应的review表结构：

create table review 
(
    review_id int primary key auto_increment, -- 可选，给review也加主键方便后续操作
    text varchar(100), 
    film_id int not null,
    foreign key (film_id) references film(film_id)
);

两种方案适用场景 #

• 方案1适合无法改动现有film表结构的存量业务场景，缺点是关联、查询时都需要携带三个字段，SQL编写较繁琐，冗余字段多
• 方案2是更推荐的设计，单列主键关联逻辑更简洁，后续新增其他关联表时复用性更高，关联查询性能也更好

内容的提问来源于stack exchange，提问作者KrabbyPatty