第 7 章 建立資料表與完整性限制條件

1. 第7章 建立資料表與完整性限制條件 • 7-1 資料型別 • 7-2 資料表的建立 • 7-3 建立完整性限制條件 • 7-4 修改與刪除資料表 • 7-5 建立SQL Server資料庫圖表 • 7-6 暫存資料表的建立

2. 7-1 資料型別 • 7-1-1 數字資料型別 • 7-1-2 日期資料型別 • 7-1-3 字元與位元串流資料型別 • 7-1-4 其他資料型別

3. 7-1-1 數字資料型別-位元資料型別 • SQL Server數字資料型別是用來儲存數值資料，包含整數和小數。 • 位元資料型別bit的值可以是0、1或NULL，一個bit資料型別的欄位大小是1位元（Bits），但它會佔用整個位元組（Bytes），除非資料表擁有數個bit資料型別的欄位，才會使用同一個位元組的1~8位元，若超過8個欄位，就使用第2個位元組的9~16位元來儲存。 • 因為bit資料型別的值可以是1或0，所以特別適合使用在開/關、真/假和true/false等布林資料的欄位，即1為true；0為false。

4. 7-1-1 數字資料型別-整數資料型別 • 整數資料型別可以儲存整數但沒有小數的數值資料，例如：1、-23、589和8888等正負整數。SQL Server提供數種整數資料型別來儲存不同範圍的整數資料，請依照欄位可能整數值的範圍來決定使用哪一種整數資料型別，如下表所示：

5. 7-1-1 數字資料型別-精確小數資料型別 • 精確小數資料型別可以儲存包含小數的數值資料，而且可以完全保留指定的精確度（Precision）。SQL Server提供兩種精確小數資料型別，如下表所示：

6. 7-1-1 數字資料型別-浮點數資料型別 • 浮點數資料型別是遵循IEEE（Institute of Electrical and Electronic Engineers）的資料型別，一樣可以用來儲存擁有小數點的數值資料。此型別也稱為不精確小數資料型別，因為當數值非常大或非常小時，其儲存的資料是一個近似值，例如：1/3的結果就是一個近似值。 • SQL Server提供的兩種浮點數資料型別，如下表所示：

7. 7-1-1 數字資料型別-貨幣資料型別 • 貨幣資料型別並不是ANSI-SQL 92規格的資料型別，SQL Server提供此型別來儲存貨幣資料，以符合實際貨幣輸入格式的千元符號「,」，例如：2,5000元，如下表所示：

8. 7-1-2 日期資料型別 • 日期資料型別儲存日期與時間資料，如下表：

9. 7-1-3 字元與位元串流資料型別-說明 • 在電腦系統讀寫的位元串流（Byte Stream）就是一序列的位元組資料。SQL Server可以將位元組資料解碼成字元、數字或符號，即字串和統一字碼字串資料型別的資料。如果不作任何解碼就是二進位字串資料型別。 • 統一字碼（Unicode）是由Unicode Consortium組織所制定的一個能包括全世界文字的字碼集，它包含GB2312和Big5字碼集的所有字集，即ISO 10646字集。

10. 7-1-3 字元與位元串流資料型別-字串資料型別 • 字串資料型別是用來儲存字串資料，例如：'陳會安'、'This is a book.'和'Joe Chen'等。SQL Server提供四種字串資料型別，可以用來儲存固定長度或變動長度的字串，如下表所示：

11. 7-1-3 字元與位元串流資料型別-統一字碼字串資料型別 • 統一字碼字串資料型別是用來儲存使用統一字碼為字碼集的字串資料，SQL Server統一字碼字串的資料需要指明「N」開頭的字串，如下所示： N'This is a book.' N'Joe Chen'

12. 7-1-3 字元與位元串流資料型別-二進位字串資料型別 • 二進位字串資料型別是用來儲存二進位字串（Binary String）資料，也就是未經解碼的位元串流，可以用來儲存二進位資料的圖檔、Word文件或Excel試算表等，如下表所示：

