基于SQL Server數據庫完整性約束的案例教學研究

魏慧娟 李思 文小爽

摘要：數據庫完整性是DBMS中衡量數據庫存在狀態是否合理的重要技術指標。在SQL Server數據庫中，完整性約束如何定義和使用是數據庫教學環節中的重點和難點。該文結合案例教學法和項目化教學法，提出了將一個項目貫穿到實體完整性、域完整性、參照完整性、用戶自定義完整性的案例設計中，研究出完整性約束的教學方法，能夠幫助學生深入透徹地理解完整性約束，為提高學生的實踐動手能力和靈活運用數據庫技術的能力奠定了基礎。

關鍵詞：SQL Server數據庫;完整性約束;案例

中圖分類號：TP311? ? ? ? 文獻標識碼：A

文章編號：1009-3044（2021）09-0166-03

開放科學（資源服務）標識碼（OSID）：

數據庫技術是計算機專業的學生必須掌握的重要技術，而學生在學習數據庫技術時，通常覺得完整性約束部分的知識晦澀難懂，只能片面的掌握約束知識點的應用，不能有效地整合知識點的零碎案例，形成一個完整的知識體系。針對此現象，本文結合一個項目，在SQL Server數據庫的基礎上設計了一套完整性約束的教學案例。

1項目概述

對于項目的選擇，要貼合學生的現實生活，有助于學生理解。本文選擇基于Java的網上購物系統的設計與開發項目[1]，該項目主要分為需求分析、設計數據庫、實現完整的數據庫、設計應用程序界面、連接數據庫、應用程序對數據庫的查詢統計等、系統調試七個階段，本文案例主要選擇實現完整的數據庫這個階段，旨在培養學生能夠依據E-R圖創建出符合完整性約束的數據庫的職業能力。

項目的部分需求描述如下：每個用戶有唯一的ID，但是相同商品的商品ID是一樣的，每個帶有商品ID的商品都有庫存記錄在數據庫中，用戶和商品之間產生多對多的訂單關系。根據該描述可得到數據庫的概念模型（E-R模型），部分實體的E-R圖如圖1所示。

數據庫的概念模型和物理模型之間可以相互轉換，學生應該學會根據E-R圖繪制出系統的物理表結構，轉換的規則是一個實體對應一張物理表，實體和實體之間產生的關系要不要轉換成表分為3種情況：（1）1：1關系不用生成新的物理表，只需在關系的任一方實體轉換成的物理表中添加外鍵約束即可;（2）1：n關系也無須生成新的物理表，只需在關系n的一方實體轉換成的物理表中添加外鍵約束（關系1方的主鍵）;（3）m：n關系必須轉換成新的物理表，新的物理表由關系m方和關系n方的主屬性和新s的聯系屬性構成。依據此規則可描繪出該系統的主要關系模式，具體如下：

User（userid，username，password，sex，phone，address，points）

Goods（goodsid，goodsname，categoryid，price，stock，collection）

Order（orderid，userid[fk1]，goodsid[fk2]，quantity）

2數據庫完整性的概述

SQL Server數據庫完整性是指數據庫中數據的一致性、正確性和相容性，通過完整性約束實現，主要分為實體完整性、域完整性、參照完整性和用戶自定義完整性[2]。數據庫中所有數據的狀態及狀態間的轉換都受到約束的限制，如果不對數據進行完整性約束，會出現數據的更新異常、插入異常、刪除異常等不一致性的問題。合理地制定完整性約束是保證SQL Server數據庫安全的重要技術手段。在設計開發數據庫階段，如何設定完整性約束更為重要，本文將從實體、域、參照、用戶自定義完整性四個方面探討User、Goods、Order三張表完整性約束的案例設計。整體的教學過程大致如下：首先由教師提出問題引出案例，然后學生分組討論，再由教師演示案例的設計，接著由學生自己動手實踐，最后由教師進行點評。

3實體完整性

實體完整性主要對表中的每一行進行限制，每一行是一個元組，一個實體，需要確保每個實體都是唯一的，不能重復。可通過設置主鍵（PRIMARY KEY）約束、唯一（UNIQUE）約束、索引實現實體完整性。根據實體完整性的知識點聯系網上購物系統的項目將設計以下案例說明PRIMARY KEY和UNIQUE約束的用法。

1）主鍵約束

