資料庫設計（3/9）：創建表

来源:http://www.cnblogs.com/woodytu/archive/2016/06/17/5495828.html
-Advertisement-
Play Games

對於設計和創建資料庫完全是個新手？沒關係，Joe Celko， 世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的資料庫老手，也會給他們帶來驚喜。Joe是DMBS雜誌是多年來最受 讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO ...


對於設計和創建資料庫完全是個新手？沒關係，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聲明。這些列用來作為表之間的其他約束，但現在不要擔心這個。

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

  1. 實體（Entity）
  2. 關係（Relationship）
  3. 輔助（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操作。

  1. NO ACTION：事務回滾，你收到提示。當有簡單的REFERENCES子句時的預設操作。
  2. SET DEFAULT：引用的列通過事件改變，但引用的列值會修改為它們的預設值。當然，引用的列在它們上面要有聲明的預設值。這些預設需要在引用表裡。
  3. SET NULL：引用的列通過事件改變，但引用的列值會修改為NULL。當然，引用的列允許NULL值。這是引入NULL值“無罪推定（benefit of the doubt）”的地方。
  4. 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));

看看下麵的情況發生時，你會找出會發生什麼？

  1. 一個客戶走了，我們刪除它。
  2. 我們修改Lawn Gnome雕像為更有品味的Pink Flamingo。
  3. 我們停止銷售Pink Flamingo。
  4. 在1-3步驟後，有人嘗試下Lawn Gnome訂單。

顯然，我留下未處理的問題和其他東西，但我們會接觸這些。

原文鏈接：

http://www.sqlservercentral.com/articles/Stairway+Series/69927/


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • android VelocityTracker 速度追蹤器的使用及創建
    VelocityTracker 速度追蹤 第一，創建方式： VelocityTracker mVelocityTracker = new VelocityTracker .obtain() 第二，把你要追求的MotionEvent 註冊到VelocityTracker 的監聽中： mVelocity ...
  • Mongoose Schemas定義中timestamps選項的妙用
    在Node.js中使用MongoDB少不了Mongoose。 假設有如下Mongoose Schemas的定義： 我們希望在保存model數據時不用指定createTime欄位的值，按照上述Schema的定義，createTime會自動保存為系統當前時間。當然，在更新model數據時updateTi ...
  • 資料庫練習題
    資料庫練習題，關係代數，SQL查詢語句 ...
  • Percona-Server-5.5.33-31.1安裝
    一、下載 http://www.percona.com/downloads/Percona-Server-5.5/ Percona-Server-5.5.33-31.1 選擇 binary 選擇linux 選擇x86_64 二、文檔： http://www.percona.com/doc/perco ...
  • 曲演雜壇--為什麼SELECT語句會被其他SELECT阻塞？
    很多剛入門的DBA在捕獲阻塞得時候，會問這麼一個問題“為什麼這個SELECT語句被那個SELECT語句阻塞了，難道不是共用鎖麽？” 讓我們來做個小測試，首先準備一些測試數據： 註意上面創建的表中RID是唯一聚集索引，因此如果我們按照RID來進行更新或查詢，會加行鎖。 首先開啟一個事務來修改數據： 然 ...
  • mysql查詢區分大小寫
    在查找的條件值加上的binary() 來源：http://my.oschina.net/xiangtao/blog/33983 ...
  • hadoop初次接觸----------虛擬機、SSH互信及hadoop配置
    部署環境： OS：Fedora 23 JDK：jdk-7u80-linux-x64 Hadoop：hadoop-2.7.2 VMWare：VMware Workstation 12 Pro 安裝JDK rpm -ivh jdk-7u80-linux-x64.rpm 配置環境變數 用vim編輯器打開/ ...
  • SQL SERVER 的模糊查詢 LIKE
    今天寫個動態腳本，需要把資料庫裡面包含“USER_"的表刪除掉，突然想不起來如何搜索通配字元了，趕緊查查MSDN，整理了下模糊查詢的知識點，留著以後查閱用。 LIKE模糊查詢的通配符 通配符 說明 示例 % 包含零個或多個字元的任意字元串。 WHERE title LIKE '%computer%' ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類