13. 7-1-4 其他資料型別-說明 • 在SQL Server除了前述常用的數值、貨幣、日期與時間和字串資料型別外，還提供一些特殊用途的資料型別。 • 一般來說，除非資料庫應用程式有特殊的需求，通常並不會使用到這些資料型別。

14. 7-1-4 其他資料型別-標記資料型別 • 標記資料型別可以用來建立記錄資料戳記或識別碼，這些都是資料庫或全域的唯一值。SQL Server提供兩種標記資料型別，如下表所示：

15. 7-1-4 其他資料型別-xml資料型別 • 資料型別xml是SQL Server 2005新增的資料型別，可以在資料表儲存整份XML文件或XML片斷內容，並且支援XML索引來加速XML資料存取。 • 基本上，在資料表建立的xml欄位可以分為兩種：強制型態的XML欄位（Typed XML Columns）需要使用XML Schema進行驗證，否則只能建立非強制型態的XML欄位（Un-typed XML Columns），進一步說明請參閱＜第20章：SQL Server與XML＞。

16. 7-1-4 其他資料型別-sql_variant資料型別 • sql_variant資料型別建立的資料表欄位可以儲存text、ntext、image、timestamp、sql_variant、varchar(max)、nvarchar(max)和varbinary(max)資料型別之外所有資料型別的資料，視資料型別的不同，可以儲存最大8016位元的資料。 • 資料型別sql_variant如同Visual Basic語言的variant資料型別，可以建立儲存各種資料的變數，當我們不確定欄位的資料型別時，就可以使用sql_variant資料型別來儲存數值、日期或字串資料。

17. 7-1-4 其他資料型別-階層資料型別 • 在SQL Server 2008版新增hierarchyid資料型別，可以在SQL Server資料庫儲存階層資料（Hierarchical Data），這是一種可變長度的系統資料型別，可以使用hierarchyid欄位來代表記錄在階層中的位置。 • 不過，hierarchyid資料型別的欄位並不會自動產生樹狀目錄的階層架構，應用程式需要自行產生且指定hierarchyid欄位值，以便建立記錄間的關聯性來建立階層資料。因為此型別的使用已經超過本書範圍，其進一步說明請參閱SQL Server線上叢書。

18. 7-1-4 其他資料型別-空間資料型別 • SQL Server 2008版新增geometry和geography兩種空間資料型別，geometry資料型別支援儲存平面地球（Flat Earth）的地理資料，符合開放式地理空間協會（Open Geospatial Consortium，OGC）的規格。 • 資料型別geography可以儲存球形地球（Round Earth）資料，即地理座標的經緯度，例如：儲存GPS的經緯度座標。關於空間資料型別的使用可能需要整本書來說明，所以本書並沒有討論這兩種資料型別。

19. 7-1-4 其他資料型別-T-SQL變數的資料型別 • SQL Server提供一些專為T-SQL變數宣告使用的資料型別，這些資料型別並不能用來定義資料表的欄位，如下表所示：

20. 7-2 資料表的建立 • 7-2-1 使用Management Studio建立資料表 • 7-2-2 使用T-SQL指令建立資料表 • 7-2-3 建立計算欄位 • 7-2-4 疏鬆欄位的使用

21. 7-2-1 使用Management Studio建立資料表 • Management Studio提供圖形編輯畫面來建立資料表的定義資料，例如：在執行Ch7-2.sql建立的【教務系統】資料庫上，建立名為【學生】資料表，如下圖所示：

22. 7-2-2 使用T-SQL指令建立資料表-語法 • T-SQL語言是使用CREATE TABLE指令在目前選擇的資料庫建立資料表，其基本語法如下所示： CREATE TABLE 資料表名稱 ( 欄位名稱1 資料型別 [ 欄位屬性清單 ], 欄位名稱2 資料型別 [ 欄位屬性清單 ], ………. 欄位名稱n 資料型別 [ 欄位屬性清單 ] [ 資料表屬性清單 ] ) [ ON 檔案群組名稱 ] [ TEXTIMAGE_ON 檔案群組名稱 ]

