PostgreSQL：如何校验integer[]数组元素引用另一表的id字段

PostgreSQL本身不直接支持数组元素的外键约束，不过可以通过自定义函数+检查约束或者触发器两种方式实现校验需求，另外也建议考虑更符合关系型数据库范式的替代方案：

方法1：检查约束+自定义函数（简单直接） #

先创建一个函数，用来验证数组中的每个元素都存在于ingredients表的id字段中：

CREATE OR REPLACE FUNCTION check_ingredient_ids(ids integer[])
RETURNS boolean AS $$
BEGIN
  -- 允许空数组的情况，若不允许可删除此判断并返回FALSE
  IF ids IS NULL OR array_length(ids, 1) IS NULL THEN
    RETURN TRUE;
  END IF;
  -- 检查数组中所有元素都存在于食材表
  RETURN NOT EXISTS (
    SELECT 1 FROM unnest(ids) AS elem
    WHERE elem NOT IN (SELECT id FROM ingredients)
  );
END;
$$ LANGUAGE plpgsql STABLE;

接着创建recipies表时，添加调用该函数的检查约束：

CREATE TABLE recipies (
  id SERIAL PRIMARY KEY,
  ingredient_ids integer[],
  CONSTRAINT fk_ingredient_array CHECK (check_ingredient_ids(ingredient_ids))
);

注意：这种方法仅在插入/更新recipies数据时校验，若ingredients表的id被删除或修改，recipies中的数组不会自动同步，需要额外处理。

方法2：触发器（支持级联操作，更严谨） #

如果需要联动处理ingredients表的删除/更新操作，触发器是更合适的选择：

第一步：创建插入/更新时的校验触发器 #

CREATE OR REPLACE FUNCTION validate_ingredient_array()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.ingredient_ids IS NOT NULL THEN
    -- 检查是否有不存在的食材ID
    PERFORM 1 FROM unnest(NEW.ingredient_ids) AS elem
    WHERE elem NOT IN (SELECT id FROM ingredients);
    IF FOUND THEN
      RAISE EXCEPTION '数组中包含不存在的食材ID';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_validate_recipies_ingredients
BEFORE INSERT OR UPDATE OF ingredient_ids ON recipies
FOR EACH ROW EXECUTE FUNCTION validate_ingredient_array();

第二步：可选 - 处理食材ID被删除的情况 #

如果要阻止删除被引用的食材ID：

CREATE OR REPLACE FUNCTION prevent_ingredient_deletion()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM recipies
    WHERE OLD.id = ANY(ingredient_ids)
  ) THEN
    RAISE EXCEPTION '该食材ID被食谱引用，无法删除';
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_prevent_ingredient_delete
BEFORE DELETE ON ingredients
FOR EACH ROW EXECUTE FUNCTION prevent_ingredient_deletion();

或者自动从食谱数组中移除被删除的食材ID：

CREATE OR REPLACE FUNCTION remove_deleted_ingredient_from_recipies()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE recipies
  SET ingredient_ids = array_remove(ingredient_ids, OLD.id)
  WHERE OLD.id = ANY(ingredient_ids);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_cleanup_recipies_on_ingredient_delete
AFTER DELETE ON ingredients
FOR EACH ROW EXECUTE FUNCTION remove_deleted_ingredient_from_recipies();

更推荐的替代方案：使用关联表（符合数据库范式） #

从长期维护和查询效率考虑，不建议用数组存储多对多关系，更推荐创建中间关联表：

CREATE TABLE recipies (
  id SERIAL PRIMARY KEY
);

CREATE TABLE recipies_ingredients (
  recipe_id INTEGER REFERENCES recipies(id) ON DELETE CASCADE,
  ingredient_id INTEGER REFERENCES ingredients(id) ON DELETE CASCADE,
  PRIMARY KEY (recipe_id, ingredient_id)
);

这种设计天然支持外键约束，查询、更新、维护都更灵活，也避免了数组带来的查询复杂度问题。

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