數(shù)據(jù)完整性機制的研究與實現(xiàn)

王紅，陳功平

（六安職業(yè)技術學院信息工程系，安徽六安237158）

數(shù)據(jù)完整性機制的研究與實現(xiàn)

王紅，陳功平

（六安職業(yè)技術學院信息工程系，安徽六安237158）

為了保證數(shù)據(jù)庫中數(shù)據(jù)的準確性、一致性，數(shù)據(jù)庫設計者應合理設計數(shù)據(jù)表的數(shù)據(jù)完整性。SQLServer數(shù)據(jù)庫管理系統(tǒng)作為微軟的核心產(chǎn)品，可以通過主鍵、標識列、惟一鍵、檢查約束、外鍵約束等手段實現(xiàn)實體完整性、域完整性和參照完整性，參照完整性是保證數(shù)據(jù)一致性的重要機制，SQLServer中的觸發(fā)器也可以實現(xiàn)參照完整性，以保證數(shù)據(jù)表間的插入規(guī)則、更新規(guī)則和刪除規(guī)則的約定。

數(shù)據(jù)完整性；參照完整性；SQLServer數(shù)據(jù)庫管理系統(tǒng)；觸發(fā)器

數(shù)據(jù)完整性可以有效地保證數(shù)據(jù)庫中數(shù)據(jù)的一致性和正確性［1］。數(shù)據(jù)完整性設計是數(shù)據(jù)庫設計中的重點，其設計的優(yōu)劣會直接影響數(shù)據(jù)庫的后期使用，數(shù)據(jù)完整性按照所影響的對象不同，可分為實體完整性、域完整性和參照完整性［2］。

（1）實體完整性：用于保證關系表中的行不重復，防止相同記錄的存在，取自萬物不相同之意。

（2）域完整性：用于保證關系表中列的取值在一定范圍（領域）內(nèi)，限定取值的有界性。

（3）參照完整性：用于保證數(shù)據(jù)表之間數(shù)據(jù)的一致性，參照完整性是數(shù)據(jù)設計中非常重要的一個環(huán)節(jié)。

1 數(shù)據(jù)完整性實施策略

數(shù)據(jù)完整性有很強的數(shù)學理論基礎，各數(shù)據(jù)庫生產(chǎn)廠商按照完整性的要求，使用不同的策略來實現(xiàn)完整性理論。微軟公司作為軟件行業(yè)的龍頭，在數(shù)據(jù)庫管理系統(tǒng)設計方面有獨特的優(yōu)勢，其開發(fā)的ACESS、VFP、SQLServer等關系數(shù)據(jù)庫管理系統(tǒng)應用廣泛，其中SQLServer是實際應用最廣泛、管理能力最強的數(shù)據(jù)庫管理系統(tǒng)，獲得了各行業(yè)的認可，在數(shù)據(jù)庫界有著不可動搖的地位，SQLServer數(shù)據(jù)庫管理系統(tǒng)在實現(xiàn)數(shù)據(jù)完整性設計方面的方法和手段較多。

1.1 實體完整性實施策略

實體完整性是為了防止行出現(xiàn)重復，設計人員為了實現(xiàn)實體完整性，通常會虛擬出一個屬性，比如戶籍表虛擬出“身份證號”，學生表虛擬出“學號”，職工表虛擬出“職工編號”等。SQLServer中實現(xiàn)實體完整性的方法有主鍵約束、惟一鍵約束和標識字段。

（1）主鍵約束。設計數(shù)據(jù)表時應為每張表都設置主鍵約束，SQLServer中每個關系表的主鍵只能有一個，主鍵可以包含多個字段，但主鍵的取值不能重復也不可為空。指定關系表中的字段為主鍵可以使用圖形方式選擇，也可使用PRIMARY KEY關鍵字表示。

（2）惟一鍵約束。關系表可以設置多個惟一鍵，設置惟一鍵字段的取值不能重復，可取空值，但空值只能有一個。SQLServer中惟一鍵用UNIQUE表示，通常惟一鍵作為主鍵的補充，比如學生表指定學號為主鍵可保證其實體完整性，若再要保證“身份證號”列也不重復，就只能使用惟一鍵來實現(xiàn)。

