將除一個以外的所有列標記為主鍵是否合理？

我有一張代表電影的表格。欄位是：

id (PK), title, genre, runtime, released_in, tags, origin, downloads。

我的數據庫不會被重複的行污染，所以我想強制執行唯一性。問題是不同的電影可能有相同的標題，甚至是相同的欄位，除了tags和downloads。如何執行唯一性？

我想到了兩種方法：

• 製作除downloads主鍵以外的所有欄位。downloads因為它是 JSON，所以我一直在外面，它可能會影響性能。
• 僅保留id作為主鍵，但為所有其他列添加唯一約束（再次除外downloads）。

我讀了這個非常相似的問題，但我不太明白我該怎麼做。目前此表與任何其他表均不相關，但將來可能。

目前我的記錄略少於 20,000 條，但我預計這個數字會增長。我不知道這是否與這個問題有些相關。

**編輯：**我修改了架構，這是我創建表的方式：

CREATE TABLE movies (
   id          serial PRIMARY KEY,
   title       text NOT NULL,
   runtime     smallint NOT NULL CHECK (runtime >= 0),
   released_in smallint NOT NULL CHECK (released_in > 0),
   genres      text[] NOT NULL default ARRAY[]::text[],
   tags        text[] NOT NULL default ARRAY[]::text[],
   origin      text[] NOT NULL default ARRAY[]::text[],
   downloads   json NOT NULL,
   inserted_at timestamp NOT NULL default current_timestamp,
   CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

我還添加了timestamp專欄，但這不是問題，因為我不會碰它。所以它總是自動的和獨特的。

您的表定義現在看起來很合理。對於所有列NOT NULL，UNIQUE約束將按預期工作——除了拼寫錯誤和細微的拼寫差異，我擔心這可能相當普遍。考慮@a_horse 的評論。

具有功能唯一索引的替代方案

另一種選擇是功能唯一索引（類似於@Dave 評論的內容）。但我會使用uuid數據類型來優化索引大小和性能。

從數組到文本的轉換不是IMMUTABLE（由於它的通用實現）：

• 索引數組以進行全文搜尋

因此，您需要一個小輔助函式來聲明它不可變：

CREATE OR REPLACE FUNCTION f_movie_uuid(_title text
                                     , _runtime int2
                                     , _released_in int2
                                     , _genres text[]
                                     , _tags text[]
                                     , _origin text[])
 RETURNS uuid LANGUAGE sql IMMUTABLE AS  -- faking IMMUTABLE
'SELECT md5(_title || _runtime::text || _released_in::text
        || _genres::text || _tags::text || _origin::text)::uuid';

將其用於索引定義：

CREATE UNIQUE INDEX movies_uni_idx
ON movies (f_movie_uuid(title,runtime,released_in,genres,tags,origin));

SQL小提琴。

更多細節：

• 當所有值都是 36 個字元時，使用 char 與 varchar 進行索引查找會明顯更快嗎
• 將文本表示中的十六進制轉換為十進制數

您可以將生成的 UUID 用作 PK，但我仍會使用serial具有 4 個字節的列，這對於 FK 引用和其他用途來說既簡單又便宜。對於需要獨立生成 PK 值的分佈式系統，UUID 將是一個很好的選擇。或者對於非常大的桌子，但我們的太陽系中幾乎沒有足夠的電影來做到這一點。

優點和缺點

唯一約束通過所涉及列上的唯一索引來實現。首先將相關列放在約束定義中，這樣您就有了一個有用的索引，可用於其他目的作為附帶利益。

還有其他特定的好處，這裡是一個列表：

• PostgreSQL 如何強制執行 UNIQUE 約束/它使用什麼類型的索引？

功能唯一索引的大小（可能要小得多），這可以大大加快速度。如果您的列不是太大，則差異不會太大。計算的成本也很小。

連接所有列可能會引入誤報 ( 'foo ' || 'bar' = 'foob ' || 'ar'，但在這種情況下這似乎不太可能。錯字的可能性更大，您可以在這里安全地忽略它。

唯一性和數組

必須對數組進行一致的排序，以便在依賴於運算符的任何唯一排列中有意義，=因為'{1,2}' <> '{2,1}'. 我建議使用PK和唯一條目的查找表genre，以便對數組元素進行模糊搜尋。然後：tag``origin``serial

• 要麼實現完全規範化的 n:m 關係，還提供參照完整性。每組引用的唯一性更難建立，您可以使用MATERIALIZE VIEW帶有聚合數組的（MV）作為墊腳石。

  • 如何在 PostgreSQL 中實現多對多關係？

• 或使用已排序的 FK 引用數組進行操作（FK 約束尚不支持）。來自附加模組intarray的工具可能會派上用場：

  • 比較數組是否相等，忽略元素的順序

無論哪種方式，直接使用數組或使用規範化模式和物化視圖，使用正確的索引和運算符搜尋可以非常有效：

• PostgreSQL 可以索引數組列嗎？

在旁邊

如果您使用 Postgres 9.4 或更高版本，請考慮jsonb使用json.

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