如何创建指向非唯一表的外键？解决物化视图ON COMMIT刷新限制

我之前也碰到过类似的棘手场景——父表有重复数据没法直接建外键，还得保证引用关系的实时同步，带distinct的物化视图又没法用ON COMMIT刷新。给你几个实用的解决方案，你可以根据业务场景挑：

外键的核心逻辑其实就是两点：子表的引用值必须在父表存在，父表被引用的记录不能被随意删除/修改。我们可以用自定义函数+CHECK约束+触发器来实现这个逻辑：

首先写一个检查父表是否存在对应值的函数：

CREATE OR REPLACE FUNCTION check_parent_record_exists(p_ref_value INT)
RETURNS BOOLEAN AS $$
BEGIN
  -- 只要父表存在该值就返回true，不管有没有重复
  RETURN EXISTS (SELECT 1 FROM parent_table WHERE parent_column = p_ref_value);
END;
$$ LANGUAGE plpgsql STABLE;

然后给子表添加CHECK约束，调用这个函数：

ALTER TABLE child_table ADD CONSTRAINT fk_child_parent_check 
CHECK (check_parent_record_exists(child_reference_column));

接下来要处理父表的删除/修改操作，防止子表出现无效引用，写一个触发器函数：

CREATE OR REPLACE FUNCTION prevent_invalid_parent_modification()
RETURNS TRIGGER AS $$
BEGIN
  -- 如果父表要删除/修改的记录还被子表引用，就抛出错误
  IF EXISTS (SELECT 1 FROM child_table WHERE child_reference_column = OLD.parent_column) THEN
    RAISE EXCEPTION '无法删除/修改父表记录：该记录仍被子表引用';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

给父表绑定这个触发器：

-- 绑定删除前触发器
CREATE TRIGGER trg_parent_before_delete
BEFORE DELETE ON parent_table
FOR EACH ROW EXECUTE FUNCTION prevent_invalid_parent_modification();

-- 绑定修改前触发器（如果需要限制修改父表的引用字段）
CREATE TRIGGER trg_parent_before_update
BEFORE UPDATE OF parent_column ON parent_table
FOR EACH ROW EXECUTE FUNCTION prevent_invalid_parent_modification();

优缺点：

• 优点：完全实时同步，不需要任何刷新操作，逻辑和原生外键一致
• 缺点：性能会受影响，尤其是父表/子表数据量大的时候，每次写操作都会触发查询检查

既然带distinct的物化视图没法用ON COMMIT刷新，我们可以改用**ON DEMAND刷新**，配合数据库的定时任务高频执行刷新，实现接近实时的同步：

首先创建带distinct的物化视图，并给它加唯一索引（方便建外键）：

CREATE MATERIALIZED VIEW parent_unique_mv AS
SELECT DISTINCT parent_column FROM parent_table;

CREATE UNIQUE INDEX idx_mv_parent_unique ON parent_unique_mv(parent_column);

然后给子表建外键指向这个物化视图：

ALTER TABLE child_table ADD CONSTRAINT fk_child_parent_mv 
FOREIGN KEY (child_reference_column) REFERENCES parent_unique_mv(parent_column);

最后设置定时任务高频刷新物化视图，比如用PostgreSQL的pg_cron每分钟刷新一次：

-- 每分钟执行一次刷新
SELECT cron.schedule('refresh-parent-mv', '*/1 * * * *', 'REFRESH MATERIALIZED VIEW parent_unique_mv;');

如果是Oracle的话，可以用DBMS_SCHEDULER创建类似的定时任务。

优缺点：

• 优点：实现简单，不需要复杂的触发器逻辑，性能比方案1好
• 缺点：存在短时间的同步延迟（比如1分钟），如果业务对实时性要求极高可能不适用

如果业务允许的话，最好从根源解决问题——整理父表的重复数据，创建一个真正的唯一键表，让原父表和子表都关联这个新表：

1. 先创建一个存储唯一值的父表：

CREATE TABLE parent_unique (
  id SERIAL PRIMARY KEY,
  ref_value INT UNIQUE NOT NULL
);

2. 把原父表的唯一值导入新表：

INSERT INTO parent_unique(ref_value)
SELECT DISTINCT parent_column FROM parent_table;

3. 给原父表添加外键关联新表：

ALTER TABLE parent_table ADD COLUMN unique_parent_id INT;
UPDATE parent_table SET unique_parent_id = (SELECT id FROM parent_unique WHERE ref_value = parent_column);
ALTER TABLE parent_table ADD CONSTRAINT fk_parent_unique FOREIGN KEY (unique_parent_id) REFERENCES parent_unique(id);

4. 修改子表的外键指向新表：

ALTER TABLE child_table ADD COLUMN unique_parent_id INT;
UPDATE child_table SET unique_parent_id = (SELECT id FROM parent_unique WHERE ref_value = child_reference_column);
-- 删除原来的无效外键（如果有的话）
ALTER TABLE child_table DROP CONSTRAINT IF EXISTS old_invalid_fk;
-- 添加新的合法外键
ALTER TABLE child_table ADD CONSTRAINT fk_child_parent_unique FOREIGN KEY (unique_parent_id) REFERENCES parent_unique(id);

优缺点：

• 优点：完全符合数据库设计规范，性能最佳，没有任何同步问题
• 缺点：需要修改现有业务代码，还要做数据迁移，实施成本较高

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