（3）標識字段。SQLServer中的標識字段能夠?qū)崿F(xiàn)自動編號功能，因此可以保證記錄行的惟一性［3］。每個表的標識字段只能有一個，標識字段的數(shù)據(jù)類型必須為整型。標識字段列的值無需用戶輸入，計算機根據(jù)自動編號算法自動生成，通常用標識字段和主鍵共同使用來實現(xiàn)實體完整性及參照完整性。

SQLServer中定義標識字段的關鍵字為IDENTITY（起始編號，步長），比如IDENTITY（1，1）表示一個“1、2、3…”的序列，IDENTITY（-5，-2）表示一個“-5、-7、-9…”的序列。

1.2 域完整性實施策略

域完整性的實現(xiàn)策略主要有：為列選擇合適的數(shù)據(jù)類型和寬度，定義默認值，SQLServer中還可以用檢查約束進一步限定列的取值域。

定義默認值可以在創(chuàng)建數(shù)據(jù)表時為字段加入DEFAULT關鍵字，也可以在數(shù)據(jù)表創(chuàng)建完成后通過ALTER TABLE關鍵字為數(shù)據(jù)表列添加默認值約束，列有默認值后，當用戶未輸入內(nèi)容時自動用默認值填充。

檢查約束是實現(xiàn)域完整性的主要手段。比如要存儲年齡信息，就應將數(shù)據(jù)類型定義為整型而非字符型，姓名信息應定義為字符型而非數(shù)值型，若要應限制年齡的值為非負數(shù)，就需要再次定義檢查約束。檢查約束的關鍵字為CHECK，如CHECK（年齡＞=0 and年齡＜=200），CHECK（性別=‘男’or性別=‘女’）。

1.3 參照完整性實施策略

參照完整性是數(shù)據(jù)庫設計中最重要的環(huán)節(jié)，是保證數(shù)據(jù)一致性的重要手段，實現(xiàn)的主要手段為創(chuàng)建外鍵約束（FOREIGN KEY），在SQLServer數(shù)據(jù)庫管理系統(tǒng)中還可以通過觸發(fā)器實現(xiàn)。

（1）使用外鍵約束實現(xiàn)參照完整性。外鍵約束實現(xiàn)的是數(shù)據(jù)表間的關聯(lián)，表間的數(shù)據(jù)關聯(lián)有一對一（1∶1）、一對多（1∶n）和多對多（m∶n）三種類型，數(shù)據(jù)庫可以直接創(chuàng)建1∶1和1∶n關聯(lián)，而m∶n關聯(lián)無法直接實現(xiàn)，可以將m∶n聯(lián)系拆分成多個1∶1或1∶n聯(lián)系來間接實現(xiàn)。

在認知上，兩張表之間能夠創(chuàng)建關聯(lián)的條件是兩張表要有相同意義的字段，在SQLServer中只要兩張表具有數(shù)據(jù)類型相同、寬度相同并創(chuàng)建一定的約束后的列就可創(chuàng)建關聯(lián)，實現(xiàn)外鍵約束。創(chuàng)建外鍵約束時要選擇主鍵表和外鍵表，1∶1關聯(lián)，主鍵表和外鍵表是相對的，可任選；1∶n關聯(lián)，1端的表是主鍵表，n端的表是外鍵表。外鍵約束的實現(xiàn)條件如表1所示。

表1 SQL Server中實現(xiàn)外鍵約束的條件

數(shù)據(jù)表創(chuàng)建關聯(lián)后可通過配置更新規(guī)則和刪除規(guī)則來保證數(shù)據(jù)一致性，參照完整性規(guī)則及其可選配置值如表2所示。

使用外鍵約束創(chuàng)建數(shù)據(jù)關聯(lián)實現(xiàn)參照完整性時，要求關聯(lián)拓撲圖中無回路，不允許出現(xiàn)外鍵級聯(lián)［4］，而在實際應用時，數(shù)據(jù)表間的關聯(lián)關系出現(xiàn)回路的概率高，可以借助觸發(fā)器來完善。

表2 參照完整性規(guī)則

（2）使用觸發(fā)器實現(xiàn)數(shù)據(jù)完整性。觸發(fā)器是數(shù)據(jù)庫對象中的一種，可以編寫“當對數(shù)據(jù)庫對象執(zhí)行某項操作所引發(fā)的事件”代碼，在SQLServer中，數(shù)據(jù)表中記錄的INSERT、UPDATE、DELETE操作和數(shù)據(jù)庫、服務器中的CREATE、ALTER、DROP操作都可以設置觸發(fā)器，因此，根據(jù)觸發(fā)器所基于的數(shù)據(jù)庫對象的不同分為DML觸發(fā)器和DDL觸發(fā)器兩類［5］。

