5.3. 約束 · PostgreSQL 中文文檔 9.3

# 5.3\. 約束數據類型是限制我們可以在表里存儲什么數據的一種方法。不過，對于許多應用來說，這種限制實在是太粗糙了。比如，一個包含產品價格的字段應該只接受正數。但是沒有哪種標準數據類型只接受正數。另外一個問題是你可能需要根據其它字段或者其它行的數據來約束字段數據。比如，在一個包含產品信息的表中，每個產品編號都應該只有一行。對于這些問題，SQL 允許你在字段和表上定義約束。約束允許你對數據施加任意控制。如果用戶企圖在字段里存儲違反約束的數據，那么就會拋出一個錯誤。這種情況同時也適用于數值來自缺省值的情況。 ## 5.3.1\. 檢查約束檢查約束是最常見的約束類型。它允許你聲明在某個字段里的數值必須使一個布爾表達式為真。比如，要強制一個正數的產品價格，你可以用： ``` 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)** ); ``` 頭兩個約束看上去很面熟。第三個使用了一個新的語法。它沒有附著在某個字段上，而是在逗號分隔的字段列表中以一個獨立行的形式出現。字段定義和約束定義可以按照任意順序列出。我們稱頭兩個約束是"字段約束"，而第三個約束是"表約束"(和字段定義分開寫)。字段約束也可以寫成表約束，而反過來很可能不行，因為系統假設字段約束只引用它所從屬的字段。 PostgreSQL并不強制這條規則，但是如果你希望自己的表定義可以和其它數據庫系統兼容，那么你最好還是遵循這條規則。上面的例子也可以這么寫： ``` 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) ); ``` 我們還要注意的是，當約束表達式計算結果為真或 NULL 的時候，檢查約束會被認為是滿足條件的。因為大多數表達式在含有 NULL 操作數的時候結果都是 NULL ，所以這些約束不能阻止字段值為 NULL 。要確保一個字段值不為 NULL ，可以使用下一節介紹的非空約束。 ## 5.3.2\. 非空約束非空約束只是簡單地聲明一個字段必須不能是 NULL。下面是一個例子： ``` CREATE TABLE products ( product_no integer **NOT NULL**, name text **NOT NULL**, price numeric ); ``` 一個非空約束總是寫成一個字段約束。非空約束在功能上等效于創建一個檢查約束 `CHECK (``_column_name_` IS NOT NULL)，但在PostgreSQL里，創建一個明確的非空約束效率更高。缺點是你不能給它一個明確的名字。當然，一個字段可以有多個約束。只要一個接著一個寫就可以了： ``` CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) ); ``` 它們的順序無所謂。順序并不影響約束檢查的順序。 `NOT NULL`約束有個相反的約束：`NULL`約束。它并不意味著該字段必須是空，因為這樣的字段也沒用。它只是定義了該字段可以為空的這個缺省行為。在 SQL 標準里沒有定義`NULL`約束，因此不應該在可移植的應用中使用它。在PostgreSQL里面增加這個約束只是為了和其它數據庫系統兼容。不過，有些用戶喜歡它，因為這個約束可以讓他們很容易在腳本文件里切換約束。比如，你可以從下面這樣開始： ``` CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL ); ``` 然后在需要的時候插入`NOT`關鍵字。 > **Tip:** 在大多數數據庫設計里，主要的字段都應該標記為非空。 ## 5.3.3\. 唯一約束唯一約束保證在一個字段或者一組字段里的數據與表中其它行的數據相比是唯一的。它的語法是： ``` 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 ); ``` 添加一個唯一約束通常會自動在約束中使用的列或一組列上創建一個唯一btree索引。通常，如果包含在唯一約束中的那幾個字段在表中有多個相同的行，就違反了唯一約束。但是在這種比較中，NULL 被認為是不相等的。這就意味著，在多字段唯一約束的情況下，如果在至少一個字段上出現 NULL ，那么我們還是可以存儲同樣的這種數據行。這種行為遵循 SQL 標準，但是我們聽說其它 SQL 數據庫可能不遵循這個標準。因此如果你要開發可移植的程序，那么最好仔細些。 ## 5.3.4\. 主鍵從技術上講，主鍵約束只是唯一約束和非空約束的組合。所以，下面兩個表定義是等價的： ``` 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)** ); ``` 主鍵表示一個或多個字段的組合可以用于唯一標識表中的數據行。這是定義一個主鍵的直接結果。請注意：一個唯一約束實際上并不能提供一個唯一標識，因為它不排除 NULL 。這個功能對文檔目的和客戶應用都很有用。比如，一個可以修改行數值的 GUI 應用可能需要知道一個表的主鍵才能唯一地標識每一行。添加一個主鍵將會在主鍵使用的列或一組列中自動創建一個唯一btree索引。一個表最多可以有一個主鍵(但是它可以有多個唯一和非空約束)。關系型數據庫理論告訴我們，每個表都必須有一個主鍵。PostgreSQL并不強制這個規則，但我們最好還是遵循它。 ## 5.3.5\. 外鍵外鍵約束聲明一個字段(或者一組字段)的數值必須匹配另外一個表中出現的數值。我們把這個行為稱為兩個相關表之間的_參照完整性_。假設你有個產品表，我們可能使用了好幾次： ``` 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`，它們導致在被引用行刪除的時候，將引用它們的字段分別設置為 NULL 和缺省值。請注意這些選項并不能讓你逃脫被觀察和約束的境地。比如，如果一個動作聲明`SET DEFAULT`，但是缺省值并不能滿足外鍵約束，那么該動作就會失敗。與`ON DELETE`類似的還有`ON UPDATE`選項，它是在被引用字段修改(更新)的時候調用的，可用的動作是一樣的。在這種情況下，`CASCADE`意味著被引用字段的更新后的值，應該被拷貝到引用行中。通常地，如果一個引用行的任意引用字段為null，那么這個引用行不必滿足外鍵約束。如果外鍵聲明中添加了`MATCH FULL`，引用行只有在所有的引用字段都是null時，才能逃避滿足約束（所以null和non-null值的混合肯定不能滿足`MATCH FULL`約束）。如果你不想引用行能夠避免滿足外鍵約束，那么聲明引用行為`NOT NULL`。一個外鍵必須要么引用一個主鍵，要么引用一個唯一約束。這意味著被引用行總是有一個索引（一個基本的主鍵或唯一約束）；所以檢查一個引用行是否有一個匹配是高效的。因此從被引用表中`DELETE`一個行或者一個被引用字段`UPDATE`一列，都需要掃描一次引用表以便從行中匹配老的數值，給引用字段創建索引也是一個好主意。因為這個不是總是被需要，而且怎么去建立索引還有許多其他的選擇，外鍵約束的聲明不能在引用字段上自動生成一個索引。有關更新和刪除數據的更多信息可以在[Chapter 6](#calibre_link-1758)里找到。也可以查看關于外鍵約束語法的描述，在參考文檔的[CREATE TABLE](#calibre_link-7)。 ## 5.3.6\. 排除約束排他約束保證如果任何兩行被在聲明的字段里比較或者用聲明的操作表達，至少有一個操作比較會返回錯誤或空值。句法是： ``` CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) ); ``` 更多細節也請參考[`CREATE TABLE ... CONSTRAINT ... EXCLUDE`](#calibre_link-1064)。添加一個排除約束會在約束聲明里自動創建一個聲明類型的索引。