文章出處

對于設計和創建數據庫完全是個新手？沒關系，Joe Celko，世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的數據庫老手，也會給他們帶來驚喜。Joe是DMBS雜志是多年來最受讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO SQL標準委員會工作了10年，為SQL-89和SQL-92標準做出了杰出貢獻。

有很多表類型，每個都有它們特定規則和完整性約束的需求。不管什么需求，表層級的約束會確保那些規則被執行，數據完整性被保持。

在第一篇，我們為了它們是什么，更好的區分它們來命名數據元。在第二篇，我們用SQL里的數據類型和約束來模型化數據元，來提供我們行。在第三篇，我們將把這些行放入表。表是在一個名稱下，很多行的集合。

在表里，列只會出現一次。這樣做是有道理的；如果你兩次記錄某人的鞋子大小，這將是多余的，當列不一致時是混淆的。現在我們可以有表層級的在每行的列里的檢查（CHECK）約束。這和之前列上的（CHECK）檢查并沒有啥區別。它們可以在CREATE TABLE語句里，多個列聲明里命名并出現，不附加到任何行。例如：

1 CONSTRAINT Valid_Employee_Age-- don't hire people before they are born
2  CHECK (emp_birth_date < emp_hire_date)

通常不應該把檢查組合成一個大的CHECK()子句。錯誤信息會包含約束名稱，因此獨立的約束會，相比單個復雜命名的約束，給讓你更清楚的發現問題。

繼續我們的冗余問題，在表層級我們想每個行因同個原因而唯一。這可以通過約束實現。兩個表層級的約束是UNIQUE和PRIMARY KEY，它們可以是單列或多列組合。

UNIQUE約束表示在表里，列或列的組合是唯一的。但在列或多個列中有NULL，如果它是唯一值，我們還是允許的。PRIMARY KEY聲明，與對于表里面的所有列，NOT NULL且UNIQUE有同樣的效果。但由于歷史原因，表只能有一個PRIMARY KEY聲明。這些列用來作為表之間的其他約束，但現在不要擔心這個。

唯一性約束如何使用取決于涉及的表類型。一般來說，我們可以把表分為三類：

實體（Entity）
關系（Relationship）
輔助（Auxiliary）

實體表是多個同類事物，通過列的模型屬性定義。每一行是這類東西的實例。每行有同樣的列。如果你可以看到它感覺，看到它或感受它，那它是一個實體。實體表的命名不應該是單數（除非這個集合里真的只有一個成員），因為它模型化了一組。命名應該是復數，可能的話，使用集合命名。例如，“Employee”不好，“Employees”更好，“Personnel”最好。“Tree”不好，“Trees”更好，“Forest”最好。你可以添加你自己的例子。

實體也區分弱和強。強實體存在有它自己的優點，同時，弱實體存在因為一個或多個強實體。你需要購買前，你可以有個折扣。

關系表指的是一個或多個實體表，并且它們之間建立關系。關系可以有它自己委外引用實體的屬性。結婚登記號屬于婚姻，不屬于丈夫，妻子或牧師。

關系級別是關系里實體的個數。二元關系有2個實體，在現實世界中我們喜歡它們，因為它們簡單。二元迭代關系關聯到實體本身。一般的n元關系涉及n個實體，就像有買家，賣家和銀行的房貸。通常不能把n元關系分解為二元關系。成員的關系可以是可選或必須的。可選的關系表示我們可以有一類的0實體——并不是所有的買賣都有折扣。

關系基數是對于每2個實體，相關出現的實際數量。關系的基本連接類型有：1：1，1：n，和n：n。這些術語通常是符合可選（0或更多）或必須的（1或更多）的關系。

1:1關系是一個實體A的最多一個實例與實體B的一個實例關聯的時候。例如，拿通常的丈夫和妻子的關系。每個丈夫有且只要一個妻子；每個妻子有且只有一個丈夫。在這個例子都是必須一個的。

1:n關系是實體A的一個實例，對于實體B的一個實例有0個，一個或多個實體B的實例，實體A的實例只有一個的時候。一個例子會是一個部門有很多員工；每個員工分配到一個部門。取決于你的業務規則，你會允許未分配部門的員工或空的部門。

n:n關系，有時稱作非特定的，對于實體A的一個實例，有0個，一個或多個實體B的實例，并且對于實體B的一個實例，有0個，一個或多個實體A的實例。這樣的例子可以是披薩和客戶。

