面向SQL Server 2012的數(shù)據(jù)庫(kù)約束的設(shè)計(jì)與應(yīng)用

陳瀟

摘 要：在SQL Server 2012中，當(dāng)管理員完成數(shù)據(jù)庫(kù)及表的建立后，后期對(duì)表中數(shù)據(jù)進(jìn)行插入、修改、刪除等操作時(shí)由于操作不當(dāng)，很可能會(huì)導(dǎo)致不符合規(guī)范的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)。為避免這種情況發(fā)生，數(shù)據(jù)庫(kù)表建立完畢后應(yīng)通過(guò)數(shù)據(jù)完整性來(lái)保證存儲(chǔ)的數(shù)據(jù)正確、有效、相容。約束是實(shí)現(xiàn)數(shù)據(jù)完整性的有效手段之一，本文介紹不同類型數(shù)據(jù)完整性的含義及實(shí)現(xiàn)手段，并結(jié)合具體的應(yīng)用場(chǎng)景創(chuàng)建相關(guān)約束。

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

中圖分類號(hào)：TP392 文獻(xiàn)標(biāo)識(shí)碼：A

Abstract：In SQL Server 2012，after the database and the tables are set up，some abnormal data may enter the database caused by administrators' improper operations of inserting，modifying，deleting，etc.In order to avoid this situation，data integrity should be used to ensure the correctness，validness and compatibility of the stored data after setting up the database and the tables.Constraint is one of the effective means to achieve data integrity.This article describes the meaning and implementation of different types of data integrity，and creates related constraints in combination with specific application scenarios.

Keywords：SQL Server 2012；constraint；data integrity

1 引言（Introduction）

當(dāng)管理員完成數(shù)據(jù)庫(kù)及表的建立后，后期對(duì)數(shù)據(jù)進(jìn)行插入、修改、刪除等作時(shí)由于操作不當(dāng)，很可能會(huì)導(dǎo)致止不符合規(guī)范的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)。為了保證存儲(chǔ)數(shù)據(jù)的正確，SQL Server中提出了數(shù)據(jù)完整性這一概念，是指數(shù)據(jù)庫(kù)中存儲(chǔ)數(shù)據(jù)的正確、有效和相容[1]。

SQL Server 2012中的數(shù)據(jù)完整性可以分為：實(shí)體完整性、域完整性和參照完整性和用戶自定義完整性。數(shù)據(jù)庫(kù)采用多種方法來(lái)保證數(shù)據(jù)完整性，包括外鍵、約束、規(guī)則和觸發(fā)器，本文探討使用約束作為手段實(shí)現(xiàn)數(shù)據(jù)完整性。

約束是實(shí)現(xiàn)數(shù)據(jù)完整性的有效手段，是一組完整性規(guī)則的集合。約束包括主鍵（PRIMARY KEY）約束、唯一鍵（UNIQUE）約束、檢查（CHECK）約束、默認(rèn)值（DEFAULT）約束、外鍵約束和級(jí)聯(lián)參照完整性約束。

2 實(shí)體完整性（Entity integrity）

實(shí)體完整性要求每一個(gè)表中的主鍵字段都不能為空或者重復(fù)的值。主要通過(guò)對(duì)表設(shè)置主鍵約束、唯一鍵約束來(lái)實(shí)現(xiàn)[2]。

2.1 主鍵約束

主鍵約束要求設(shè)置約束列不允許出現(xiàn)重復(fù)值，一般在設(shè)計(jì)表時(shí)通過(guò)對(duì)列設(shè)置主鍵來(lái)實(shí)現(xiàn)。使用T-SQL語(yǔ)句建立主鍵的格式如下：

Alter Table 表名

Add Constraint 約束名 Primary Key（列名）

比如：設(shè)置表UserInfo的UserId列為主鍵約束。

Alter Table UserInfo

Add Constraint IX_UserInfo Primary Key（UserId）

2.2 唯一鍵約束

與主鍵約束一樣，唯一鍵約束也可以保證列中數(shù)據(jù)的唯一性。與主鍵約束不同的是唯一鍵約束可以有多個(gè)，并且設(shè)置唯一鍵約束的列中可以有空值，但只能有一個(gè)空值（NULL）。

使用T-SQL語(yǔ)句建立唯一鍵約束的格式如下：

Alter Table 表名

Add Constraint 約束名 UNIQUE（列名）

數(shù)據(jù)庫(kù)管理員在建立數(shù)據(jù)表時(shí)一般會(huì)指定主鍵列，一張表只能存在一個(gè)主鍵約束。因此如果要求表中某一字段的取值不能出現(xiàn)重復(fù)值，則可以通過(guò)唯一鍵約束來(lái)實(shí)現(xiàn)。如用戶信息表中，已將‘用戶Id字段設(shè)置為主鍵，為了保證數(shù)據(jù)的唯一性，可以將‘電話號(hào)碼‘身份證號(hào)碼等要求唯一取值的字段設(shè)置為唯一鍵約束。

比如，將會(huì)員信息表（userInfo）的用戶移動(dòng)電話（userMobile）列設(shè)置為唯一鍵約束。

