約束以防止層次結構中的立即循環

我有一張companies桌子：

CREATE TABLE companies (
id bigserial,
name varchar(255) NOT NULL
PRIMARY KEY (id)
)

我希望公司有父母和孩子。一家公司可能有許多父公司和許多子公司，因此我創建了下表：

CREATE TABLE parent_companies (
id bigserial,
parent_company_id bigint,
  CONSTRAINT parent_companies_parent_company_id_fkey
  FOREIGN KEY (parent_company_id)
  REFERENCES companies(id),
child_company_id bigint,
  CONSTRAINT parent_companies_child_company_id_fkey
  FOREIGN KEY (child_company_id)
  REFERENCES companies(id)
PRIMARY KEY (id)
)

我希望父母和孩子是唯一的，所以我添加了以下約束：

CREATE UNIQUE INDEX parent_companies_parent_company_id_child_company_id_index ON parent_companies (parent_company_id, child_company_id)

這將不允許在數據庫中創建兩次相同的父項和子項。但是，我還想防止子公司被保存為其母公司的母公司。例如，我會防止這種情況發生：

-[ RECORD 1 ]-----+--------------------
id                | 1
parent_company_id | 1
child_company_id  | 2
-[ RECORD 2 ]-----+--------------------
id                | 2
parent_company_id | 2
child_company_id  | 1

由於在記錄 1 中定義公司 1 是公司 2 的母公司，我想要一種方法來防止公司 2 被記錄為公司 1 的母公司。

我認為這可能是使用EXCLUDE或CHECK約束的情況，但我想不出一種方法來使任何一個工作。

您所描述的是表與自身之間的多對多關係（有附加限制）。看：

• 如何在 PostgreSQL 中實現多對多關係？

可以這樣實現：

CREATE TABLE company (
 id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, name text NOT NULL
)

CREATE TABLE hierarchy (
 parent_id integer NOT NULL REFERENCES company
, child_id  integer NOT NULL REFERENCES company
, PRIMARY KEY (parent_id, child_id)
, CONSTRAINT no_1step_loop CHECK (child_id <> parent_id)
);

CREATE UNIQUE INDEX hierarchy_no_2step_loop ON hierarchy (LEAST(child_id, parent_id), GREATEST(child_id, parent_id);

唯一索引hierarchy_no_2step_loop排除了您在問題中解決的兩個步驟的循環。有關的：

• 約束多列？

CHECK約束no_1step_loop排除了一步上的循環（行直接引用自己）。

但是排除更多步驟的循環並不是那麼簡單。您可以讓觸發器跟隨指向根的連結鏈，並在檢測到循環時引發異常。但是，這對於並發寫入操作來說更昂貴並且本質上是不安全的。為了使其防彈，您必須使用提升的事務隔離（如SERIALIZABLE）或在鏈中的所有行上寫入鎖。但這很容易在繁重的寫入負載下發生死鎖……

引用自：https://dba.stackexchange.com/questions/294209