DML觸發(fā)器：數(shù)據(jù)操縱觸發(fā)器，基于數(shù)據(jù)操縱語言（DataMaster Language，DML）中的INSERT、UPDATE、DELETE命令創(chuàng)建。

DDL觸發(fā)器：數(shù)據(jù)定義觸發(fā)器，基于數(shù)據(jù)定義語言（Data Define Language，DDL）中的CREATE、ALTER、DROP命令創(chuàng)建。

因此，DML觸發(fā)器可以配合外鍵約束實現(xiàn)參照完整性，DML觸發(fā)器中有Inserted和Deleted兩張輔助表幫助用戶設計觸發(fā)器事件，這兩張表只在DML觸發(fā)器中可用，是一個只讀的邏輯表而非實際表。

Inserted表，存放即將加入數(shù)據(jù)表的數(shù)據(jù)行，執(zhí)行INSERT時，存放的是將要插入的數(shù)據(jù)行，執(zhí)行UPDATE時，存放的是更新后的數(shù)據(jù)行。

Deleted表，存放即將被刪除的數(shù)據(jù)行，執(zhí)行UPDATE時，存放的是更新前的數(shù)據(jù)行，執(zhí)行DELETE時，存放的是被刪除的數(shù)據(jù)行。

因此，INSERT只用到Inserted表，DELETE只用到Deleted表，UPDATE將更新后的數(shù)據(jù)行存入Inserted表，更新前的數(shù)據(jù)行存入Deleted表。

2 應用

數(shù)據(jù)完整性是數(shù)據(jù)表得以長期正確存儲的關鍵。本文以“留言版”系統(tǒng)中的數(shù)據(jù)表設計為例，介紹數(shù)據(jù)表、數(shù)據(jù)完整性設計的過程和方法。

2.1 數(shù)據(jù)表設計

“留言版”系統(tǒng)功能可以讓登錄用戶選擇不同的版塊發(fā)布留言、回復留言，因此必須要有用戶表、版塊表、留言表、留言回復表。

（1）用戶表（tb_users）：存儲注冊用戶的信息，表結(jié)構如表3所示。

表3 tb_users數(shù)據(jù)表結(jié)構

（2）版塊表（tb_board）：存儲留言版塊的信息，表結(jié)構如表4所示。

表4 tb_board數(shù)據(jù)表結(jié)構

（3）留言表（tb_message）：存儲留言的信息，表結(jié)構如表5所示。

表5 tb_message數(shù)據(jù)表結(jié)構

（4）留言回復表（tb_reply）：存儲回復每條留言的信息，表結(jié)構如表6所示。

表6 tb_reply數(shù)據(jù)表結(jié)構

上述數(shù)據(jù)表結(jié)構只有必須的數(shù)據(jù)信息，設計者可根據(jù)需要添加其他屬性以保存信息，表結(jié)構設計時，字段名、數(shù)據(jù)類型和寬度的選擇要慎重，因為對表結(jié)構的更新易導致數(shù)據(jù)丟失，從而影響頁面開發(fā)。數(shù)據(jù)表設計成功后進入數(shù)據(jù)完整性設計環(huán)節(jié)。

2.2 數(shù)據(jù)完整性設計

2.2.1 實體完整性

由上述2.1節(jié)可以看出，設計數(shù)據(jù)表時已經(jīng)為每張表配置主鍵來完成實體完整性，tb_users使用主鍵完成實體完整性，tb_board、tb_message和tb_reply使用標識字段和主鍵共同實現(xiàn)實體完整性，主鍵約束可以在創(chuàng)建表時配置，也可數(shù)據(jù)表創(chuàng)建成功后配置，創(chuàng)建tb_board表并設置主鍵、默認值的SQL命令如下：

CREATE TABLE tb_board（id INT IDENTITY（-32768，1）PRIMARY KEY,board_name VARCHAR (100)NOTNULL，description VARCHAR（1000），createtime DATETIME DEFAULTGETDATE（），username VARCHAR（16））

2.2.2 域完整性