23. 7-2-2 使用T-SQL指令建立資料表-範例1 SQL指令碼檔：Ch7-2-2-01.sql • 在【教務系統】資料庫新增【員工】資料表，因為沒有ON子句，所以是建立在預設檔案群組，如下所示： CREATE TABLE 員工 ( 身份證字號 char(10) NOT NULL PRIMARY KEY, 姓名 varchar(12) NOT NULL, 城市 varchar(5) DEFAULT '台北', 街道 varchar(30), 電話 char(12), 薪水 money, 保險 money, 扣稅 money )

24. 7-2-2 使用T-SQL指令建立資料表-範例2 SQL指令碼檔：Ch7-2-2-02.sql • 在【教務系統】資料庫新增【課程】資料表，這是建立在名為【教務系統_群組】檔案群組的資料表，如下所示： CREATE TABLE 課程 ( 課程編號 char(5) NOT NULL PRIMARY KEY , 名稱 varchar(30) NOT NULL , 學分 int DEFAULT 3 ) ON 教務系統_群組

25. 7-2-2 使用T-SQL指令建立資料表-範例3 SQL指令碼檔：Ch7-2-2-03.sql • 在【教務系統】資料庫新增【教授】資料表，且新增自動編號的【建檔編號】欄位，如下所示： CREATE TABLE 教授 ( 建檔編號 int IDENTITY(1000, 1), 教授編號 char(4) NOT NULL PRIMARY KEY, 職稱 varchar(10), 科系 varchar(5), 身份證字號 char(10) NOT NULL )

26. 7-2-3 建立計算欄位-說明 • 計算欄位（Computed Columns）是一種沒有儲存值的資料表欄位，它的值是使用同一筆記錄其他欄位建立的運算式計算而得。因為欄位並沒有真正儲存資料，其欄位值是從其他欄位計算出，所以計算欄位就是一種虛擬欄位。 • 請注意！計算欄位因為並沒有存入值，所以並不能指定DEFAULT、NOT NULL、NULL等欄位屬性和條件約束。如果計算欄位值是唯一值且不會更動，我們仍然可以指定成PRIMARY KEY和UNIQUE欄位，不過很少會如此設定。

27. 7-2-3 建立計算欄位-建立方式 • 在Management Studio新增計算欄位就是在編輯畫面下方指定【計算資料行規格/公式】的欄位屬性。 • T-SQL指令是使用AS關鍵字來指定計算欄位的運算式。

28. 7-2-3 建立計算欄位-範例 SQL指令碼檔：Ch7-2-3.sql • 在【教務系統】資料庫新增【估價單】資料表，最後的【平均單價】欄位是一個計算欄位，其運算式為【總價 / 數量】，如下所示： CREATE TABLE 估價單 ( 估價單編號 int NOT NULL IDENTITY PRIMARY KEY, 產品編號 char(4) NOT NULL, 總價 decimal(5, 1) NOT NULL, 數量 int NOT NULL DEFAULT 1, 平均單價 AS 總價 / 數量 )

29. 7-2-4 疏鬆欄位的使用-說明 • 疏鬆欄位（Sparse Columns）是指在資料表中此欄位資料大部分是NULL值，換句話說，整個資料表只有少部分記錄的欄位擁有值，因為NULL值根本不需佔用儲存空間，換句話說，疏鬆欄位只有非NULL值才會佔用儲存空間。

30. 7-2-4 疏鬆欄位的使用-注意事項 • 疏鬆欄位在資料表設計上的注意事項，如下所示： • 疏鬆欄位必須大部分記錄的欄位值為NULL值。 • 疏鬆欄位不能指定DEFAULT或新增任何規則，也不能使用IDENTITY和ROWGUIDCOL屬性。 • 疏鬆欄位可以使用在任何資料型別的欄位，除了geography、geometry、text、ntext、image、timestamp和使用者自訂型別等資料型別。 • 疏鬆欄位不能作為主鍵欄位，也不可以用來建立叢集索引。

