Postgresql

跨表唯一

  • March 22, 2015

我有一個包含使用者的表。每個使用者都有一個主電子郵件和一個指示該使用者是否被刪除的標誌（我們從不硬刪除使用者）。

但是，每個使用者也可以有額外的電子郵件。

無論如何，電子郵件地址必須是唯一的，我想在數據庫級別強制執行此操作。對於主電子郵件地址，這很容易。我可以簡單地添加一個帶有WHERE not is_deleted限制的唯一索引，以便可以重新使用已刪除使用者的電子郵件。

但是，對於更棘手的輔助電子郵件。

  • 如果我將它們儲存在數組欄位中的同一個表中，我可能不僅會失去索引它們的能力（我們有超過 50k 的使用者並且需要能夠通過電子郵件進行搜尋，所以這是不行的）但到目前為止據我所知，我也不能將一個唯一的索引/約束放入數組中。
  • 如果我使用單獨的表（這顯然更乾淨），我將不得不將is_deleted標誌從使用者複製到該表，這也很醜陋，但允許我簡單地將 UNIQUE 索引與WHERE not is_deleted.

有沒有更好的解決方案來實現我想要做的事情？

為了強制使用唯一的電子郵件地址，我將刪除所有競爭的電子郵件列並將它們儲存在一個中央email表中，用於所有活動的電子郵件。另一個用於刪除電子郵件的表格：

CREATE TABLE users (
 user_id  serial PRIMARY KEY
, username text UNIQUE NOT NULL
, email    text UNIQUE -- FK added below  -- can also be NOT NULL
);

CREATE TABLE email (
 email    text PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
, UNIQUE (user_id, email)  -- seems redundant, but required for FK
);

ALTER TABLE users ADD CONSTRAINT users_primary_email_fkey
FOREIGN KEY (user_id, email) REFERENCES email (user_id, email);

CREATE TABLE email_deleted (
 email_id serial PRIMARY KEY
, email    text NOT NULL  -- not necessarily unique
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
);

這邊走：

  • 活動電子郵件是唯一的，由 的 PK 約束強制執行email。

  • 每個使用者可以擁有任意數量的活動和已刪除的電子郵件，但是…

  • 每個使用者只能有一個主電子郵件。

  • 每封電子郵件始終歸一個使用者所有，並隨使用者一起刪除。

  • 要軟刪除電子郵件（不失去電子郵件及其與使用者的從屬關係，請將行從email移至email_deleted.

    • 這種方式不能刪除使用者的主郵箱，因為不能刪除主郵箱。

  • 我將 FK 約束設計users_primary_email_fkey為 span (user_id, email)，起初這似乎是多餘的。但是這樣，主電子郵件只能是同一使用者實際擁有的電子郵件。

由於MATCH SIMPLEFK 約束的預設行為，您仍然可以輸入沒有主電子郵件的使用者，因為如果任何列為空，則不會強制執行 FK 約束。

細節：

對這個解決方案的UNIQUE約束users.email是多餘的，但出於其他原因它可能很有用。自動創建的索引應該派上用場（例如對於此答案中的最後一個查詢）。

唯一沒有以這種方式強制執行的是每個使用者都有一個主電子郵件。你也可以這樣做。將NOT NULL約束添加到users.email

UNIQUE (user_id, email)FK 約束需要：

您無疑已經在上述模型中發現了**循環引用。**與人們所期望的相反，這確實有效。

只要users.email可以NULL，這很簡單：

  1. INSERT沒有電子郵件的使用者。
  2. INSERT引用所有者的電子郵件user_id。
  3. UPDATE使用者設置它的主要電子郵件（如果適用）。

它甚至適用於users.emailset to NOT NULL。您必須同時插入使用者和電子郵件：

WITH u AS (
  INSERT INTO users(username, email)
  VALUES ('user_foo', 'foo@mail.com')
  RETURNING email, user_id
  )
INSERT INTO email (email, user_id)
SELECT email, user_id
FROM   u;

