PostgreSQL是否支持数组型外键？求可行实现方案

1. PostgreSQL原生是否支持数组型外键？ #

不支持。PostgreSQL的外键约束要求关联两端的类型严格匹配，数组类型（如uuid[]）和单个uuid类型属于不兼容类型，所以直接创建数组外键会触发你遇到的类型不匹配错误。原生外键机制无法遍历数组元素逐一校验其是否存在于被引用表中。

2. 能否通过PL/pgSQL实现类似约束？ #

可以，通过触发器+自定义函数模拟数组外键的校验逻辑，具体步骤如下：

• 编写自定义函数，遍历数组中的每个uuid，检查是否均存在于hourly表的id列；若有不存在的元素，直接抛出异常。
• 在主表上创建BEFORE INSERT和BEFORE UPDATE触发器，调用该函数完成校验。

示例代码：

-- 自定义数组校验函数
CREATE OR REPLACE FUNCTION check_hourly_array()
RETURNS TRIGGER AS $$
DECLARE
    elem uuid;
BEGIN
    FOREACH elem IN ARRAY NEW.hourly LOOP
        IF NOT EXISTS (SELECT 1 FROM hourly WHERE id = elem) THEN
            RAISE EXCEPTION 'UUID % 不存在于hourly表中', elem;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器绑定校验逻辑
CREATE TRIGGER trigger_check_hourly_array
BEFORE INSERT OR UPDATE ON main_table
FOR EACH ROW EXECUTE FUNCTION check_hourly_array();

注意：这种方式属于行级校验，性能需结合数组大小和表数据量考量；另外，触发器无法自动处理被引用表（hourly）删除数据后的级联校验，需额外添加反向触发器或配合ON DELETE RESTRICT类约束使用。

3. 其他数据库系统是否支持数组型外键？ #

目前主流关系型数据库基本都不支持数组型外键：

• MySQL：不支持数组类型，无数组外键相关特性；
• SQL Server：支持表值参数和数组，但不允许数组列作为外键；
• Oracle：支持VARRAY类型，但外键约束无法直接关联VARRAY元素，同样需通过触发器或自定义逻辑实现；
• CockroachDB等新型数据库：遵循关系型数据库外键匹配规则，不支持数组到单值的外键关联。

替代方案建议 #

更符合关系型数据库设计范式的做法是，将原24个列改为关联表：

• 创建中间表（如main_hourly_link），包含main_id（关联主表主键）、hour_id（关联hourly表id）、hour_index（0-23标识对应小时）三个字段；
• 该方案既避免了数组的校验问题，又具备更高灵活性（后续调整小时数无需修改表结构），还能通过原生外键约束保证数据一致性。

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