案例1：如何為Order表設置PRIMARY KEY約束？

分析：如何確定一張表的主鍵關鍵在于所選字段是否可以唯一標識一條記錄，設定為主鍵的字段可以是單個也可以是多個。而多個字段組合在一起設為一個主鍵，并不是說一張表存在多個主鍵。在Order表中，怎么唯一標識表中的一行記錄呢，一個userid可以標識一條訂單記錄嗎？很明顯單獨的一個userid或goodsid都不能標識一個訂單記錄，因為一個用戶可以擁有多個訂單，一個商品也可以被生成多個訂單。所以這里選擇userid、goodsid作為組合主鍵（訂單ID單獨作為主鍵不再重點介紹）。組合主鍵的概念很多初學者搞不清楚，經常誤以為是一張表有多個主鍵。為Order表設置組合主鍵的sql語句（不區分大小寫）如下：

Alter table Order add constraint pk_1 primary key（userid，goodsid）--增加PRIMARY KEY約束

2）唯一約束

案例2：怎樣使每個用戶的用戶名稱唯一？為User表設置唯一約束和主鍵約束有什么區別？

分析：UNIQUE約束用于限制列的值唯一，通過對表中列的限制實現實體完整性。SQL Server數據庫系統自動為建立UNIQUE約束的列創建唯一索引。要想每個用戶的用戶名稱唯一，只需為用戶名稱這一列增加UNIQUE約束。可是User表中的用戶ID作為主鍵是唯一的，用戶名稱也是唯一的，有什么區別？設置為主鍵的用戶ID列是唯一的，但不允許為NULL，而用戶名稱的列允許存在一個NULL值，這就是唯一約束和主鍵約束的區別。將User表的username字段增加唯一約束的語句如下：

Alter table User add constraint un_1 unique（username）--增加UNIQUE約束

4域完整性

域完整性主要通過限制表中的每一列實現列中所有數據取值的合法性，可通過建立檢查（CHECK）約束、默認值（DEFAULT）約束、非空（NOT NULL）約束、規則（RULE）和默認值對象（DEFAULT）實現域完整性[3]，此外也可通過數據類型限制列中數據的類型、長度、精度等。下面結合項目設計案例3-5講解域完整性的實現方法。

1）檢查約束和非空約束

案例3：為User表增加約束，該約束限制電話號碼列只能為11位數字，以數字1開頭，且該列的值不允許為空。

分析： CHECK約束可通過限制insert或update某一列或多列的值強制實現域完整性[4]。這里根據限制條件分析出使用的約束為CHECK約束和NOT NULL約束。根據限制電話號碼列只能為11位數字的條件得出CHECK表達式需要like和通配符“[]”連用：

Alter table User add constraint ck_1 check（phone like 1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]）--增加CHECK約束

Alter table User alter column phone varchar（20） not null--增加NOT NULL約束

2）默認值約束

案例4：如果在向User表增加用戶記錄時，沒有相關用戶的積分信息，則將積分字段的值默認為0，該如何設置約束？

分析：如果不向User表中積分列插入任何值，則積分列的值為NULL而不是0，學生特別容易混淆0和NULL。DEFAULT約束通過自動為沒有明確指定任何值的列添加默認值強制實現域完整性。如果積分列沒有指定具體的數值，則該列的值可通過增加DEFAULT約束將其自動設為0。

Alter table User add constraint df_1 default（0） for points--增加DEFAULT約束

3）規則和默認值對象

除了通過增加檢查、非空和默認值約束實現域完整性，也可通過建立規則和默認值對象實現域完整性，規則和默認值對象都是獨立存儲在數據庫中的對象。規則作用于表中的列或用戶自定義的數據類型上，系統會檢查用戶即將增加或更新的數據是否違反綁定在域上的規則[5]。默認值對象也一樣，所以都不會受到綁定的列或用戶自定義數據類型刪除的影響。

案例5：定義RULE，限制用戶購買某種商品的數量不能超過5個;定義DEFAULT對象，將沒有提供購買數量的銷售數量列的數值設置為1。

分析：RULE和DEFAULT對象因為是獨立存在的數據庫對象，都需先定義，再綁定到要限制的列或用戶自定義數據類型上。下面只給出定義、綁定和解綁規則，DEFAULT對象綁定解綁不再列舉。

Create rule rule_1 as @x between 0 and 5 --定義規則rule_1

