PolarDB：約束

簡介

資料類型是一種限制能夠儲存在表中資料類別的方法。但是對於很多應用來說，它們提供的約束太粗糙。例如，一個包含產品價格的列應該只接受正值。但是沒有任何一種標準資料類型只接受正值。另一個問題是我們可能需要根據其他列或行來約束一個列中的資料。例如，在一個包含產品資訊的表中，對於每個產品編號應該只有一行。

到目前為止，SQL允許我們在列和表上定義約束。約束讓我們能夠根據我們的願望來控製表中的資料。如果一個使用者試圖在一個列中儲存違反一個約束的資料，一個錯誤會被拋出。即便是這個值來自於預設值定義，這個規則也同樣適用。

檢查約束

一個檢查約束是最普通的約束類型。它允許我們指定一個特定列中的值必須要滿足一個布林運算式。例如，為了要求正值的產品價格，我們可以使用：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0)
    );

如你所見，約束定義就和預設值定義一樣跟在資料類型之後。預設值和約束之間的順序沒有影響。一個檢查約束有關鍵字CHECK以及其後的包圍在圓括弧中的運算式組成。檢查約束運算式應該涉及到被約束的列，否則該約束也沒什麼實際意義。

我們也可以給與約束一個獨立的名稱。這會使得錯誤訊息更為清晰，同時也允許我們在需要更改約束時能引用它。文法為：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CONSTRAINT positive_price CHECK (price > 0)
    );

要指定一個命名的約束，請在約束名稱標識符前使用關鍵詞CONSTRAINT，然後把約束定義放在標識符之後（如果沒有以這種方式指定一個約束名稱，系統將會為我們選擇一個）。

一個檢查約束也可以引用多個列。例如我們儲存一個普通價格和一個打折後的價格，而我們希望保證打折後的價格低於普通價格：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0),
        discounted_price numeric CHECK (discounted_price > 0),
        CHECK (price > discounted_price)
    );

前兩個約束看起來很相似。第三個則使用了一種新文法。它並沒有依附在一個特定的列，而是作為一個獨立的項出現在逗號分隔的列列表中。列定義和這種約束定義可以以混合的順序出現在列表中。

我們將前兩個約束稱為列約束，而第三個約束為資料表條件約束，因為它獨立於任何一個列定義。列約束也可以寫成資料表條件約束，但反過來不行，因為一個列約束只能引用它所依附的那一個列（本資料庫並不強制要求這個規則，但是如果我們希望表定義能夠在其他資料庫系統中工作，那就應該遵循它）。上述例子也可以寫成：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        CHECK (price > 0),
        discounted_price numeric,
        CHECK (discounted_price > 0),
        CHECK (price > discounted_price)
    );

甚至是：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric CHECK (price > 0),
        discounted_price numeric,
        CHECK (discounted_price > 0 AND price > discounted_price)
    );

資料表條件約束也可以用列約束相同的方法來指定名稱：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        CHECK (price > 0),
        discounted_price numeric,
        CHECK (discounted_price > 0),
        CONSTRAINT valid_discount CHECK (price > discounted_price)
    );

說明

本資料庫不支援參考資料表資料以外的要檢查的新增或更新的行的CHECK約束。 雖然違反此規則的CHECK約束在簡單測試中看起來能工作，它不能保證資料庫不會達到約束條件為假(false)的狀態（由於涉及的其他行隨後發生了更改）。 這將導致資料庫轉儲和重新載入失敗。 即使完整的資料庫狀態與約束一致，重新載入也可能失敗，因為行未按照滿足約束的順序載入。 如果可能的話，使用UNIQUE, EXCLUDE,或 FOREIGN KEY約束以表示跨行和跨表限制。

如果你希望的是在插入行時的時候對其他行進行一次性檢查，而不是持續維護的一致性保證，一個自訂的 trigger 可以用於實現這個功能。 （此方法避免了轉儲/重新載入問題，因為pg_dump不會重新安裝觸發器直到重新載入資料之後，因此不會在轉儲/重新載入期間強制執行檢查。）

本資料庫假定CHECK約束的條件是不可變的，也就是說，它們始終為同一輸入行提供相同的結果。 這個假設是僅在插入或更新行時,而不是在其他時間檢查CHECK約束的原因。 （上面關於不引用其他表資料的警告實際上是此限制的特殊情況。）

打破此假設的常見方法的一個樣本是在 CHECK運算式中引用使用者定義的函數，然後更改該函數的行為。 本資料庫不會禁止那樣，但它不會注意到現在表中是否有行違反了CHECK約束。這將導致後續資料庫轉儲和重新載入失敗。 處理此類更改的建議方法是刪除約束（使用ALTER TABLE），調整函數定義，然後重新添加約束，從而對所有錶行進行重新檢查。