Alter table userInfo

Add Constraint IX_userMobile UNIQUE（userMobile）

設(shè)置唯一鍵約束后，向‘userMobile列中輸入重復(fù)的電話號(hào)碼，提示如圖1所示的錯(cuò)誤。

3 域完整性（Domain integrity）

域完整性是針對(duì)關(guān)系數(shù)據(jù)庫(kù)的約束條件，域完整性限制了某些屬性中出現(xiàn)的值，把屬性限制在一個(gè)有限的集合中，它保證表字段中不能輸入無(wú)效的值，即輸入值的范圍只能在規(guī)定的集合內(nèi)[3]。在SQL Server中實(shí)現(xiàn)域完整性主要通過(guò)檢查約束（CHECK）實(shí)現(xiàn)和默認(rèn)值約束（DEFAULT）。

檢查約束通過(guò)限制可輸入或修改的一列或多列的值來(lái)強(qiáng)制實(shí)現(xiàn)域完整性[4]，它作用于插入（INSERT）和修改（UPDATE）語(yǔ)句，語(yǔ)法格式如下。

Alter Table 表名

Add Constraint 約束名 CHECK （檢查表達(dá)式）

例如：將會(huì)員信息表（userInfo）的用戶移動(dòng)電話（userMobile）列限制為13位數(shù)字，且首位數(shù)字不能為‘0。

Alter Table userInfo

Add Constraint CK_mobileCHECK （userMobile like‘[1-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]）

在默認(rèn)情況下，檢查（CHECK）約束同時(shí)作用于新數(shù)據(jù)和表中已有的老數(shù)據(jù)，有些情況下，表字段中現(xiàn)有的記錄與必須遵從的特定業(yè)務(wù)相沖突，但取值沒(méi)有錯(cuò)誤，例如，過(guò)去可能要求郵政編碼必須是五位數(shù)字，但現(xiàn)在卻需要新數(shù)據(jù)為九位郵政編碼。包含五位郵政編碼的舊數(shù)據(jù)將與包含九位郵政編碼的新數(shù)據(jù)共存?？梢栽谕ㄟ^(guò)關(guān)鍵字WITH NOCHECK禁止檢查表中已有的數(shù)據(jù)。語(yǔ)法格式如下：

Alter Table 表名

Withnocheck Add Constraint 約束名 CHECK （檢查表達(dá)式）

4 參照完整性（Referential integrity）

在關(guān)系數(shù)據(jù)庫(kù)中，表之間是通過(guò)主鍵和外鍵存在聯(lián)系的，兩張具有聯(lián)系的表分為父表（主鍵列所在表）和子表（外鍵列所在表）。在數(shù)據(jù)表具體操作中經(jīng)常遇到以下情況，當(dāng)我們對(duì)表中的數(shù)據(jù)進(jìn)行插入、修改和刪除時(shí)，很容易出現(xiàn)父表、子表中相關(guān)記錄不匹配的現(xiàn)象[5]。

比如，將會(huì)員信息表（userInfo）中的用戶姓名（userName）進(jìn)行更改，如果與之相關(guān)的表中的用戶姓名不做更改，則會(huì)出現(xiàn)記錄不匹配的現(xiàn)象。再比如，如果在會(huì)員信息表（userInfo）中將一個(gè)會(huì)員刪除，那么與之相關(guān)的表，如訂單信息表（orderInfo）中該用戶的相關(guān)記錄應(yīng)該一起刪除。

參照完整性的目的是為了保證數(shù)據(jù)庫(kù)相關(guān)表之間記錄的一致性，涉及關(guān)系（表）之間的相互引用。要求關(guān)系中不允許引用不存在的實(shí)體，當(dāng)插入、更新、刪除表中的記錄時(shí)，通過(guò)參照引用相互關(guān)聯(lián)的另一個(gè)表中的相應(yīng)記錄，來(lái)檢查對(duì)表的操作是否符合要求。主要通過(guò)外鍵（FOREIGN KEY）約束和級(jí)聯(lián)參照完整性約束來(lái)實(shí)現(xiàn)。

4.1 外鍵約束

外鍵（FOREIGN KEY）約束是為了強(qiáng)制實(shí)現(xiàn)表之間的參照完整性。定義了一個(gè)關(guān)系數(shù)據(jù)庫(kù)中，不同的表中列之間的關(guān)系（父鍵與外鍵）。要求一個(gè)表中（子表）的一列或一組列的值必須與另一個(gè)表（父表）中的相關(guān)一列或一組列的值相匹配[6]。外鍵約束不允許為空值，但是，如果組合外鍵的某列含有空值，則將跳過(guò)該外鍵約束的檢驗(yàn)，格式如下。

Alter Table 表名

Add Constraint 關(guān)系名 FOREIGN KEY （外鍵列） REFERENCES 主鍵表（主鍵列）

比如，設(shè)置用戶信息表‘userinfo中的用戶編號(hào)列‘userid與訂單信息表‘orderInfo的用戶編號(hào)列‘userid列設(shè)置外鍵約束。語(yǔ)句格式如下：

Alter Table orderInfo

