資料庫定義-約束

 約束

 

資料類型是約束我們可以在表裡儲存什麼類型的資料的一種方法。 不過，對於許多應用，它們提供的約束實在是太粗糙。比如， 一個包含產品價格的欄位可能應該只接受正數。但是沒有哪種標準資料類型只接受正數。 另外一個問題是你可能需要根據其他欄位或者行的資料來限制欄位資料。比如，在一個包含產品資訊的表中， 每個產品編號都應該只有一行。

對於這些問題，SQL允許你在欄位和表上定義約束。 約束給予你所需要對資料施加的一切控制。如果一個使用者企圖在一個欄位裡儲存會違反約束的資料，那麼就會拋出一個錯誤。 這種情況同時也適用於數值來自預設值的情況。

 

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));

我們還要知道一個檢查約束在運算式計算出真或者空值的時候是滿足條件的。 因為大多數運算式在任意運算元是空的時候都會得出空值， 所以這些約束不能在受約數欄位上禁止空值。要確保一個欄位不包含空值，我們可以使用下一節介紹的非空約束。

檢查約束還可以用於提高表分區的效能。

 

2. 非空約束

 

非空約束只是簡單地聲明一個欄位必須不能是空值。下面是一個文法例子：

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 關鍵字。

 

提示: 在大多數資料庫設計裡，主要的欄位都應該標記為非空。

 

 

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);

通常，如果在表中有兩行或更多行，而這些行中包含在唯一約束裡面的那幾個欄位都相等，那麼就算違反了唯一約束。 但是在這種比較中，空值是認為不相等的。這就意味著，在多欄位唯一約束的情況下， 如果在至少一個欄位上存在空值，那麼我們還是可以儲存同樣的這種資料行。 這種行為遵循 SQL 標準，但是我們聽說其它 SQL 資料庫可能不遵循這個標準。因此如果你要開發可移植的程式， 那麼最好仔細些。

 

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));

主鍵表示一個欄位或者是若干個欄位的組合可以用於表中的資料行的唯一標識。 （這是定義一個主鍵的直接結果。請注意一個唯一約束實際上並不能提供一個唯一表示，因為它不排除空值。） 這個功能對文檔目的和客戶應用都很有用。比如，一個可以修改行數值的 GUI 應用可能需要知道一個表的主鍵才能唯一地標識一個行。

一個表最多可以有一個主鍵（但是它可以有多個唯一和非空約束）。 關係型資料庫理論告訴我們，每個表都必須有一個主鍵。PostgreSQL 並不強制這個規則，但我們最好還是遵循它。

 

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。 這樣會導致在被引用行刪除的時候，引用它們的欄位分別設定為空白或者預設值。 請注意這些選項並不能讓你逃脫被觀察和約束的境地。 比如，如果一個動作聲明 SET DEFAULT，但是預設值並不能滿足外鍵，那麼動作就會失敗。

類似 ON DELETE，還有 ON UPDATE 選項，它是在被引用欄位修改（更新）的時候調用的。可用的動作是一樣的。