31. 7-2-4 疏鬆欄位的使用-建立方式 • 在Management Studio新增疏鬆欄位就是在編輯畫面下方的【為疏鬆】欄位，將屬性值改為【是】。 • T-SQL指令是使用SPARSE關鍵字來指定欄位為疏鬆欄位。

32. 7-2-4 疏鬆欄位的使用-範例 SQL指令碼檔：Ch7-2-4.sql • 在【教務系統】資料庫新增【廠商】資料表，最後的【分公司數】欄位是一個疏鬆欄位，因為大部分廠商都沒有分公司，如下所示： CREATE TABLE 廠商 ( 廠商編號 int NOT NULL IDENTITY PRIMARY KEY, 廠商名稱 varchar(100), 分公司數 int SPARSE )

33. 練習題 • 寫出圖中學生關聯表的T-SQL，其中學號是主鍵，以自動編號方式，起始值9800001，遞增值1

34. 7-3 建立完整性限制條件 • 7-3-1 條件約束的基礎 • 7-3-2 建立PRIMARY KEY條件約束 • 7-3-3 建立CHECK條件約束 • 7-3-4 建立資料表的關聯性

35. 7-3-1 條件約束的基礎-說明 • 條件約束（Constraints）可以定義欄位的檢查規則，檢查輸入資料是否允許存入資料表欄位。換句話說，它就是在建立資料庫的完整性限制條件，來維護資料的完整性。 • SQL Server的條件約束可以分為針對單一欄位值的「欄位層級條件約束」（Column-level Constraints）和多個欄位值的「資料表層級條件約束」（Table-level Constraints）。

36. 7-3-1 條件約束的基礎-種類

37. 7-3-2 建立PRIMARY KEY條件約束-說明 • PRIMARY KEY條件約束就是指定資料表的主鍵，或稱為主索引鍵。

38. 7-3-2 建立PRIMARY KEY條件約束-使用Management Studio • 在第7-2節已經說明如何建立欄位層級的PRIMARY KEY條件約束。 • 如果主鍵是多個欄位的複合鍵，在Management Studio選取欄位時，請使用Ctrl鍵配合選取多個欄位後，再設定主索引鍵。

39. 7-3-2 建立PRIMARY KEY條件約束-使用T-SQL(語法) • 在T-SQL的CREATE TABLE指令可以指定資料表層級的PRIMARY KEY條件約束，其語法如下所示： [ CONSTRAINT 條件約束名稱 ] PRIMARY KEY (欄位清單) • 上述語法建立名為【條件約束名稱】的條件約束，如果沒有指定名稱，SQL Server會自動產生條件約束名稱，在括號內的欄位清單如果為多欄位的複合鍵，請使用逗號分隔欄位名稱。

40. 7-3-2 建立PRIMARY KEY條件約束-使用T-SQL(範例) SQL指令碼檔：Ch7-3-2.sql • 在【教務系統】資料庫新增【訂單明細】資料表，其主鍵是【訂單編號】和【項目序號】欄位的複合鍵，如下所示： CREATE TABLE 訂單明細 ( 訂單編號 int NOT NULL, 項目序號 smallint NOT NULL, 數量 int DEFAULT 1, PRIMARY KEY (訂單編號, 項目序號) )

41. 7-3-3 建立CHECK條件約束-說明 • CHECK條件約束能夠限制欄位值是否在指定的範圍，其內容是條件運算式，運算結果如為true，就允許存入欄位資料，否則並不允許存入。 • 例如：在【員工】資料表的【扣稅】欄位值，一定小於【薪水】欄位值，即【薪水 > 扣稅】條件運算式。

42. 7-3-3 建立CHECK條件約束-使用Management Studio • 在Management Studio工具建立CHECK條件約束（正確的說是修改資料表新增CHECK條件約束）。

