主页 / dba / 问题 / 241075
Accepted
Victoria Stuart
Asked: 2019-06-21 17:00:56 +0800 CST

约束多列？

  • 772

我有一个 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) );
database-design postgresql

1 个回答

  1. Best Answer

    A UNIQUE，多列，部分表达式索引应该可以解决问题：

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

    与INSERT ... ON CONFLICT DO NOTHING.

    有关的：

    另外：如果这些是 IP 地址，请考虑数据类型cidr或ip4来自附加模块 ip4r的 column rel。

    • 3

相关问题