輔助表不是實體也不是關系；它提供信息。它們是像日歷或在SQL里替換計算的查詢表（look up tables）。它們經常被誤解被當實體或關系表對待。

我們來具體說下。銷售訂單是客戶（實體）和我們的庫存（實體）之間的關系。訂單明細是存在的弱實體，因為我們有訂單。這個關系有一個不是庫存或客戶一部分的訂單號。運費從輔助表獲得。對于這個例子，這里我用了一些骨架表。對于訂單項目，我使用GTIN（Global Trade Item Number），對于客戶，我使用GUNS（Data Universal Numbering System）。在你設計數據庫的時候，記得都先看看行業標準。

 1 CREATE TABLE Sales_Orders
 2 
 3 (order_nbr INTEGER NOT NULL PRIMARY KEY
 4 
 5  CHECK (order_nbr > 0),
 6 
 7  customer_duns CHAR(9) NOT NULL,
 8 
 9  order_shipping_amt DECIMAL (5,2) NOT NULL
10 
11  CHECK (shipping_amt >= 0.00),
12 
13  etc);
14 
15 CREATE TABLE Sales_Order_Details
16 
17 (order_nbr INTEGER NOT NULL,
18 
19  gtin CHAR(15) NOT NULL,
20 
21  PRIMARY KEY (order_nbr, gtin),
22 
23  item_qty INTEGER NOT NULL
24 
25  CHECK (item_qty > 0),
26 
27  item_unit_price DECIMAL (8,2) NOT NULL
28 
29  CHECK (item_unit_price >=0.00));
30 
31 CREATE TABLE Customers
32 
33 (customer_duns CHAR(9) NOT NULL PRIMARY KEY
34 
35  CHECK (customer_duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
36 
37  etc);
38 
39 CREATE TABLE Inventory
40 
41 (gtin CHAR(15) NOT NULL PRIMARY KEY
42 
43  CHECK (gtin LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
44 
45  onhand_qty INTEGER NOT NULL
46 
47  CHECK (onhand_qty >= 0),

我們可以看到訂單表是客戶和庫存間的關系。訂單有它們自己的主鍵（order_nbr），但沒有東西強制我們使用有效的客戶DUNS號或對于我們庫存里的產品GTIN號。事實上，我可以插入顯然無效的DUNS和GTIN碼到訂單表，現在就這樣聲明。

這就是我們要引入REFERENCES子句的地方。它是讓我們從數據模型強制所有基數和程度的東西。引用（reference）不是個鏈接或指針。這些是物理概念，引用是個邏輯概念，我們不知道它如何實現。它強制的是，在引用表里，引用表列符合單行的規則。這意味著在引用表里的行必須唯一；默認情況下，在引用表里可以使用主鍵（PRIMARY KEY），但不必這樣。在引用表的值可以稱為外鍵（Foreign Keys）——它們不在它們的表里，但在架構里的其它地方。

這是上面有更多信息的主要架構：

 1 CREATE TABLE Sales_Orders
 2 (order_nbr INTEGER NOT NULL PRIMARY KEY
 3  CHECK (order_nbr > 0),
 4  customer_duns CHAR(9) NOT NULL
 5  REFERENCES Customers(customer_duns),
 6  order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 NOT NULL
 7  CHECK (shipping_amt >= 0.00),
 8  etc);
 9 
10 CREATE TABLE Sales_Order_Details
11 (order_nbr INTEGER NOT NULL
12  REFERENCES Orders(order_nbr),
13  gtin CHAR(15) NOT NULL
14  REFERENCES Inventory(gtin),
15  PRIMARY KEY (order_nbr, gtin),-- two column key
16  item_qty INTEGER NOT NULL
17  CHECK (item_qty > 0),
18  item_unit_price DECIMAL (8,2) NOT NULL
19  CHECK (item_unit_price >= 0.00));
20 
21 CREATE TABLE Customers
22 (customer_duns CHAR(9) NOT NULL PRIMARY KEY
23  CHECK (customer_duns LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
24  etc);
25 
26 CREATE TABLE Inventory
27 (gtin CHAR(15) NOT NULL PRIMARY KEY
28  CHECK (gtin LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
29  onhand_qty INTEGER NOT NULL
30  CHECK (onhand_qty >= 0),
31  etc);

注意，在DUNS和GTIN是主鍵的地方，我們只有CHECK（）約束，不是在它們出現的引用表里。實體表，客戶和庫存是引用的；關系表，訂單，引用了其它表。這是常用模式，但這個設置不是固定的。

這個子句的多列看起來像這樣：

1 FOREIGN KEY (order_nbr, gtin)
2 REFERENCES Sales_Order_Details(order_nbr, gtin)

在FOREIGN KEY子句的列是引用表里需要匹配的引用主鍵，列對列，但會有不同的名稱。我可以通過在相應的地方放置唯一約束來設置1:1,1:n和n:n的關系。作為輔助表的一個例子，我們可以基于訂單總額計算運費。表看起來像這樣：

1 CREATE TABLE Shipping_Costs
2 (start_order_amt_tot DECIMAL (10,2) NOT NULL,
3  end_order_amt_tot DECIMAL (10,2) NOT NULL,
4 CONSTRAINT Valid_Shipping_Range
5  CHECK (start_order_amt_tot < end_order_amt_tot),
6 PRIMARY KEY (start_order_amt_tot, end_order_amt_tot),
7  shipping_amt DECIMAL (5,2) NOT NULL
8  CHECK (shipping_amt > 0.00));

當我們在輔助運費表上聲明了主鍵（PRIMARY KEY），對于實體，它不想主鍵——沒有驗證或核查，它不是個標識。使用這個表，我們可以這樣查詢：

1 SELECT shipping_amt
2   FROM Shipping_Costs
3  WHERE <order amount total> BETWEEN start_order_amt_tot AND end_order_amt_tot;

作為練習，嘗試寫下會從重復和斷層上阻止開始和結束范圍的約束。如果你需要的話，可以重新設計表。

在修正后的主要架構里，當你下沒有庫存的訂單，你會收到錯誤提示“沒有庫存！”，這樣的話，你可以試下別的。但如果你嘗試從庫存里刪除產品，你同樣也會收到錯誤提示“額，有人已經下了此產品的訂單”，因此在可以從庫存里刪它之前，你必須到每個訂單用別的值或NULL值來替換它。

這里就是引用完整性（Declarative Referential Integrity (DRI)）用的地方。語法是：

1 ON DELETE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]
2 ON UPDATE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]

刪除和更新是所謂的“數據基礎事件（data base events）”；當它們發生到表時，就會發生DRI操作。

NO ACTION：事務回滾，你收到提示。當有簡單的REFERENCES子句時的默認操作。
SET DEFAULT：引用的列通過事件改變，但引用的列值會修改為它們的默認值。當然，引用的列在它們上面要有聲明的默認值。這些默認需要在引用表里。
SET NULL：引用的列通過事件改變，但引用的列值會修改為NULL。當然，引用的列允許NULL值。這是引入NULL值“無罪推定（benefit of the doubt）”的地方。
CASCADE：引用的列通過事件改變，這些值會級聯到引用的列。實際中這是最重要的選項。例如，如果我們想停止一個產品，我們可以從庫存里刪除它，ON DELETE CASCADE會讓SQL引擎會在Sales_Order_Details自動刪除匹配的行。同樣，如果在庫存里更新一個項目，ON UPDATE CASCADE會用新值自動替換引用的列。

在這些操作完成后，引用完整性約束還是有效的。這是最終的架構：

 1 CREATE TABLE Sales_Orders
 2 (order_nbr INTEGER NOT NULL PRIMARY KEY
 3  CHECK (order_nbr > 0),
 4  customer_duns CHAR(9) NOT NULL
 5  REFERENCES Customers(customer_duns)
 6  ON UPDATE CASCADE
 7  ON DELETE CASCADE,
 8  order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 NOT NULL
 9  CHECK (shipping_amt >= 0.00),
10  etc);
11 
12 CREATE TABLE Sales_Order_Details
13 (order_nbr INTEGER NOT NULL
14  REFERENCES Orders(order_nbr)
15  ON UPDATE CASCADE
16  ON DELETE CASCADE,
17  gtin CHAR(15) NOT NULL
18  REFERENCES Inventory(gtin)
19  ON UPDATE CASCADE
20  ON DELETE CASCADE,
21  PRIMARY KEY (order_nbr, gtin),-- two column key
22  item_qty INTEGER NOT NULL
23  CHECK (item_qty > 0),
24  item_unit_price DECIMAL (8,2) NOT NULL
25  CHECK (item_unit_price >= 0.00));