PostgreSQL中如何将复合主键的某一键设为另一表的外键

我是PostgreSQL新手，创建了包含复合主键的表tab1：

CREATE TABLE tab1(
col_1 int,
col_2 txt,
col_3 txt,
PRIMARY KEY (col_1,col_2)
);

尝试创建表tab2，将tab1的其中一个主键字段作为外键：

CREATE TABLE tab2 (
col_4 int,
col_5 txt,
col_6 numeric(5,2),
PRIMARY KEY (col_4),
CONSTRAINT fk_tab1 FOREIGN KEY(col_5) REFERENCES tab1(col_1) ON DELETE SET NULL
);

执行后出现错误：

ERROR:  there is no unique constraint matching given keys for referenced table "tab1"
SQL state: 42830

请问如何将复合主键中的某一键作为另一表的外键？

解决方案 #

PostgreSQL要求外键引用的字段必须具备唯一约束（主键约束属于唯一约束的一种）。tab1的(col_1, col_2)是复合主键，仅能保证两个字段的组合值唯一，但单个col_1字段可能存在重复值，因此无法直接被外键引用。你可以通过以下两种方式解决：

方法1：给tab1的col_1单独添加唯一约束 #

如果业务上col_1本身应该是唯一的，直接给该字段添加唯一约束即可：

-- 若表未创建，直接在建表时添加
CREATE TABLE tab1(
col_1 int UNIQUE,
col_2 txt,
col_3 txt,
PRIMARY KEY (col_1,col_2)
);

-- 若表已存在且无重复col_1值，执行ALTER语句添加约束
ALTER TABLE tab1 ADD CONSTRAINT unique_col1 UNIQUE (col_1);

添加约束后，再执行tab2的创建语句即可成功。

方法2：外键引用完整复合主键（业务逻辑允许时） #

如果你的业务实际需要关联tab1的复合主键组合，那么需要在tab2中同时包含对应两个字段，再引用整个复合主键：

CREATE TABLE tab2 (
col_4 int,
col_5 int, -- 对应tab1的col_1
col_6 txt, -- 对应tab1的col_2
col_7 numeric(5,2),
PRIMARY KEY (col_4),
CONSTRAINT fk_tab1 FOREIGN KEY(col_5, col_6) REFERENCES tab1(col_1, col_2) ON DELETE SET NULL
);

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