Postgresql:对 2 列联合的唯一约束 [英] Postgresql: Unique constraint over Union of 2 columns

问题描述

我有以下表格:

交易

 id   |   amount
------------------
  1   |    100
  2   |    -100
  3   |    250
  4   |   -250

TRANSACTION_LINKS

TRANSACTION_LINKS

id  | send_tx  | receive_tx
---------------------------
 1  |     2    |     1 
 2  |     4    |     2 

交易链接表中的 send_tx 和 receive_tx 列使用指向交易表 ID 的外键.

The send_tx and receive_tx columns in the transaction links table use foreign keys pointing to the ID of the transactions table.

这就是我创建交易链接表的方式

This is how I create the transaction links table

CREATE TABLE IF NOT EXISTS transaction_links
(
    id            BIGSERIAL PRIMARY KEY,
    send_id       INT NOT NULL UNIQUE REFERENCES transactions(id) ON DELETE
    RESTRICT,
    receive_id    INT NOT NULL UNIQUE REFERENCES transactions(id) ON DELETE
    RESTRICT
);

我想对 send_tx 和 receive_tx 创建一个唯一约束，这意味着如果在 receive_tx 列 中找到事务 ID 1，然后

I want to create a unique constraint over both send_tx and receive_tx, meaning that if transaction id 1 is found in the receive_tx column, then

• 没有其他交易链接可以有 receiving_tx = 1
• 没有其他交易链接可以有 sending_tx = 1

• no other transaction link can have the receiving_tx = 1
• no other transaction link can have the sending_tx = 1

我知道我可以对每一列分别设置唯一的约束，但这只能解决我的第一个问题

I know that I can have a unique constraint on each column separately, but that only solves my first problem

本质上，如果我将 (1,2) 插入事务链接，那么插入 (1,3) 或 (3,1) 或 (4,2) 或 (2,4) 都应被拒绝此外，在我的设计中，交易表包含的列比此处显示的要多得多，为了简单起见，我只包含了金额.

essentially, if I insert (1,2) into transaction links, then inserting (1,3) or (3,1) or (4,2) or (2,4) should all be rejected Also, in my design, the transactions table contains many more columns than what is shown here, I've only included the amount for simplicity's sake.

推荐答案

您可以使用 排除约束，只需要一个索引:

You can use an exclusion constraint which only requires a single index:

alter table transaction_links
  add constraint check_tx
  exclude using gist ( (array[send_id, receive_id]) with &&);

&& 运算符是数组的重叠"运算符 - 这意味着具有共同的元素，无论数组中元素的顺序如何.在这种情况下，约束阻止插入任何行，其中 (send_id, receive_id) 的任何值出现在表的其他行中(无论列如何).

The && operator is the "overlaps" operator for arrays - which means "have elements in common, regardless of the order of the elements in the array. In this case the constraint prevents to insert any row where any value of (send_id, receive_id) appears in some other row of the table (regardless of the column).

但是，您需要 intarray 扩展.

However, you need the intarray extension for that.

在线示例:https://rextester.com/QOYS23482

这篇关于Postgresql:对 2 列联合的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！