PostgreSQL12中数组元素关联其他表建表报错如何解决？

PostgreSQL 原生不支持 ELEMENT REFERENCES 这类数组元素外键约束语法，因此你编写的建表语句会执行报错。常见的实现方案有两种： #

方案一：使用关联表实现（最推荐） #

这是关系型数据库标准的多对多关系实现方式，完全符合数据库范式，所有约束逻辑由 PostgreSQL 原生支持，稳定性和性能最优。

1. 先创建destinations表，去掉数组外键定义：

CREATE TABLE destinations(
    code varchar(80) PRIMARY KEY,
    name varchar(80),
    updated_at varchar(80),
    latitude varchar(80),
    longitude varchar(80),
    country varchar(80) references countries(code),
    parent varchar(80) references destinations(code)
);

2. 新增关联表存储目的地和区域的映射关系：

CREATE TABLE destinations_regions (
    destination_code varchar(80) REFERENCES destinations(code) ON DELETE CASCADE,
    region_id int REFERENCES regions(id) ON DELETE CASCADE,
    PRIMARY KEY (destination_code, region_id)
);

• 优势：原生支持外键约束、级联删除/更新，查询优化器可更好的优化关联查询，不存在数据一致性风险。

方案二：使用触发器手动实现约束（适合必须保留数组字段的场景） #

如果业务要求必须在destinations表保留regions数组字段，可以通过触发器手动实现外键校验逻辑：

1. 建表时将regions定义为普通int数组：

CREATE TABLE destinations(
    code varchar(80) PRIMARY KEY,
    name varchar(80),
    updated_at varchar(80),
    latitude varchar(80),
    longitude varchar(80),
    country varchar(80) references countries(code),
    parent varchar(80) references destinations(code),
    regions int[]
);

2. 编写校验函数并绑定触发器：

-- 校验regions数组中所有id都存在于regions表
CREATE OR REPLACE FUNCTION check_regions_exist()
RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM unnest(NEW.regions) r(id)
        LEFT JOIN regions ON regions.id = r.id
        WHERE regions.id IS NULL
    ) THEN
        RAISE EXCEPTION 'regions数组中包含不存在的区域ID';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定插入、更新操作的校验触发器
CREATE TRIGGER trigger_destinations_regions_check
BEFORE INSERT OR UPDATE OF regions ON destinations
FOR EACH ROW EXECUTE FUNCTION check_regions_exist();

3. 补充反向校验：如果需要限制regions表删除已被引用的记录，还需要在regions表上新增触发器，检查删除的ID是否存在于任意destinations.regions数组中。

• 劣势：需要手动维护全量约束逻辑，容易出现边界遗漏，性能低于原生外键，数组关联查询复杂度更高。

不推荐使用非官方的第三方扩展实现数组外键，兼容性和稳定性都没有官方保障，不适合生产环境使用。

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