PostgreSQL外键创建报错及WPF报价单状态颜色自动联动实现问题

问题原因 #

PostgreSQL要求外键引用的列集合，必须在被引用表上存在显式的唯一约束/主键约束。你当前的quotation_status_list主键是(id, status, status_color)三列组合，虽然status和status_color各自单独有唯一约束，逻辑上两列组合也肯定唯一，但你没有给(status, status_color)这个组合单独声明唯一约束，因此外键引用这两个列的时候找不到匹配的约束，就触发了对应报错。

修复建表错误方案 #

先优化quotation_status_list的结构，移除冗余的多列主键，同时显式添加外键需要的组合唯一约束：

CREATE TABLE IF NOT EXISTS quotation_status_list
( 
    id SERIAL PRIMARY KEY, 
    status TEXT UNIQUE NOT NULL, 
    status_color TEXT UNIQUE NOT NULL,
    -- 显式给外键引用的列组合加唯一约束
    UNIQUE(status, status_color)
);

修改后你原来的quotation_list建表语句就可以正常创建，同时你配置的ON UPDATE CASCADE会直接生效，自动实现第一个需求：修改quotation_status_list的status_color时，quotation_list中所有对应状态的status_color会同步更新。

实现第二个需求（修改quotation_list的status时自动更新status_color） #

外键仅能处理父表变更的级联同步，子表字段变更触发的关联更新需要用触发器实现：

1. 创建触发器函数

CREATE OR REPLACE FUNCTION update_quotation_status_color()
RETURNS TRIGGER AS $$
BEGIN
    SELECT status_color INTO NEW.status_color
    FROM quotation_status_list
    WHERE status = NEW.status;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给quotation_list绑定触发器，插入新行或修改status字段时自动触发

CREATE TRIGGER trigger_update_status_color
BEFORE INSERT OR UPDATE OF status ON quotation_list
FOR EACH ROW
EXECUTE FUNCTION update_quotation_status_color();

额外优化建议 #

更规范的表设计可以直接去掉quotation_list里的status_color冗余字段，查询数据时通过JOIN关联quotation_status_list获取对应颜色值，直接把关联后的结果集绑定到WPF的DataGrid即可，不需要额外维护冗余字段，也从根源上避免了数据不一致的问题，只有在JOIN性能不符合要求时再考虑上面的触发器方案。

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