如何在PostgreSQL中实现循环约束？ [英] How do I implement circular constraints in PostgreSQL?

问题描述

我想强制一个表中的一行在另一个表中必须有匹配的行，反之亦然。我目前正在这样做，以解决您不能引用尚未创建的表的事实。有没有更自然的方式，我不知道？

  CREATE TABLE LE（id int PRIMARY KEY）; 
 CREATE TABLE LE_TYP（id int PRIMARY KEY，typ text）; 
 
 ALTER TABLE LE ADD CONSTRAINT 
 twowayref FOREIGN KEY（id）REFERENCES LE_TYP（id）DEFERRABLE INITIALLY DEFERRED; 
 
 ALTER TABLE LE_TYP ADD CONSTRAINT 
 twowayref_rev FOREIGN KEY（id）REFERENCES LE（id）DEFERRABLE INITIALLY DEFERRED; 
  

解决方案

你所做的是理想的循环约束。在您的解决方案中没有什么可以改进。

然而，有两点值得一提，以供下一位读者使用。第一个是在您的代码中使用此解决方案不明显，为什么选择您的订单。如果你这样做，你可能想要注意哪个约束被推迟。通常这很明显，但您可能希望根据引用相应表格的其他外键选择您的插入订单。

另一个是关于是否是围绕循环依赖进行工程设计。一方面，这似乎是一个好主意，这样做似乎是一个明显的简化。

不过值得一提的是，在这样的情况下，您可能希望看看多个表继承代替。可以这样做：

  CREATE TABLE p1（
 id serial not null，
 attribute1 int不为null，
 attribute2 text not null，
 check（attribute1> 1或length（attribute2）> 10）
）; 
 
 CREATE TABLE p2（
 id int not null， - 共享主键与p1 
 attribute3 int not null，
 attribute4 text not null 
） ; 
 
 CREATE TABLE组合（
主键（id）
）INHERITS（p1，p2）; 
  

这将所有的列都放在一个表中，但是给你这个表的逻辑接口就好像是两个表在一个公共字段中加入。

I want to enforce that a row in one table must have a matching row in another table, and vice versa. I'm currently doing it like this to work around the fact that you can't REFERENCE a table that hasn't been created yet. Is there a more natural way that I'm not aware of?

CREATE TABLE LE (id int PRIMARY KEY);
CREATE TABLE LE_TYP (id int PRIMARY KEY, typ text);

ALTER TABLE LE ADD CONSTRAINT
  twowayref FOREIGN KEY (id) REFERENCES LE_TYP (id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE LE_TYP ADD CONSTRAINT
  twowayref_rev FOREIGN KEY (id) REFERENCES LE (id) DEFERRABLE INITIALLY DEFERRED;

解决方案

What you are doing is ideal where you have circular constraints. There is nothing that can be improved on in your solution.

There are however two points that are worth mentioning briefly just for the next reader. The first is that with this solution in your code isn't obvious why you are choosing the order you are. If you are doing this you probably want to be careful about which constraint is deferred. Usually this is obvious, but you probably want to choose your insert order based on other foreign keys referencing the appropriate tables.

The other is that of the idea of whether it is desirable to engineer around circular dependencies. On one hand it may seem like a good idea when doing so seems like an obvious simplification.

It is however worth pointing out that in a case like this you may want to look at multiple table inheritance instead. In this way you might do:

CREATE TABLE p1 (
     id serial not null,
     attribute1 int not null,
     attribute2 text not null,
     check (attribute1 > 1 or length(attribute2) > 10)
);

CREATE TABLE p2 (
     id int not null, -- shared primary key with p1
     attribute3 int not null,
     attribute4 text not null
);

CREATE TABLE combined (
     primary key (id)
) INHERITS (p1, p2);

This puts all your columns in one table, but gives you logical interfaces to that table as if it is two tables joined on a common field.

这篇关于如何在PostgreSQL中实现循环约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！