基于父表实现城镇名国家内唯一约束的替代方案

方案一：添加冗余country_id字段（最可靠的通用方案） #

这就是你想到的方案，完全能保证数据一致性，具体步骤如下：

1. 新增字段并创建唯一约束
   给towns表加个country_id字段，然后创建唯一约束：UNIQUE(name, country_id)，这样直接就能确保同一国家下的城镇名称不会重复。

2. 用触发器自动同步country_id
   因为towns已经有region_id的外键约束，我们可以写触发器，在插入或更新towns的region_id时，自动从regions表把对应的country_id同步过来：

• 插入时：根据新的region_id查询regions的country_id，填充到towns.country_id
• 更新region_id时：同步更新country_id

以PostgreSQL为例，代码示例：

-- 触发器函数：同步country_id
CREATE OR REPLACE FUNCTION sync_town_country_id()
RETURNS TRIGGER AS $$
BEGIN
    SELECT country_id INTO NEW.country_id
    FROM regions
    WHERE id = NEW.region_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 插入触发器
CREATE TRIGGER trigger_town_insert_sync
BEFORE INSERT ON towns
FOR EACH ROW EXECUTE FUNCTION sync_town_country_id();

-- 更新region_id时的触发器
CREATE TRIGGER trigger_town_update_sync
BEFORE UPDATE OF region_id ON towns
FOR EACH ROW EXECUTE FUNCTION sync_town_country_id();

3. 禁止手动修改country_id
   再加个触发器，阻止用户直接修改country_id，只允许触发器自动维护：

-- 触发器函数：阻止手动修改country_id
CREATE OR REPLACE FUNCTION prevent_town_country_edit()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.country_id IS NOT NULL AND NEW.country_id != OLD.country_id THEN
        RAISE EXCEPTION '不能手动修改towns.country_id，该字段由系统自动同步';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_town_prevent_country_mod
BEFORE UPDATE OF country_id ON towns
FOR EACH ROW EXECUTE FUNCTION prevent_town_country_edit();

这样用户直接修改country_id会报错，只有修改region_id时由触发器自动更新，或者插入时自动填充，完全能保证字段和region对应的country_id一致。

方案二：函数索引（适合部分数据库） #

如果不想加冗余字段，可以试试函数索引，直接基于关联表的country_id做唯一约束。比如PostgreSQL支持这种写法：

CREATE UNIQUE INDEX idx_town_name_country_unique ON towns (
    name,
    (SELECT country_id FROM regions WHERE id = towns.region_id)
);

但要注意：这种方式不是所有数据库都支持（比如MySQL就不行），而且当regions的country_id更新时，索引的同步效率可能不如冗余字段，查询性能也会受点影响。

方案三：视图+约束（局限性大） #

可以建一个包含城镇名和对应国家ID的视图，然后在视图上添加唯一约束，但这种要求视图是可更新的，不同数据库支持程度不一样，实际用起来不如前两种方案顺手。

内容的提问来源于stack exchange，提问作者Adriano di Lauro