数据库设计：关联非唯一rl_id触发外键约束报错的最优解

问题背景 #

现有数据库中，assets表存储多ID资产：同一rl_id对应多个不同语言版本的资产（如原版电影、配音版），这些资产共享演员等核心数据。但创建关联演员与资产的people_assets_map表时，因assets.rl_id无唯一约束，无法建立外键引用，触发报错：

there is no unique constraint matching given keys for referenced table "assets"

由于assets表结构已在其他业务场景中使用，无法直接修改，需寻找无需改动原表的最佳实践。

现有表结构 #

assets表

CREATE TABLE IF NOT EXISTS assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  rl_id INT NOT NULL,
  unique_id VARCHAR(9) NOT NULL UNIQUE,
  UNIQUE(rl_id, unique_id)
);
INSERT INTO assets (rl_id, unique_id) VALUES (3894, 3894); -- 西班牙语原版电影
INSERT INTO assets (rl_id, unique_id) VALUES (3894, '3894dubEN'); -- 该电影的英语配音版
INSERT INTO assets (rl_id, unique_id) VALUES (3894, '3894dubFR'); -- 该电影的法语配音版

关联表结构

CREATE TABLE IF NOT EXISTS people (
  id SERIAL PRIMARY KEY,
  speaking_id VARCHAR(255) NOT NULL UNIQUE,
  name TEXT NOT NULL,
);

-- 创建失败的关联表（外键引用rl_id报错）
CREATE TABLE IF NOT EXISTS people_assets_map (
  id SERIAL PRIMARY KEY,
  person_id INT NOT NULL REFERENCES people(id),
  rl_id INT NOT NULL REFERENCES assets(rl_id),
);

可行解决方案 #

1. 独立rl_id维度表+自动同步触发器（推荐） #

这是保证数据完整性的最优方案，通过独立表存储唯一rl_id，并利用触发器自动同步：

• 创建存储唯一rl_id的维度表：

CREATE TABLE IF NOT EXISTS asset_rl_ids (
  rl_id INT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 初始化现有数据
INSERT INTO asset_rl_ids (rl_id) SELECT DISTINCT rl_id FROM assets ON CONFLICT DO NOTHING;

• 创建触发器函数，同步assets表的rl_id变更：

CREATE OR REPLACE FUNCTION sync_asset_rl_id()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO asset_rl_ids (rl_id) VALUES (NEW.rl_id) ON CONFLICT DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到assets表的INSERT/UPDATE事件
CREATE TRIGGER trigger_sync_rl_id
AFTER INSERT OR UPDATE OF rl_id ON assets
FOR EACH ROW EXECUTE FUNCTION sync_asset_rl_id();

• 修改关联表的外键引用至维度表：

CREATE TABLE IF NOT EXISTS people_assets_map (
  id SERIAL PRIMARY KEY,
  person_id INT NOT NULL REFERENCES people(id),
  rl_id INT NOT NULL REFERENCES asset_rl_ids(rl_id)
);

该方案逻辑清晰，对现有业务无侵入，能完全保证外键引用的完整性。

2. 物化视图替代维度表 #

如果rl_id新增频率极低，可以用物化视图存储唯一rl_id，避免维护触发器：

CREATE MATERIALIZED VIEW asset_rl_ids_mv AS
SELECT DISTINCT rl_id FROM assets
WITH DATA;

-- 给物化视图添加主键约束
ALTER MATERIALIZED VIEW asset_rl_ids_mv ADD PRIMARY KEY (rl_id);

• 关联表外键引用物化视图：

CREATE TABLE IF NOT EXISTS people_assets_map (
  id SERIAL PRIMARY KEY,
  person_id INT NOT NULL REFERENCES people(id),
  rl_id INT NOT NULL REFERENCES asset_rl_ids_mv(rl_id)
);

注意：物化视图不会自动同步assets的新数据，需要定期执行REFRESH MATERIALIZED VIEW asset_rl_ids_mv;刷新数据。

3. 自定义检查约束（临时方案，不推荐） #

若仅需临时绕过外键约束，可使用自定义函数检查rl_id是否存在，但无法保证数据一致性：

CREATE OR REPLACE FUNCTION rl_id_exists(p_rl_id INT)
RETURNS BOOLEAN AS $$
SELECT EXISTS(SELECT 1 FROM assets WHERE rl_id = p_rl_id);
$$ LANGUAGE sql STABLE;

CREATE TABLE IF NOT EXISTS people_assets_map (
  id SERIAL PRIMARY KEY,
  person_id INT NOT NULL REFERENCES people(id),
  rl_id INT NOT NULL CHECK (rl_id_exists(rl_id))
);

缺点：当assets中删除某个rl_id时，people_assets_map的对应数据不会被约束，易产生脏数据，仅适合临时场景。

最佳实践总结 #

优先选择方案1：独立维度表+触发器同步，既能保证数据完整性，又对现有业务无侵入，长期维护成本低。若rl_id几乎不会新增，方案2的物化视图是轻量化替代方案。

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