基于SQL Server數(shù)據(jù)庫完整性約束的案例教學(xué)研究

魏慧娟 李思 文小爽

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

關(guān)鍵詞：SQL Server數(shù)據(jù)庫;完整性約束;案例

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

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

開放科學(xué)（資源服務(wù)）標識碼（OSID）：

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

1項目概述

對于項目的選擇，要貼合學(xué)生的現(xiàn)實生活，有助于學(xué)生理解。本文選擇基于Java的網(wǎng)上購物系統(tǒng)的設(shè)計與開發(fā)項目[1]，該項目主要分為需求分析、設(shè)計數(shù)據(jù)庫、實現(xiàn)完整的數(shù)據(jù)庫、設(shè)計應(yīng)用程序界面、連接數(shù)據(jù)庫、應(yīng)用程序?qū)?shù)據(jù)庫的查詢統(tǒng)計等、系統(tǒng)調(diào)試七個階段，本文案例主要選擇實現(xiàn)完整的數(shù)據(jù)庫這個階段，旨在培養(yǎng)學(xué)生能夠依據(jù)E-R圖創(chuàng)建出符合完整性約束的數(shù)據(jù)庫的職業(yè)能力。

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

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

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

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

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

2數(shù)據(jù)庫完整性的概述

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

3實體完整性

實體完整性主要對表中的每一行進行限制，每一行是一個元組，一個實體，需要確保每個實體都是唯一的，不能重復(fù)?？赏ㄟ^設(shè)置主鍵（PRIMARY KEY）約束、唯一（UNIQUE）約束、索引實現(xiàn)實體完整性。根據(jù)實體完整性的知識點聯(lián)系網(wǎng)上購物系統(tǒng)的項目將設(shè)計以下案例說明PRIMARY KEY和UNIQUE約束的用法。

1）主鍵約束

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

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

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

2）唯一約束

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

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

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

4域完整性

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

1）檢查約束和非空約束

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

分析： CHECK約束可通過限制insert或update某一列或多列的值強制實現(xiàn)域完整性[4]。這里根據(jù)限制條件分析出使用的約束為CHECK約束和NOT NULL約束。根據(jù)限制電話號碼列只能為11位數(shù)字的條件得出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表增加用戶記錄時，沒有相關(guān)用戶的積分信息，則將積分字段的值默認為0，該如何設(shè)置約束？

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

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

3）規(guī)則和默認值對象

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

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

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

Create rule rule_1 as @x between 0 and 5 --定義規(guī)則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參照完整性

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

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

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

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

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

6用戶自定義完整性

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

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

分析：用戶生成一次訂單說明Order表中增加了一條記錄，而商品的庫存量自動更新說明觸發(fā)器觸發(fā)條件是向Order表中insert數(shù)據(jù)，觸發(fā)器觸發(fā)后引起的操作是update Goods表的庫存量。所以要在Order表上創(chuàng)建after（與for同義）觸發(fā)器，生成訂單時，要插入的銷售數(shù)量@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結(jié)束語

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

參考文獻：

[1] 朱成.基于Java的網(wǎng)上購物系統(tǒng)的設(shè)計與開發(fā)[D].南昌：南昌航空大學(xué)，2019：22.

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

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

[4] 陳林琳，蔣麗麗，解二虎.SQL Server 2008數(shù)據(jù)庫設(shè)計教程[M].鎮(zhèn)江：江蘇大學(xué)出版社，2013.

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

【通聯(lián)編輯：代影】