SQL跨多表唯一约束：类表继承中owner与animal_type限制实现

问题背景 #

我采用类表继承的数据库结构，具体情况如下：

• 父表Animals存储所有动物的通用字段：animal_id（主键）、animal_name、animal_type（字符串类型，因数据来自外部，动物类型无法提前全量预设）、number_of_legs等。
• 子表如Mammals、Reptiles，子表主键（比如mammal_id）关联Animals.animal_id，子表存储对应类型的专属字段——例如Mammals包含owner字段，记录动物的主人。
• 新增未预设的动物类型时，直接在Animals的animal_type字段记录；后续若需为该类型存储特殊数据，再创建对应子表。子表内部还可通过Animals.animal_type细分，比如Mammals下可区分Dogs、Cats。

当前需求：同一owner不能拥有同一种animal_type的多只动物（例如不能养两只猫，但可同时养猫和狗）。但animal_type仅存在于父表Animals，若在子表重复存储会导致数据冗余且难以保证同步，常规子表唯一约束无法直接实现该需求。

可行解决方案 #

1. 物化视图+唯一索引（适合PostgreSQL等支持物化视图的数据库） #

创建物化视图关联子表与父表的关键字段，再给视图添加唯一约束：

-- 创建物化视图，关联Mammals和Animals，提取需要的约束字段
CREATE MATERIALIZED VIEW mammal_owner_type AS
SELECT m.owner, a.animal_type
FROM Mammals m
JOIN Animals a ON m.mammal_id = a.animal_id;

-- 在物化视图上创建唯一索引，确保同一owner+animal_type组合唯一
CREATE UNIQUE INDEX idx_mammal_owner_type_unique ON mammal_owner_type (owner, animal_type);

注意：物化视图不会自动同步数据，需定期手动刷新或配置自动刷新，适合对实时性要求不极端的场景。

2. 触发器实时检查（通用多数数据库，如MySQL、PostgreSQL） #

通过触发器在插入/更新Mammals数据时，实时检查关联的animal_type是否已被该owner拥有：

-- MySQL插入触发器示例
DELIMITER //
CREATE TRIGGER check_mammal_owner_before_insert BEFORE INSERT ON Mammals
FOR EACH ROW
BEGIN
    DECLARE same_type_count INT;
    -- 统计当前owner已拥有该animal_type的动物数量
    SELECT COUNT(*) INTO same_type_count
    FROM Animals a
    JOIN Mammals m ON a.animal_id = m.mammal_id
    WHERE m.owner = NEW.owner 
      AND a.animal_type = (SELECT animal_type FROM Animals WHERE animal_id = NEW.mammal_id);
    
    IF same_type_count > 0 THEN
        -- 抛出错误阻止插入
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '同一主人不能养同类型的多只哺乳动物';
    END IF;
END //
DELIMITER ;

-- MySQL更新触发器示例（防止修改owner或关联动物类型导致重复）
DELIMITER //
CREATE TRIGGER check_mammal_owner_before_update BEFORE UPDATE ON Mammals
FOR EACH ROW
BEGIN
    DECLARE same_type_count INT;
    SELECT COUNT(*) INTO same_type_count
    FROM Animals a
    JOIN Mammals m ON a.animal_id = m.mammal_id
    WHERE m.owner = NEW.owner 
      AND a.animal_type = (SELECT animal_type FROM Animals WHERE animal_id = NEW.mammal_id)
      AND m.mammal_id != NEW.mammal_id; -- 排除当前更新的记录
    
    IF same_type_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '同一主人不能养同类型的多只哺乳动物';
    END IF;
END //
DELIMITER ;

这种方式能保证数据一致性，但会增加插入/更新的性能开销，需根据业务量权衡。

3. 函数索引（适合PostgreSQL等支持函数索引的数据库） #

先创建函数通过子表主键获取对应animal_type，再在子表上创建结合owner与函数返回值的唯一索引：

-- 创建函数，根据mammal_id从Animals中获取对应的animal_type
CREATE OR REPLACE FUNCTION get_animal_type(mammal_id INT) RETURNS VARCHAR AS $$
SELECT animal_type FROM Animals WHERE animal_id = mammal_id;
$$ LANGUAGE sql STABLE;

-- 在Mammals表上创建唯一索引，约束owner+对应animal_type的唯一性
CREATE UNIQUE INDEX idx_mammal_owner_type ON Mammals (owner, get_animal_type(mammal_id));

该方案无需额外视图，实时性好，但依赖数据库对函数索引的支持，且需保证函数为稳定型（同一输入返回固定结果）。

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