sql - 将 Postgres 中的外键定义为目标表的子集

Question

例子:
我有:

Table A:  
 int id  
 int table_b_id

Table B:  
 int id  
 text type  

我想在 table_b_id 列上添加约束检查，以验证它仅指向表 B 中类型值为“X”的行。
我无法更改表结构。
我知道这可以通过“CHECK”和一个将执行特定查询的 postgres 函数来完成，但我看到有人建议避免使用它。
任何有关实现它的最佳方法的意见都会有所帮助。

Answer 1

您指的不是 FOREIGN KEY，在 PostgreSQL 中，它指的是另一个表中的(多个)列，其中有一个唯一索引/这些列，并且当那个/那些列的值更改时(ON UPDATE、ON DELETE)，它们可能具有关联的自动操作。

您正在尝试实现一种特定类型的参照完整性，类似于 FOREIGN KEY 所做的。你可以用一个 CHECK 子句和一个函数来做到这一点(因为 CHECK 子句不允许子查询)，你也可以用 table inheritance and range partitioning 来做到这一点(指的是仅包含 type = 'X' 的行的子表)，但使用触发器执行此操作可能是最简单的:

CREATE FUNCTION trf_test_type_x() RETURNS trigger AS $$
BEGIN
  PERFORM * FROM tableB WHERE id = NEW.table_b_id AND type = 'X';
  IF NOT FOUND THEN
    -- RAISE NOTICE 'Foreign key violation...';
    RETURN NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE tr_test_type_x
BEFORE INSERT OR UPDATE ON tableA
FOR EACH ROW EXECUTE PROCEDURE trf_test_type_x();

您可以在 tableB 上创建部分索引以加快速度:

CREATE UNIQUE INDEX idx_type_X ON tableB(id) WHERE type = 'X';