設計數(shù)據(jù)表時為每個字段選定了合適的數(shù)據(jù)類型和寬度，這是保證域完整性的基礎，比如tb_users表中的pwd字段的數(shù)據(jù)類型為VARCHAR、寬度為16，可保證pwd列的取值最多為16個字符，但無法保證所錄入值的最小字符數(shù)，為了密碼安全性，通常要求密碼的最小長度為6位，要實現(xiàn)該域完整性，SQLServer中可通過添加檢查約束來實現(xiàn)，SQL命令如下：

ALTER TABLE tb_usersADDCHECK（LEN（pwd）＞=6）

2.2.3 參照完整性

根據(jù)上述1.3節(jié)的描述，“留言版”系統(tǒng)各數(shù)據(jù)表間的參照完整性設置如表7所示。

表7 “留言版”系統(tǒng)外鍵約束簡明信息

（1）使用外鍵約束實現(xiàn)參照完整性。當A表的主鍵字段在B表中出現(xiàn)，則B表中的字段可稱為A表主鍵的外鍵，主鍵和外鍵之間可以創(chuàng)建外鍵約束實現(xiàn)，創(chuàng)建外鍵約束可以使用外鍵表的設計器創(chuàng)建“關系”實現(xiàn)，也可以通過SQL命令實現(xiàn)，或通過創(chuàng)建關系圖實現(xiàn)。

1）使用SQL命令創(chuàng)建外鍵約束?？梢栽趧?chuàng)建表時新建外鍵約束，也可以在數(shù)據(jù)表創(chuàng)建成功后通過ALTER TABLE命令來創(chuàng)建外鍵約束，SQLServer中外鍵約束只能在外鍵表中設置和查看。

ALTER TABLE tb_board ADD FOREIGN KEY(username)REFERENCES tb_users(username)ON UPDATECASCADEONDELETESETNULL

其中ONUPDATECASCADE表示級聯(lián)更新，ONDELETESETNULL表示刪除規(guī)則設置為NULL。

2）使用數(shù)據(jù)庫關系圖創(chuàng)建外鍵約束。在數(shù)據(jù)庫的“數(shù)據(jù)庫關系圖”中新建關系圖，添加數(shù)據(jù)庫中的所有表，使用鼠標從主鍵拖動到外鍵創(chuàng)建并設置外鍵約束的更新規(guī)則和刪除規(guī)則。

“留言版”系統(tǒng)的數(shù)據(jù)庫關系圖如圖1所示。

（2）使用觸發(fā)器實現(xiàn)參照完整性。

1）插入規(guī)則。創(chuàng)建外鍵約束后可以自動保證插入規(guī)則，插入規(guī)則是針對外鍵表的約束，保證外鍵表的關聯(lián)字段值來自于主鍵表［6］，觸發(fā)器實現(xiàn)參照完整性中的插入規(guī)則是基于表的INSERT和UPDATE操作，以tb_users和tb_board表為例，創(chuàng)建觸發(fā)器實現(xiàn)插入規(guī)則的命令如下：

CREATE TRIGGER tr_tb_board_updateON tb_board FOR INSERT,UPDATE

AS

DECLARE@username VARCHAR（16）

SELECT@username=username FROM inserted