非空約束

一個非空約束僅僅指定一個列中不會有空值。文法例子：

    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric
    );

一個非空約束總是被寫成一個列約束。一個非空約束等價於建立一個檢查約束CHECK (``column_name`` IS NOT NULL)，但在本資料庫中建立一個顯式的非空約束更高效。這種方式建立的非空約束的缺點是我們無法為它給予一個顯式的名稱。

當然，一個列可以有多於一個的約束，只需要將這些約束一個接一個寫出：

    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric NOT NULL CHECK (price > 0)
    );

約束的順序沒有關係，因為並不需要決定約束被檢查的順序。

NOT NULL約束有一個相反的情況：NULL約束。這並不意味著該列必須為空白，進而肯定是無用的。相反，它僅僅選擇了列可能為空白的預設行為。SQL 標準中並不存在NULL約束，因此它不能被用於可移植的應用中（本資料庫中加入它是為了和某些其他資料庫系統相容）。但是某些使用者喜歡它，因為它使得在一個指令檔中可以很容易的進行約束切換。例如，初始時我們可以：

    CREATE TABLE products (
        product_no integer NULL,
        name text NULL,
        price numeric NULL
    );

然後可以在需要的地方插入NOT關鍵詞。

唯一約束

唯一約束保證\在一列中或者一組列中儲存的資料在表中所有行間是唯一的。寫成一個列約束的文法是：

    CREATE TABLE products (
        product_no integer UNIQUE,
        name text,
        price numeric
    );

寫成一個資料表條件約束的文法是：

    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric,
        UNIQUE (product_no)
    );

當寫入資料表條件約束時。

要為一組列定義一個唯一約束，把它寫作一個表級約束，列名用逗號分隔：

    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        UNIQUE (a, c)
    );

這指定這些列的組合值在整個表的範圍內是唯一的，但其中任意一列的值並不需要是（一般也不是）唯一的。

我們可以通常的方式為一個唯一索引命名：

    CREATE TABLE products (
        product_no integer CONSTRAINT must_be_different UNIQUE,
        name text,
        price numeric
    );

增加一個唯一約束會在約束中列出的列或列組上自動建立一個唯一 B-tree 索引。只覆蓋某些行的唯一性限制不能被寫為一個唯一約束，但可以通過建立一個唯一的部分索引來強制這種限制。

通常，如果表中有超過一行在約束所包括列上的值相同，將會違反唯一約束。但是在這種比較中，兩個空值被認為是不同的。這意味著即便存在一個唯一約束，也可以儲存多個在至少一個被約束列中包含空值的行。這種行為符合 SQL 標準，但我們聽說一些其他 SQL 資料庫可能不遵循這個規則。所以在開發需要可移植的應用時應注意這一點。

主鍵

一個主鍵約束表示可以用作表中行的唯一識別碼的一個列或者一組列。這要求那些值都是唯一的並且非空。因此，下面的兩個表定義接受相同的資料：

    CREATE TABLE products (
        product_no integer UNIQUE NOT NULL,
        name text,
        price numeric
    );

    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );

主鍵也可以包含多於一個列，其文法和唯一約束相似：

    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        PRIMARY KEY (a, c)
    );

增加一個主鍵將自動在主鍵中列出的列或列組上建立一個唯一 B-tree 索引。並且會強制這些列被標記為NOT NULL。

一個表最多隻能有一個主鍵（可以有任意數量的唯一和非空約束，它們可以達到和主鍵幾乎一樣的功能，但只能有一個被標識為主鍵）。關聯式資料庫理論要求每一個表都要有一個主鍵。但本資料庫中並未強制要求這一點，但是最好能夠遵循它。

主鍵對於文檔和用戶端應用都是有用的。例如，一個允許修改行值的 GUI 應用可能需要知道一個表的主鍵，以便能唯一地標識行。如果定義了主鍵，資料庫系統也有多種方法來利用主鍵。例如，主鍵定義了外鍵要引用的預設目標列。

外鍵

一個外鍵約束指定一列（或一組列）中的值必須匹配出現在另一個表中某些行的值。我們說這維持了兩個關聯表之間的參考完整性。

例如我們有一個使用過多次的產品表：

    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );

讓我們假設我們還有一個儲存這些產品訂單的表。我們希望保證訂單表中只包含真正存在的產品的訂單。因此我們在訂單表中定義一個引用產品表的外鍵約束：

    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        product_no integer REFERENCES products (product_no),
        quantity integer
    );

