如何在PostgreSQL中无需应用代码强制用户车辆子集关联约束？

现有表结构的约束验证 #

你的现有表结构已经满足大部分业务需求：

• 用户必须隶属于恰好一家公司：users表的company_id为NOT NULL且关联companies表外键，确保每个用户只能归属一家公司且不能为空。
• 一家公司可拥有多辆车辆：vehicle_companies多对多表支持一个公司关联多辆车辆。
• 一辆车辆可关联多家公司：同样通过vehicle_companies的多对多结构实现。

核心待解决的是用户关联的车辆必须是其所属公司有权访问的车辆，以下是两种可靠的实现方式：

方案一：使用复合外键（推荐，纯约束实现） #

这种方式通过数据库原生约束保证数据一致性，性能更高且更可靠。

步骤1：给vehicle_companies添加唯一约束 #

确保同一车辆与公司的组合不会重复：

ALTER TABLE vehicle_companies ADD CONSTRAINT uc_vehicle_company UNIQUE (vehicle_id, company_id);

步骤2：重构user_vehicles表 #

调整表结构，通过复合外键同时关联用户所属公司和车辆-公司关联记录：

DROP TABLE IF EXISTS user_vehicles;
CREATE TABLE user_vehicles (
    user_id INTEGER NOT NULL,
    vehicle_id INTEGER NOT NULL,
    company_id INTEGER NOT NULL,
    -- 确保一个用户不会重复关联同一车辆
    PRIMARY KEY (user_id, vehicle_id),
    -- 约束company_id必须对应用户所属的公司
    FOREIGN KEY (user_id, company_id) REFERENCES users(id, company_id),
    -- 约束该车辆与公司的关联已存在于vehicle_companies中
    FOREIGN KEY (vehicle_id, company_id) REFERENCES vehicle_companies(vehicle_id, company_id)
);

约束生效逻辑 #

当插入/更新user_vehicles记录时：

1. 第一个复合外键确保company_id是当前user_id对应的公司；
2. 第二个复合外键确保vehicle_id与company_id的组合已在vehicle_companies中存在；
3. 两者结合天然保证用户只能关联其所属公司有权访问的车辆。

方案二：使用触发器（适用于无法修改表结构的场景） #

如果无法调整user_vehicles的现有结构，可通过触发器实现校验：

创建触发器函数 #

CREATE OR REPLACE FUNCTION check_user_vehicle_company_match()
RETURNS TRIGGER AS $$
BEGIN
    -- 验证用户的公司与车辆关联的公司一致
    IF NOT EXISTS (
        SELECT 1
        FROM users u
        INNER JOIN vehicle_companies vc 
            ON u.company_id = vc.company_id
        WHERE u.id = NEW.user_id 
          AND vc.id = NEW.vehicle_company_id
    ) THEN
        RAISE EXCEPTION '用户无法关联其所属公司无权访问的车辆';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器 #

CREATE TRIGGER trigger_check_user_vehicle_company
BEFORE INSERT OR UPDATE ON user_vehicles
FOR EACH ROW EXECUTE FUNCTION check_user_vehicle_company_match();

说明 #

触发器会在插入或更新user_vehicles记录前执行校验，若不符合规则则抛出异常阻止操作。但相比复合外键，触发器的性能稍低，且约束逻辑不如外键直观。

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