PostgreSQL中如何为table A添加双列约束限制合法的default_project_id与user_id组合？

问题背景 #

我需要在PostgreSQL中限制Table A的插入/修改操作：仅当default_project_id与user_id的组合在Table B中存在对应的(project_id, user_id)记录时，才允许执行操作。

Table A 结构（user_id 唯一，default_project_id 不唯一） #

-- table A
default_project_id    user_id    place    start         ...
1                     1          Berlin   01.01.2023    ...
1                     2          Rome     01.01.2023    ...
2                     3          Rio      ...           ...
3                     4          ...      ...           ...
3                     5          ...      ...           ...

Table B 结构（user_id 和 project_id 均不唯一） #

-- table B
project_id     user_id
1              1
2              1
3              1
2              2
5              3
4              4
9              4
1              ...

尝试直接创建外键约束时触发报错：

There is no unique constraint matching given keys for references table table B

解决方案 #

报错原因 #

PostgreSQL的外键约束要求被引用的列（或列组合）必须具备唯一约束或主键，因为外键需要确保能找到唯一匹配的关联行。你的Table B中(project_id, user_id)组合没有唯一约束，因此无法直接创建外键。

实现方式 #

方式1：给Table B添加唯一约束（推荐，业务逻辑允许时）

如果业务上(project_id, user_id)组合本就应该唯一（即同一用户不能重复关联同一项目），先给Table B添加唯一约束：

ALTER TABLE table_b ADD CONSTRAINT unique_project_user UNIQUE (project_id, user_id);

随后即可给Table A创建外键约束：

ALTER TABLE table_a ADD CONSTRAINT fk_a_b_project_user 
FOREIGN KEY (default_project_id, user_id) REFERENCES table_b (project_id, user_id);

方式2：使用CHECK约束+自定义函数（无法修改Table B时）

若不能修改Table B的结构，可通过自定义函数配合CHECK约束实现校验：

1. 创建校验函数，检查组合是否存在于Table B：

CREATE OR REPLACE FUNCTION is_valid_project_user(p_project_id INT, p_user_id INT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM table_b 
        WHERE project_id = p_project_id AND user_id = p_user_id
    );
END;
$$ LANGUAGE plpgsql STABLE;

2. 给Table A添加CHECK约束：

ALTER TABLE table_a ADD CONSTRAINT check_valid_project_user 
CHECK (is_valid_project_user(default_project_id, user_id));

注意：此方法的局限性在于，当Table B中的关联记录被删除或修改时，Table A中已存在的旧数据不会自动触发校验（若需同步校验需额外添加触发器），而外键约束会自动维护引用完整性。

内容的提问来源于stack exchange，提问作者four-eyes