現在就不可能建立包含不存在於產品表中的product_no值（非空）的訂單。

我們說在這種情況下，訂單表是參考資料表而產品表是被參考資料表。相應地，也有引用和被引用列的說法。

我們也可以把上述命令簡寫為：

    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        product_no integer REFERENCES products,
        quantity integer
    );

因為如果缺少列的列表，則被參考資料表的主鍵將被用作被引用列。

一個外鍵也可以約束和引用一組列。照例，它需要被寫成資料表條件約束的形式。下面是一個例子：

    CREATE TABLE t1 (
      a integer PRIMARY KEY,
      b integer,
      c integer,
      FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
    );

當然，被約束列的數量和類型應該匹配被引用列的數量和類型。

按照前面的方式，我們可以為一個外鍵約束命名。

一個表可以有超過一個的外鍵約束。這被用於實現表之間的多對多關係。例如我們有關於產品和訂單的表，但我們現在希望一個訂單能包含多種產品（這在上面的結構中是不允許的）。我們可以使用這種表結構：

    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );

    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        shipping_address text,
        ...
    );

    CREATE TABLE order_items (
        product_no integer REFERENCES products,
        order_id integer REFERENCES orders,
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );

我們知道外鍵不允許建立與任何產品都不相關的訂單。但如果一個產品在一個引用它的訂單建立之後被移除會發生什嗎？SQL 允許我們處理這種情況。直觀上，我們有幾種選項：

• 不允許刪除一個被引用的產品。

• 同時也刪除引用產品的訂單。

• 其他。

為了說明這些，讓我們在上面的多對多關係例子中實現下面的策略：當某人希望移除一個仍然被一個訂單引用（通過order_items）的產品時 ，我們組織它。如果某人移除一個訂單，訂單項也同時被移除：

    CREATE TABLE products (
        product_no integer PRIMARY KEY,
        name text,
        price numeric
    );

    CREATE TABLE orders (
        order_id integer PRIMARY KEY,
        shipping_address text,
        ...
    );

    CREATE TABLE order_items (
        product_no integer REFERENCES products ON DELETE RESTRICT,
        order_id integer REFERENCES orders ON DELETE CASCADE,
        quantity integer,
        PRIMARY KEY (product_no, order_id)
    );

限制刪除或者串聯刪除是兩種最常見的選項。RESTRICT阻止刪除一個被引用的行。NO ACTION表示在約束被檢察時如果有任何引用行存在，則會拋出一個錯誤，這是我們沒有指定任何東西時的預設行為（這兩種選擇的本質不同在於NO ACTION允許檢查被延遲到事務的最後，而RESTRICT則不會）。CASCADE指定當一個被引用行被刪除後，引用它的行也應該被自動刪除。還有其他兩種選項：SET NULL和SET DEFAULT。這些將導致在被引用行被刪除後，引用行中的引用列被置為空白值或它們的預設值。注意這些並不會是我們免於遵守任何約束。例如，如果一個動作指定了SET DEFAULT，但是預設值不滿足外鍵約束，操作將會失敗。

與ON DELETE相似，同樣有ON UPDATE可以用在一個被引用列被修改（更新）的情況，可選的動作相同。在這種情況下，CASCADE意味著被引用列的更新值應該被複製到引用行中。

正常情況下，如果一個引用行的任意一個引用列都為空白，則它不需要滿足外鍵約束。如果在外鍵定義中加入了MATCH FULL，一個引用行只有在它的所有引用列為空白時才不需要滿足外鍵約束（因此空和非空值的混合肯定會導致MATCH FULL約束失敗）。如果不希望引用行能夠避開外鍵約束，將引用行聲明為NOT NULL。

一個外鍵所引用的列必須是一個主鍵或者被唯一約束所限制。這意味著被引用列總是擁有一個索引（位於主鍵或唯一約束之下的索引），因此在其上進行的一個引用行是否匹配的檢查將會很高效。由於從被參考資料表中DELETE一行或者UPDATE一個被引用列將要求對參考資料表進行掃描以得到匹配舊值的行，在引用列上建立合適的索引也會大有益處。由於這種做法並不是必須的，而且建立索引也有很多種選擇，所以外鍵約束的定義並不會自動在引用列上建立索引。

外鍵約束的文法描述請參考CREATE TABLE。

排他約束

排他約束保證如果將任何兩行的指定列或運算式使用指定操作符進行比較，至少其中一個操作符比較將會返回否或空值。文法是：

    CREATE TABLE circles (
        c circle,
        EXCLUDE USING gist (c WITH &&)
    );

增加一個排他約束將在約束聲明所指定的類型上自動建立索引。