PostgreSQL：实现单条数据源收藏标记的最优方案

现有表结构 #

CREATE TABLE public.data_source__instrument (
  instrument_id int4 NOT NULL,
  data_source_id int4 NOT NULL,
  CONSTRAINT data_source__instrument__pk PRIMARY KEY (data_source_id, instrument_id)
);

示例数据 #

需求 #

为每个instrument设置唯一的收藏数据源，要求每个instrument必须且只能有一个收藏数据源。

当前方案及痛点 #

当前通过新增字段和唯一约束实现：

CREATE TABLE public.data_source__instrument (
  instrument_id int4 NOT NULL,
  data_source_id int4 NOT NULL,
  fav_data_source boolean NULL, -- 新增字段
  CONSTRAINT data_source__instrument__pk PRIMARY KEY (data_source_id, instrument_id),
  CONSTRAINT fav_data_source UNIQUE (instrument_id,fav_data_source) -- 新增唯一约束
);

用true标记收藏项，非收藏项设为null（利用唯一约束对NULL无效的特性），示例数据如下：

但该方案存在缺陷：

• 允许instrument没有收藏数据源，不符合“必须有一个”的要求
• fav_data_source可设为false，易混淆且无实际作用

希望找到更优方案，尽量避免触发器等复杂特性。

方案一：新增独立收藏映射表（推荐） #

保留原关联表不变，创建单独的表存储每个instrument的唯一收藏数据源，通过主键和外键约束严格实现需求：

-- 原关联表保持不变
CREATE TABLE public.data_source__instrument (
  instrument_id int4 NOT NULL,
  data_source_id int4 NOT NULL,
  CONSTRAINT data_source__instrument__pk PRIMARY KEY (data_source_id, instrument_id)
);

-- 新增收藏映射表
CREATE TABLE public.instrument_fav_data_source (
  instrument_id int4 NOT NULL PRIMARY KEY, -- 主键确保每个instrument唯一对应一条记录
  data_source_id int4 NOT NULL,
  -- 外键约束：确保收藏的instrument存在于关联表中
  CONSTRAINT fk_fav_instrument FOREIGN KEY (instrument_id) 
    REFERENCES public.data_source__instrument(instrument_id),
  -- 复合外键：确保该数据源确实与instrument存在关联
  CONSTRAINT fk_fav_data_source FOREIGN KEY (data_source_id, instrument_id) 
    REFERENCES public.data_source__instrument(data_source_id, instrument_id)
);

核心优势 #

• 完全满足“必须且只能有一个收藏数据源”的要求：主键instrument_id强制唯一性，外键确保收藏的数据源与instrument的关联合法
• 逻辑清晰，符合数据库设计的单一职责原则，避免原表字段冗余
• 纯约束实现，无需触发器或复杂逻辑

方案二：原表内通过部分唯一索引实现（可选） #

如果不想新增表，可利用PostgreSQL的部分唯一索引结合检查约束，确保每个instrument最多一个收藏项；若要强制“必须有一个”，则需配合触发器（若业务层能保证该规则，可省略触发器）：

CREATE TABLE public.data_source__instrument (
  instrument_id int4 NOT NULL,
  data_source_id int4 NOT NULL,
  fav_data_source boolean NOT NULL DEFAULT false, -- 设为NOT NULL，默认非收藏
  CONSTRAINT data_source__instrument__pk PRIMARY KEY (data_source_id, instrument_id),
  -- 检查约束：明确字段只能是true/false（可选，boolean类型本身限制）
  CONSTRAINT chk_fav_value CHECK (fav_data_source IN (true, false)),
  -- 部分唯一索引：仅当fav_data_source为true时，每个instrument只能有一条记录
  CONSTRAINT uq_fav_instrument UNIQUE (instrument_id) WHERE (fav_data_source = true)
);

-- （可选）触发器确保每个instrument至少有一个收藏项
CREATE OR REPLACE FUNCTION enforce_fav_exists() RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM public.data_source__instrument 
    WHERE instrument_id = NEW.instrument_id AND fav_data_source = true
  ) THEN
    RAISE EXCEPTION 'Instrument % must have exactly one favorite data source', NEW.instrument_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_enforce_fav_exists
AFTER INSERT OR UPDATE OR DELETE ON public.data_source__instrument
FOR EACH ROW EXECUTE FUNCTION enforce_fav_exists();

注意点 #

• 若严格要求避免触发器，可仅保留部分唯一索引和检查约束，确保最多一个收藏项，然后在业务逻辑中强制每个instrument必须设置一个收藏项
• 该方案会让原表承担额外的收藏标记职责，逻辑复杂度略高于方案一

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