Exec sp_bindrulerule_1，Order.quantity--綁定rule_1到列quantity

Exec sp_unbindruleOrder.quantity --利用存儲過程sp_unbindrule解綁rule_1

Create default value1 as 1 --定義默認值對象value1

5參照完整性

參照完整性的實現主要通過定義一個數據庫中不同表之間的關系，要求一張表（子表）的一列必須引用另一張表（父表）的一列（主鍵），實現此關系的列在子表中被定義為外鍵（FOREIGN KEY）。建立FOREIGN KEY約束是實現參照完整性的主要手段。

案例6：如何使Order表和User表、Goods表產生聯系，產生聯系后，向Order表中插入一條記錄，該記錄中的用戶ID在User表卻不存在，可以插入成功嗎？

分析：如果使Order表和User表建立關系，則應建立FOREIGN KEY約束，如何判斷約束要建在哪個表中？首先分清誰是父表，誰是子表，被引用的表稱為父表，父表中包含被引用列的全部信息，并且該列在父表中充當主鍵。Order表和User表只能通過用戶ID產生關系，而User表中包含用戶ID列的全部信息，且用戶ID為User表的主鍵，所以User表為被引用的表（即父表），在子表Order表中設置用戶ID為外鍵。同理，在子表Order表中設置商品ID列為外鍵參照Goods表（父表）的商品ID列。建立完FOREIGN KEY約束后，必須保證Order表用戶ID的取值和User表中用戶ID的取值相匹配，如果向Order表中插入不匹配的數據，就會出現插入異常的現象。在教學過程中，發現學生經常不理解外鍵約束的概念，分不清子父表，通過此案例已講解清楚如何區分子父表。

Alter table Order add constraint fk_1 foreign key（userid） references User（userid）--在Order表中設置userid為外鍵

Alter table Order add constraint fk_2 foreign key（goodsid） references Goods（goodsid） --在Order表中設置goodsid為外鍵

6用戶自定義完整性

用戶自定義完整性涵蓋范圍比較廣，實現域完整性的各種方法都支持用戶自定義完整性，如字段的數據類型，檢查約束，默認值約束，規則，存儲過程，觸發器等。這里簡單介紹下如何定義觸發器，實現用戶自定義完整性。觸發器是教學環節中的難點，觸發器的執行是在某些特定條件下自動觸發執行的，不需要調用。在觸發器的知識點上，學生首先要充分理解兩個臨時表inserted和deleted，才能熟練的創建觸發器。

案例7：用戶每生成一次訂單，商品的庫存量都會自動更新。

分析：用戶生成一次訂單說明Order表中增加了一條記錄，而商品的庫存量自動更新說明觸發器觸發條件是向Order表中insert數據，觸發器觸發后引起的操作是update Goods表的庫存量。所以要在Order表上創建after（與for同義）觸發器，生成訂單時，要插入的銷售數量@quantity存放在臨時表inserted中，Goods表的庫存量等于當前商品@goodsid的庫存量減去@quantity。

Create trigger tri_1

on Order for insert

as

declare @quantity int， @goodsid varchar（20）

select @quantity=quantity， @goodsid=goodsid from inserted

update Goods set stock=stock-@quantity where goodsid=@goodsid

7結束語

本文借助于一個項目設計案例介紹了保持數據庫完整性的各種實現方法，設計的案例引導學生逐步了解完整性約束的各個知識點，從而使其具備設計完整數據庫的職業能力。此外，精心選擇的各個案例能夠調動學生學習的積極性，進一步培養學生的實踐能力，幫助學生對完整性約束建立一個全面的知識體系。

參考文獻：

[1] 朱成.基于Java的網上購物系統的設計與開發[D].南昌：南昌航空大學，2019：22.

[2] 李熹.問題驅動與反例教學法相結合提高數據庫完整性的教學質量[J].廣西民族大學學報（自然科學版），2017，23（1）：104-108.

[3] 徐博龍.數據庫中域完整性的設計與應用[J].信息與電腦（理論版），2019（14）：152-154.

[4] 陳林琳，蔣麗麗，解二虎.SQL Server 2008數據庫設計教程[M].鎮江：江蘇大學出版社，2013.

[5] 陳瀟.面向SQL Server 2012的數據庫約束的設計與應用[J].軟件工程，2018，21（12）：12-14.

【通聯編輯：代影】