如何強制由同一張表的兩個外鍵組成的複合主鍵的唯一性？

我有兩張桌子：一張user桌子和一張friendship桌子。假設friendship表格如下所示：

friendship
------------
user_one_id
user_two_id
other_fields

我需要強制執行值組合的唯一性(user_one_id, user_two_id)並忽略ordering，因此：

user_one_id | user_two_id
------------+------------
         1 |          2
         2 |          1  -- the DBMS should throw a unique constraint error when trying to insert a row like this one.

另一個重要的一點是user_one_id表示發起者和代表接收者friendship，所以我不能只使兩個id的“更小” 。user_two_id user_one_id

問題

有沒有辦法使用約束來做到這一點，或者我應該以其他方式實現這一點？

根據您的評論，

對於我的案例，user_one_id 代表友誼的發起者，user_two_id 代表友誼的接受者。所以我不能只使用最低值作為 user_one_id。

好吧，你仍然可以做到。您的案例只是排除了行約束以確保這一點。你想要的是使用一個表約束，像這樣。

CREATE TABLE friendship (
 user_one_id int NOT NULL,
 user_two_id int NOT NULL,
 CHECK (user_one_id != user_two_id ),
 PRIMARY KEY (user_one_id, user_two_id)
);
-- you can do least first if you want. doesn't matter.
CREATE UNIQUE INDEX ON friendship (
 greatest(user_one_id, user_two_id),
 least(user_one_id, user_two_id)
);

我們這裡有很多事情要做。我們確保。

1. 兩者都是NOT NULL
2. 兩者不相等
3. 兩者都是UNIQUE (user_one_id, user_two_id)

這留下了一個剩餘的交換唯一性問題，我們通過使用索引實現的自定義唯一表約束來解決這個問題。

布丁中的證明

INSERT INTO friendship VALUES ( 1,2 );
INSERT 0 1
test=# INSERT INTO friendship VALUES ( 2,1 );
ERROR:  duplicate key value violates unique constraint friendship_greatest_least_idx"
DETAIL:  Key ((GREATEST(user_one_id, user_two_id)), (LEAST(user_one_id, user_two_id)))=(2, 1) already exists.

作為一個重要的友好說明，你的名字很愚蠢。關係很好。在生產中，請給他們更好的名字..

friendship
----------
request_initiator
request_target
other_fields

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