如何在 PostgreSQL 中對第六範式強制執行唯一約束

我有一張表person，我打算將其規範化為第六範式。該表具有屬性username、email和phone。我想使用第六範式的原因是保留這些列的更改歷史並向它們添加額外的元數據（例如，關於數據的可信度）。

我計劃創建新表person_username，並擁有將它們組合在一起並具有觸發器的視圖，person_email以便使用者可以插入和更新視圖，並且這些更改將定向到基礎表。person_phone``person``INSTEAD OF

我的問題是，如何UNIQUE在使用第六範式時強制執行約束。表的結構person_email如下所示：

CREATE TABLE person_email (
   person_id integer FOREIGN KEY person(id),
   username text NOT NULL,
   valid_from datetime
);

表中的數據可能如下所示：

標記為粗體的行包含最新（目前）資訊。使用者 1 和 2 都將目前使用者名設置為foo不允許使用的使用者名。但是，我不能username UNIQUE，因為在遙遠的過去有人使用過這個名字並不意味著其他人現在不能使用它。

我需要的是一個UNIQUE (username)約束，它只會查看每個使用者的最新行。如何做到這一點？有沒有辦法利用 PostgreSQLEXCLUDE約束？

是的，您可以使用排除約束。

您需要安裝附加模組btree_gist來添加我們需要的操作符類來支持約束的多列索引。

沒有像您顯示的數據類型*“日期時間” 。*你的價值觀表明date，所以我會去的。（實際的數據類型對實現很重要！）

此外，您的表格設計valid_from通常可以正常工作。valid_to可以從下一行導出。但是出於排除約束的目的，我們需要valid_to在同一行中。所以添加（冗餘）。

ALTER TABLE person_email ADD CONSTRAINT person_email_no_username_overlap
EXCLUDE USING gist (username WITH =, daterange(valid_from, valid_to, '[]') WITH &&);

這樣，相同的使用者名永遠不會在重疊的時間範圍內使用。

請注意我如何使用. '[]'適應您的需求。不同的數據類型存在細微差別。

NULL下限或上限意味著“無界”。

或者，您可以使用daterange列作為開頭。兩者各有利弊。

有關的：

• 如何使用 NOW() 函式作為範圍的上限？
• 為什麼 PostgreSQL 認為範圍類型中的 NULL 邊界與無限邊界不同？
• 在 PostgreSQL 中查找重疊的日期範圍
• numrange 和 boolean 的複合約束

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