sql - 如何通过约束强制引用的行也具有给定类型

Question

我有多个表 B、C、D ... 相互引用另一个表 A。

让我们看看 A 和 B:

CREATE TABLE A (
  ID int PRIMARY KEY
, TYPE enum_type
);

CREATE TABLE B (
  A_ID int REFERENCES A(ID)
);

当表 B 引用时，是否可以检查 A 是否始终具有一个特定的 TYPE？

• 表 B 引用的 A 中的每一行都必须具有 TYPE 'X'
• 表 C 引用的 A 中的每一行都必须具有 TYPE 'Y'
• 表 D 引用的 A 中的每一行都必须具有 TYPE 'Z'
• 等等

这是否可能在某种程度上受到限制？还是我必须使用功能？或者最好的方法是什么？

Answer 1

1。 FK约束

如果您想使用 FK 约束强制执行参照完整性，则必须包含 type在表中b冗余地，添加一个(冗余)UNIQUE对 a(id, type) 的约束并将其设为 multicolumn FK constraint在 (a_id, type) REFERENCES a(id, type) .

相关(带代码):

• Enforcing constraints “two tables away”
• Foreign keys referring other foreign keys in PostgreSQL

2。分区

可以使用 table partitioning 构建不太严格的设计.有主表a在分区键上使用列表分区 type .因此你会有一个分区 a_x所有类型为“X”，以及一个分区 a_y所有类型为“Y”。然后对各自的分区创建FK约束，就可以确定类型了。

我建议在 Postgres 11 或更高版本中使用列表分区。相关(带有列表分区的代码示例):

• Is there a way to automatically update a view based on daily created tables?

请注意，您不能对 master 表设置 FK 约束。 The manual :

foreign keys referencing partitioned tables are not supported.

FK方案由于冗余列和冗余索引占用较多磁盘空间。使用分区 OTOH，根据您的 Postgres 版本和选择的分区方法，您必须处理 limitations/caveats不过，该特定设置。

3。触发器

您发布了另一个基于触发器的解决方案，它不占用额外的空间，也不会使关系设计复杂化。但是更容易破解/规避。不像 FK 解决方案那样防弹。无论哪种方式，这是您的想法的改进版本:

CREATE TABLE a (
    id int PRIMARY KEY,   
    type enum_type NOT NULL  -- ! NOT NULL or adapt the check in the trigger
);

CREATE UNIQUE INDEX a_type_id_idx ON a(type, id);  -- ! see below

CREATE TABLE b (
    a_id int REFERENCES a(id)
);

CREATE FUNCTION type_check()  -- ! rewritten
  RETURNS trigger AS
$type_check$
BEGIN
   IF EXISTS (
      SELECT 
      FROM   a
      WHERE  a.id = NEW.a_id
      AND    a.type = TG_ARGV[0]) THEN
      -- do nothing, all good
   ELSE
      RAISE EXCEPTION 'Type does not match table type!';
   END IF;

   RETURN NEW;
END
$type_check$  LANGUAGE plpgsql;

CREATE TRIGGER tg_check_type_b
BEFORE INSERT OR UPDATE ON b
FOR EACH ROW EXECUTE PROCEDURE type_check('X');

• a(type, id)上的索引允许对频繁查找进行仅索引扫描。 this 顺序的索引表达式。原因如下:

  • Is a composite index also good for queries on the first field?

• 您检查了 IF var_type <> var_allowed_type ，但是a.type未定义 NOT NULL .这将允许与 NULL 不匹配.

• 在 plpgsql 中，分配的成本相对较高。我简化了函数以使其快速。 IF EXISTS ...替换变量，带有赋值的附加查询应该快得多 - 并且还会发现与 NULL 的潜在不匹配通过反转逻辑。

  • PostgreSQL IF statement

4。滥用CHECK约束

甚至更快，也更 hacky。制作 NOT VALID在很大程度上是合法的。具有代码示例和原理的相关案例:

• SQL constraint to check whether value doesn't exist in another table
• How to avoid a cyclic dependency (circular reference) between 3 tables?