約束多列？

我有一個 table edges，它描述了圖表中的關係：

CREATE TABLE IF NOT EXISTS edges (
  src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
 ,tgt INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE
 ,rel TEXT NOT NULL
 ,rel_type INT NOT NULL
 ,PRIMARY KEY (src, tgt, rel)
 ,UNIQUE (src, tgt, rel)
 );

插入後：

select * from edges;
src | tgt |    rel    | rel_type 
-----+-----+-----------+----------
  1 |   2 | 5.4.2.2   |        2
  2 |   3 | 5.3.1.9   |        2
  ...
  5 |   6 | 2.7.1.2   |        1
  5 |   6 | 2.7.1.147 |        1
  6 |   2 | 5.3.1.9   |        2
  6 |   3 | 5.3.1.9   |        2
  ...

我rel_type用來指定邊緣方向性（0：無向；1：源到目標；2：雙向）。

(3, 2, '5.3.1.9', 2)因此，對於上面的第二個條目（例如），插入是多餘的——它已經表達了互惠關係2 --> 3和3 --> 2。

如何添加一個約束來防止插入那些冗餘的互惠關係——理想情況下ON CONFLICT DO NOTHING？

基本上，類似的東西（這些不起作用：首先是由於語法；其次是由於其他問題）：

ALTER TABLE edges ADD CONSTRAINT duplicate_rel_check CHECK ((src, tgt) <> (tgt, src) WHERE rel_type = 2);

或者

CREATE UNIQUE INDEX ON edges ( greatest(src, tgt, rel_type=2), least(tgt, src, rel_type=2) );

A UNIQUE，多列，部分錶達式索引應該可以解決問題：

CREATE UNIQUE INDEX ON edges (LEAST(src, tgt), GREATEST(src, tgt), rel)
WHERE rel_type = 2;

與INSERT ... ON CONFLICT DO NOTHING.

有關的：

• 用於跨多列排列的唯一 postgres 約束
• 如何創建具有唯一組合主鍵的 postgres 表？
• PostgreSQL 多列唯一約束和 NULL 值

另外：如果這些是 IP 地址，請考慮數據類型cidr或ip4來自附加模組 ip4r的 column rel。

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