基于STI方法映射泛化约束到SQL的方案可行性咨询

你的选型思路完全靠谱：针对无特殊属性的子类用Single Table Inheritance（STI），用查找表实现business type而非枚举/硬编码，兼顾了可移植性和扩展性，这个方向没问题。下面针对你关心的两个核心约束，给出具体的实现方案：

一、Business Type 查找表的合理性 #

用查找表管理business type是比枚举、硬编码更优的选择：

• 枚举类型跨数据库兼容性差，修改枚举值需要DDL操作，业务侵入性强；查找表仅需DML操作就能新增/调整类型，无需改动表结构
• 硬编码校验分散在业务代码中，易出现数据不一致；查找表通过外键约束就能保证type值的合法性，无需业务层额外做校验

二、触发器实现核心约束 #

你需要实现两个关键约束：禁止修改已插入的business type、基于type限制仅关联一个子关系，以下是具体的触发器写法（以PostgreSQL为例，其他数据库逻辑一致，语法稍作调整）：

1. 禁止修改business type #

首先确保父表的type字段只能在插入时设置，后续无法修改：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION prevent_type_update()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.type <> NEW.type THEN
    RAISE EXCEPTION 'Business type cannot be modified after insertion';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定到父表的UPDATE事件
CREATE TRIGGER trigger_prevent_type_update
BEFORE UPDATE ON parent_table
FOR EACH ROW EXECUTE FUNCTION prevent_type_update();

如果要禁止删除查找表中已被引用的type值，只需给父表的type字段加外键约束并设置ON DELETE RESTRICT：

ALTER TABLE parent_table
ADD CONSTRAINT fk_parent_type FOREIGN KEY (type)
REFERENCES business_type(id) ON DELETE RESTRICT;

2. 基于type的排他关联约束 #

假设父表有rel_a_id、rel_b_id、rel_c_id、rel_d_id四个关联字段，对应四个子关系，需要确保每个记录仅根据type值关联对应子表：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION enforce_single_relation()
RETURNS TRIGGER AS $$
DECLARE
  non_null_count INT;
BEGIN
  -- 根据type值校验关联字段的合法性
  CASE NEW.type
    WHEN 'TYPE_A' THEN
      non_null_count := (NEW.rel_a_id IS NOT NULL)::INT + 
                        (NEW.rel_b_id IS NOT NULL)::INT + 
                        (NEW.rel_c_id IS NOT NULL)::INT + 
                        (NEW.rel_d_id IS NOT NULL)::INT;
      IF non_null_count <> 1 OR NEW.rel_a_id IS NULL THEN
        RAISE EXCEPTION 'TYPE_A records must have exactly one non-NULL value in rel_a_id';
      END IF;
    WHEN 'TYPE_B' THEN
      non_null_count := (NEW.rel_a_id IS NOT NULL)::INT + 
                        (NEW.rel_b_id IS NOT NULL)::INT + 
                        (NEW.rel_c_id IS NOT NULL)::INT + 
                        (NEW.rel_d_id IS NOT NULL)::INT;
      IF non_null_count <> 1 OR NEW.rel_b_id IS NULL THEN
        RAISE EXCEPTION 'TYPE_B records must have exactly one non-NULL value in rel_b_id';
      END IF;
    WHEN 'TYPE_C' THEN
      non_null_count := (NEW.rel_a_id IS NOT NULL)::INT + 
                        (NEW.rel_b_id IS NOT NULL)::INT + 
                        (NEW.rel_c_id IS NOT NULL)::INT + 
                        (NEW.rel_d_id IS NOT NULL)::INT;
      IF non_null_count <> 1 OR NEW.rel_c_id IS NULL THEN
        RAISE EXCEPTION 'TYPE_C records must have exactly one non-NULL value in rel_c_id';
      END IF;
    WHEN 'TYPE_D' THEN
      non_null_count := (NEW.rel_a_id IS NOT NULL)::INT + 
                        (NEW.rel_b_id IS NOT NULL)::INT + 
                        (NEW.rel_c_id IS NOT NULL)::INT + 
                        (NEW.rel_d_id IS NOT NULL)::INT;
      IF non_null_count <> 1 OR NEW.rel_d_id IS NULL THEN
        RAISE EXCEPTION 'TYPE_D records must have exactly one non-NULL value in rel_d_id';
      END IF;
    ELSE
      RAISE EXCEPTION 'Invalid business type: %', NEW.type;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定到父表的INSERT和UPDATE事件
CREATE TRIGGER trigger_enforce_single_relation
BEFORE INSERT OR UPDATE ON parent_table
FOR EACH ROW EXECUTE FUNCTION enforce_single_relation();

补充：可选替代方案 #

如果觉得触发器逻辑繁琐，也可以尝试用CHECK约束实现部分校验，但CHECK约束无法动态关联查找表的type值，灵活性不足；且部分数据库（如MySQL）对CHECK约束的支持有限，因此触发器仍是更通用的方案。

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