43. 7-3-3 建立CHECK條件約束-使用T-SQL(語法) • 在T-SQL的CREATE TABLE指令也可以建立CHECK條件約束，欄位層級是位在欄位屬性清單，其建立的約束條件只對此欄位有效；至於資料表層級是位在資料表屬性清單，其條件對整個資料表都有效。其基本語法如下所示： [ CONSTRAINT 條件約束名稱 ] CHECK (條件運算式) • 上述語法在CHECK條件約束的括號內是條件運算式，如果沒有指定條件約束名稱，SQL Server會自動新增其名稱。

44. 7-3-3 建立CHECK條件約束-使用T-SQL(範例1) SQL指令碼檔：Ch7-3-3-01.sql • 在【教務系統】資料庫新增【訂單】資料表，並且在【訂單總價】和【付款總額】欄位建立欄位層級的CHECK條件約束，如下所示： CREATE TABLE 訂單 ( 訂單編號 int NOT NULL IDENTITY PRIMARY KEY, 訂單總價 money NOT NULL CONSTRAINT 訂單總價_條件約束 CHECK (訂單總價 > 0), 付款總額 money DEFAULT 0 CHECK (付款總額 > 0) )

45. 7-3-3 建立CHECK條件約束-使用T-SQL(範例2) SQL指令碼檔：Ch7-3-3-02.sql • 在【教務系統】資料庫新增【我的訂單】資料表，並且建立資料表層級的CHECK條件約束，如下所示： CREATE TABLE 我的訂單 ( 訂單編號 int NOT NULL IDENTITY PRIMARY KEY, 訂單總價 money NOT NULL, 付款總額 money DEFAULT 0, CHECK ( (訂單總價 > 0) AND (付款總額 > 0) AND (訂單總價 > 付款總額)) )

46. 練習 check條件 • 1.欄位層級的條件約束:在【教務系統】資料庫，建立[學生]資料表，各欄位如下(不用索引)，其中[學號]欄位要建立名為[學年]的條件約束名稱，條件: 學號>9800001 • 2.資料表層級的條件約束:在【教務系統】資料庫，建立[結果]資料表，各欄位如圖，其中[成績]欄位的條件: 成績>=60 • (兩種寫法分開寫在同一個檔案就可以)

47. 7-3-4 建立資料表的關聯性-說明 • 資料表的關聯性（Relationships）是二個或多個資料表間所擁有的關係。 • 在資料表間建立關聯性（Relationships）的目的是建立參考完整性（Referential Integrity），它是資料表與資料表間的完整性限制條件。

48. 7-3-4 建立資料表的關聯性-種類 • 一對一的關聯性（1:1）：指一個資料表記錄只關聯到另一個資料表記錄，這是指資料表一筆記錄的欄位值可以被其他資料表一筆記錄的欄位值所參考。 • 一對多的關聯性（1:N）：指一個資料表記錄關聯到多個資料表記錄，這是指資料表一筆記錄的欄位值可以被其他資料表多筆記錄的欄位值所參考。 • 多對多的關聯性（M:N）：指多個資料表記錄關聯到多個資料表記錄，這是指資料表多筆記錄的欄位值可以被其他資料表多筆記錄的欄位值所參考。

49. 7-3-4 建立資料表的關聯性-使用Management Studio • 在Management Studio建立關聯性是在資料表新增外部索引鍵，例如：在【教務系統】資料庫的【教授】資料表擁有參考【員工】資料表主鍵【身份證字號】外來鍵的一對一關聯性，如下圖所示：

50. 7-3-4 建立資料表的關聯性-使用T-SQL(語法) • 使用T-SQL指令建立關聯性就是新增FOREIGN KEY條件約束，在CREATE TABLE指令的條件約束語法中，欄位層級是位在欄位屬性清單；資料表層級位在資料表屬性清單，其基本語法如下所示： [CONSTRAINT 條件約束名稱] [ [FOREIGN KEY (欄位清單) ] REFERENCES 參考資料表名稱 (欄位清單) [ON DELETE { CASCADE | NO ACTION }] [ON UPDATE { CASCADE | NO ACTION }] ]