IF@username ISNOT NULL OR@username!=”）AND EXISTS(SELECT*FROM inserted WHERE inserted.username NOT IN（SELECTusername FROM tb_users））

ROLLBACK TRANSACTION

功能說明：當向表tb_board執(zhí)行INSERT或UPDATE時，若username列的值不為空值或空串時，且在tb_users表中不存在，則回滾事務。

2）更新規(guī)則。更新規(guī)則是針對主鍵表的約束，當主鍵表的關聯(lián)字段UPDATE后，外鍵表關聯(lián)字段所采取的措施稱為更新規(guī)則，以tb_users和tb_board表為例，創(chuàng)建觸發(fā)器實現(xiàn)更新規(guī)則的命令如下：

CREATETRIGGER tr_tb_users_updateON tb_users FORUPDATE

AS

IFUPDATE（username）

UPDATE tb_board SET username=（SELECT username FROM inserted）WHERE username=（SELECT username FROM deleted）

功能說明：當更新tb_users表的username字段值時，將tb_board表對應的username字段的值一并更新。

若將上述命令AS后的語句更換為如下語句，則表示默認的更新規(guī)則“不執(zhí)行任何操作”。

IF（EXISTS（SELECT*FROM insertedWHERE username IN（SELECTusername FROM tb_board）））

ROLLBACK TRANSACTION

3）刪除規(guī)則。刪除規(guī)則也是針對主鍵表的約束，當主鍵表執(zhí)行DELETE時，外鍵表關聯(lián)字段所采取的措施稱為刪除規(guī)則，以tb_users和tb_board表為例，創(chuàng)建觸發(fā)器實現(xiàn)刪除規(guī)則的命令如下：

CREATETRIGGER tr_tb_users_deleteON tb_users FORDELETE

AS

UPDATE tb_board SETusername=“WHERE username=（SELECTusername FROM deleted）

功能說明：當刪除tb_users表的數(shù)據(jù)行時，將tb_board表對應的username值設置為空串。

3 結(jié)語

為了實現(xiàn)數(shù)據(jù)安全性、一致性，數(shù)據(jù)庫管理系統(tǒng)的生產(chǎn)廠商也在努力升級［7］，作為數(shù)據(jù)庫設計者，應該使用各種方法來實現(xiàn)數(shù)據(jù)完整性，保證數(shù)據(jù)又快又好地運行下去。數(shù)據(jù)完整性不僅要在數(shù)據(jù)庫中進行配置，在數(shù)據(jù)錄入和修改頁面也應該通過腳本代碼等各種途徑實現(xiàn)。

本文以SQLServer數(shù)據(jù)庫管理系統(tǒng)為模板，研究和實現(xiàn)了數(shù)據(jù)完整性，尤其是使用觸發(fā)器來定義插入規(guī)則、更新規(guī)則和刪除規(guī)則，這也給了設計人員一個重新設計完整性規(guī)則的入口，在數(shù)據(jù)庫設計方面有著廣泛的應用前景和意義。

［1］劉永楠,鄒兆年,李建中,等.數(shù)據(jù)完整性的評估方法［J］.計算機研究與發(fā)展,2013,50（S1）∶230-238.

［2］于洋洋,虞慧群,范貴生.一種云存儲數(shù)據(jù)完整性驗證方法［J］.華東理工大學學報∶自然科學版,2013,39（2）∶211-216.

［3］曹夕,許力,陳蘭香.云存儲系統(tǒng)中數(shù)據(jù)完整性驗證協(xié)議［J］.計算機應用,2012,32（1）∶8-12.

［4］康效龍,張玉清,田玉敏.觸發(fā)器在實現(xiàn)數(shù)據(jù)庫安全方面的新應用［J］.計算機工程,2005,31（19）∶157-159.

［5］CHANDRASP.Avoid Referential Integrity ErrorsWhen Deleting Records from Databases［J］.SQLServerMagazine,2009,11（6）∶15-16.

［6］劉樹,徐碩,黃其泉,等.一種基于觸發(fā)器的數(shù)據(jù)同步系統(tǒng)的研究及關鍵技術實現(xiàn)［J］.計算機應用與軟件,2012,29（12）∶189-191.

［7］NAMHEUNS,YUNHO L,DOHYUNK,etal.A study ofuser data integrity duringacquisition of Android devices［J］.Digital Investigation,2013,10（2）∶3-11.

【責任編輯：王桂珍foshanwgzh@163.com】

Research and implementation of data integrity

WANGHong，CHENGong-ping

（Departmentof Information Engineering,Luan Vocation Technology College,Luan 237158,China）

∶In order toensure theaccuracy and consistencyof the data in the database,the database designermust set the data integrity for data tables.SQL Server databasemanagement system as the core productofMicrosoft, through theprimary key,identity column,unique key,check constraints,foreign key constraintsand othermeans to achieve the entity integrity,domain integrity and referential integrity.Referential integrity is an important means to ensure the consistency of data,SQL Server triggers can also implement referential integrity,to ensure the data insertion rules,theupdate ruleand the deletion rule.

data integrity;referential integrity;SQLServer databasemanagementsystem;trigger

TP309.2

A

1008-0171（2015）01-0081-07

2014-06-16

安徽省高等學校教學質(zhì)量與教學改革工程項目（2012jyxm667，2013jyxm667）

王紅（1983-），女，安徽霍邱人，六安職業(yè)技術學院講師。