IMMEDIATE在每個語句的末尾檢查 FK 約束（預設）。以上是一種說法。這就是為什麼它在兩個單獨的語句會失敗的情況下起作用的原因。詳細解釋：

要將使用者的所有電子郵件作為數組獲取，首先是主電子郵件：

SELECT u.*, e.emails
FROM   users u
    , LATERAL (
     SELECT ARRAY (
     SELECT email
     FROM   email
     WHERE  user_id = u.user_id
     ORDER  BY (email <> u.email)  -- sort primary email first
     ) AS emails
  ) e
WHERE  user_id = 1;

您可以使用它創建一個VIEW以方便使用。

LATERAL需要 Postgres 9.3。在 pg 9.2 中使用相關子查詢：

SELECT *, ARRAY (
            SELECT email
            FROM   email
            WHERE  user_id = u.user_id
            ORDER  BY (email <> u.email)  -- sort primary email first
            ) AS emails
FROM   users u
WHERE  user_id = 1;

要軟刪除電子郵件：

WITH del AS (
  DELETE FROM email
  WHERE  email = 'spam@mail.com'
  RETURNING email, user_id
  )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM del;

要軟刪除給定使用者的主要電子郵件：

WITH upd AS (
  UPDATE users u
  SET    email = NULL
  FROM   (SELECT user_id, email FROM users WHERE user_id = 123 FOR UPDATE) old
  WHERE  old.user_id = u.user_id
  AND    u.user_id = 1
  RETURNING old.*
  )
,    del AS (
  DELETE FROM email
  USING  upd
  WHERE  email.email = upd.email
  )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM upd;

細節：

以上所有內容的快速測試：SQL Fiddle。

考慮這一點的一種方法是，您有兩類使用者，它們的規則略有不同：已刪除和未刪除。已刪除使用者的電子郵件可能會發生衝突，未刪除的使用者電子郵件必須是唯一的。

因為這兩個類有不同的規則（即約束），而不是使用標誌來指示使用者是否被刪除，我會復製表：一組用於已刪除的使用者，一組用於未刪除的使用者。然後我會簡單地在表上為未刪除的使用者電子郵件創建唯一約束。

優點：

  • 在大多數操作將針對“未刪除”使用者的假設下，此模型提供了更好的性能。“未刪除”使用者表僅代表目前的活動使用者集，因此它們會相應地增長和縮小。刪除的使用者表只會增長（除非您允許取消刪除使用者）。
  • 查詢所有使用者仍然相對簡單，如果您想讓它像以前一樣簡單，您可以簡單地創建一個合併兩個集合的視圖（並is_deleted根據行的來源生成一個標誌）。
  • 這不依賴於CREATE INDEX...WHERE. 我提到這一點的原因是部分索引沒有得到普遍支持（例如 MS-SQL 和 DB2）。顯然，您對 PostgreSQL 沒有任何問題，但避免潛在的未來遷移障礙永遠不會有壞處。

缺點：

  • 刪除（或取消刪除）使用者更複雜。您現在必須移動行，而不是翻轉一個簡單的標誌欄位。但是，您提到您正在使用 SQLAlchemy（不錯的選擇！）。如果我沒記錯的話，建構這樣的語句沒有問題INSERT..SELECT FROM，應該使將條目從未刪除移動到已刪除（反之亦然）變得簡單。
  • 使用者表的兩倍（+聯合的可選視圖）。這不會影響性能，但可能會使任何模式圖複雜化！

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

相關問答

Postgresql

如何約束一個表中的行值以匹配另一表中的行值？

  • June 13, 2022
檢視問答
Postgresql

外鍵 NULL 與空字元串的外鍵

  • November 18, 2020
檢視問答
Postgresql

自定義唯一約束，僅在一列具有特定值時強制執行

  • October 9, 2020
檢視問答
Postgresql

條件外鍵關係

  • February 13, 2019
檢視問答
Postgresql

不同列的唯一約束

  • October 6, 2017
檢視問答
Postgresql

為什麼複合外鍵需要單獨的唯一約束？

  • October 26, 2016
檢視問答