Add Constraint FK_FOREIGN FOREIGN KEY （userid） references userinfo（userid）

為兩表之間設(shè)置外鍵約束后，要求子表（orderinfo）中的‘userid列的取值必須與父表（userinfo）表中的‘userid列的取值匹配。如不匹配，比如輸入一個(gè)主表中沒(méi)有的數(shù)據(jù)（如：userId為16），則數(shù)據(jù)輸入完畢后提示無(wú)法更新，如圖2所示。

4.2 級(jí)聯(lián)參照完整性約束

級(jí)聯(lián)參照完整性約束是為了保證外鍵數(shù)據(jù)的關(guān)聯(lián)性。當(dāng)在父表中更新或刪除外鍵引用的鍵記錄時(shí)，則同時(shí)在子表中更新或刪除引用它的外鍵記錄，為了防止孤立外鍵的產(chǎn)生[7]。使用語(yǔ)句設(shè)置外鍵約束的格式如下。級(jí)聯(lián)參照完整性分為更新級(jí)聯(lián)約束和刪除級(jí)聯(lián)約束兩種。語(yǔ)法格式如下：

Alter Table 表名

Add Constraint 關(guān)系名 FOREIGN KEY （外鍵列） REFERENCES 引用表名（引用列） ON DELETE|UPDATE CASCADE

其中，ON DELETE CASCADE和ON UPDATE CASCADE分別表示帶更新級(jí)聯(lián)的外檢約束和帶刪除級(jí)聯(lián)的外鍵約束。

例如，設(shè)置會(huì)員信息表‘usrInfo和訂單信息表‘orderInfo之間的關(guān)系，實(shí)現(xiàn)當(dāng)父表（usrInfo）中的列‘userId更新數(shù)據(jù)時(shí)，子表（orderInfo）中相關(guān)列（userId）能一起更新。

Alter table orderInfo

Add constraint FK_會(huì)員信息_訂單信息FOREIGN KEY （userId） REFERENCES userInfo（userId） ON UPDATE CASCADE

約束設(shè)置完畢后，更新父表中的主鍵列數(shù)據(jù)（如將userid為‘3的記錄更改為‘103），則子表中相關(guān)列隨之一起更改。如圖3和圖4所示。

同理，使用ON DELETTE CASCADE子句可以實(shí)現(xiàn)父表中刪除記錄后，子表相關(guān)記錄會(huì)隨之刪除。

5 結(jié)論（Conclusion）

SQLServer中約束定義關(guān)于列中允許值的規(guī)則，是強(qiáng)制完整性的標(biāo)準(zhǔn)機(jī)制。使用約束優(yōu)先于使用觸發(fā)器、規(guī)則和默認(rèn)值[8]。數(shù)據(jù)庫(kù)管理者可根據(jù)數(shù)據(jù)完整性的不同級(jí)別合理使用約束，避免數(shù)據(jù)庫(kù)中出現(xiàn)無(wú)效數(shù)據(jù)，從而實(shí)現(xiàn)數(shù)據(jù)存儲(chǔ)的合理性和正確性。

參考文獻(xiàn)（References）

[1] 潘林森，姚渝春.SQL Server數(shù)據(jù)庫(kù)系統(tǒng)中的數(shù)據(jù)完整性控制方法[J].重慶師范大學(xué)學(xué)報(bào)（自然科學(xué)版），2004（02）：35-38.

[2] 赫然，王秀坤，王軼謳，等.分布式數(shù)據(jù)庫(kù)中海量數(shù)據(jù)采集完整性約束[J].大連理工大學(xué)學(xué)報(bào)，2013（S1）：190-192.

[3] 趙潔.信息系統(tǒng)開(kāi)發(fā)中的數(shù)據(jù)庫(kù)完整性維護(hù)技術(shù)[J].信息技術(shù)，2013（07）：137-139.

[4] 陳潔，薛恒威.SQL Server數(shù)據(jù)庫(kù)中數(shù)據(jù)完整性的分析與實(shí)踐[J].河北軟件職業(yè)技術(shù)學(xué)院學(xué)報(bào)，2018（01）：4-6.

[5] 陳增祥.SQL Server數(shù)據(jù)庫(kù)數(shù)據(jù)完整性的研究與分析[J].信息安全與技術(shù)，2012（01）：32-34.

[6] 王洪香.基于SQL Server數(shù)據(jù)庫(kù)完整性約束的幾點(diǎn)探討[J].辦公自動(dòng)化，2006（15）：25-27.

[7] 任靖.淺析數(shù)據(jù)庫(kù)觸發(fā)器和約束之優(yōu)先級(jí)及兼容性[J].電子制作，2013（3）：62.

[8] 王哲.數(shù)據(jù)庫(kù)中完整性約束的教學(xué)方法探究[J].中國(guó)科教創(chuàng)新刊，2013（35）：164-166.

作者簡(jiǎn)介：

陳 瀟（1983-），男，碩士，講師.研究領(lǐng)域：數(shù)據(jù)庫(kù)管理與應(yīng)用，管理信息系統(tǒng)開(kāi)發(fā).