第五章數(shù)據(jù)完整性

第五章數(shù)據(jù)完整性【本章要點(diǎn)】1、數(shù)據(jù)完整性的類型及區(qū)別2、約束的類型及創(chuàng)建和刪除3、默認(rèn)的創(chuàng)建、綁定和刪除4、規(guī)則的創(chuàng)建、綁定和刪除5.1數(shù)據(jù)完整性概念

當(dāng)人們操作表中的數(shù)據(jù)時(shí),由于一些人為原因,我們經(jīng)常遇到一些問題,例如:員工進(jìn)入公司的日期早于該員工的出生日期；如某公司的員工表中發(fā)現(xiàn)有相同的員工編號；又如某公司人事部有一張員工信息表和一張員工借款信息表,但后來發(fā)現(xiàn)借款表中的某個(gè)員工不是公司的員工。諸如此類的問題，不能僅靠數(shù)據(jù)錄入人員和操作人員的認(rèn)真和負(fù)責(zé)，而應(yīng)該建立一套保障機(jī)制；要么防止這類問題的發(fā)生，要么發(fā)生這些問題時(shí)可以及時(shí)地發(fā)現(xiàn)。數(shù)據(jù)完整性就是解決這些問題的機(jī)制。

數(shù)據(jù)完整性（DataIntegrity）是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)的一致性和準(zhǔn)確性。

它是應(yīng)防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯誤信息的輸入輸出造成無效操作或錯誤信息而提出的。在MicrosoftSQLServer2005系統(tǒng)中數(shù)據(jù)完整性分為三類：域完整性、實(shí)體完整性、參照完整性5.1.1域完整性

域完整性，也可稱為列完整性，指數(shù)據(jù)庫表中的列必須滿足某種特定的數(shù)據(jù)類型或約束。其中約束又包括取值范圍、精度等規(guī)定。例如，在性別列中，限制其取值范圍為“男”和“女”，其他的取值無效。5.1.2實(shí)體完整性

實(shí)體完整性，也可稱為行完整性，規(guī)定表中的每一行都有惟一標(biāo)識符，這種標(biāo)識符一般稱為主鍵值。例如，對于所有的中國公民來說,居民身份證號碼是唯一的，使用居民身份證號碼可以唯一地確定某一個(gè)人，因此可以把公民的居民身份證明號碼作為主鍵對待，主鍵值是否能夠被修改或表中的全部數(shù)據(jù)是否能夠被全部刪除都要依賴于主鍵表和其他表之間要求的完整性。5.1.3參照完整性

參照完整性，也可稱為引用完整性，指兩個(gè)表（被引用表）的主關(guān)鍵字和（引用表的）外部關(guān)鍵字的數(shù)據(jù)應(yīng)對應(yīng)一致。它確保了有主關(guān)鍵字的表中對應(yīng)其它表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴(kuò)散。例如，在一個(gè)數(shù)據(jù)庫中有兩張表，即人事表和財(cái)務(wù)表。人事表中記錄了本單位的所有員工的基本信息，財(cái)務(wù)表記錄了本單位員工的借款信息。一般地，如果某個(gè)公司員工有借款，那么他就不能直接從人事表中刪除。數(shù)據(jù)完整性分析5.2約束(constraints)

約束是通過限制列中數(shù)據(jù)、行中數(shù)據(jù)和表之間數(shù)據(jù)來保證數(shù)據(jù)完整性的非常有效的方法。建立和使用約束的目的是保證數(shù)據(jù)的完整性，在SQLServer2005中可使用5種約束來限制列中的數(shù)據(jù)、行中的數(shù)據(jù)和表之間的數(shù)據(jù)以保證數(shù)據(jù)的完整性。1、CHECK約束/檢查約束：限制插入列中的值2、PRIMARYKEY約束/主鍵約束：要求主鍵的列上沒有兩行具有相同值，也沒有空值。3、FOREIGNKEY約束/外鍵約束:要求指定的列（外鍵）中正被插入或更新的新值，必須在被參照表（主表）的相應(yīng)列（主鍵）中已經(jīng)存在。4、UNIQUE約束/唯一約束:要求表中在指定的列上沒有兩行具有相同的值。5、DEFAULT約束/缺省約束：當(dāng)向數(shù)據(jù)庫中的表插入數(shù)據(jù)時(shí)，如果用戶沒有明確給出某列的值，

SQL

Server自動為該列輸入指定值。5.2.1check約束

CHECK約束用于限制輸入到一列或多列中值的范圍來保證SQLServer數(shù)據(jù)庫中數(shù)據(jù)的域完整性，也就是一個(gè)字段的輸入內(nèi)容必須滿足CHECK約束的條件，否則，數(shù)據(jù)無法正常輸入，從而強(qiáng)制數(shù)據(jù)的域完整性。

方法一、使用SQLServer企業(yè)管理器定義、刪除check約束

【例1】在客服表中定義性別列只能輸入“男“或者“女“的check約束

使用T-SQL語句來創(chuàng)建check約束1.格式:創(chuàng)建表的同時(shí)創(chuàng)建check約束Createtable表名(…..，列名數(shù)據(jù)類型是否為空constraint約束名check(表達(dá)式),…)2.修改表時(shí)添加check約束。Altertable表名AddConstraint約束名check(表達(dá)式)創(chuàng)建客戶表的同時(shí)定義check約束要求性別列只能輸入“男“或者”女“Use手機(jī)Createtable客戶表

(客戶編號varchar(10)notnull,

客戶姓名varchar(10)notnull,

客戶密碼char(6)notnull,

性別char(2)notnullConstraintck_性別check(性別=’男’or性別=’女’),

證件號varchar(18)notnull,

月收入smallmoneynull)創(chuàng)建職工表時(shí)，給年齡加個(gè)check約束，將年齡限制在18-60歲createtable職工(職工號char(20)null,姓名char(20)null,年齡intnullconstraintck_年齡check(年齡>=18and年齡<=60),籍貫char(20)null，學(xué)歷char(20)null)給職工表的學(xué)歷添加約束Altertable職工AddConstraintck_學(xué)歷check(學(xué)歷=‘本科’or學(xué)歷=‘碩士’or學(xué)歷=‘博士’)3.使用T-SQL語句刪除check約束

(注意：次語句可用于任何一種約束的刪除）語法：Altertable表名Dropconstraint約束名稱(1)刪除約束Altertable客戶表Dropconstraintck_客戶表5.2.2PRIMARYKEY約束

在數(shù)據(jù)庫的每個(gè)表中，經(jīng)常有一列或者多個(gè)列的組合，其值能唯一的標(biāo)識表中的每一行。就好像我們平時(shí)使用的身份證，能夠唯一的標(biāo)識每個(gè)人一樣。這樣的一列或者多個(gè)列，被稱為主鍵，通過主鍵，可以強(qiáng)制表的實(shí)體完整性。

每一張表中只能有一個(gè)PRIMARYKEY約束，在使用PRIMARYKEY約束時(shí)，該列的屬性必須定義為空值NOTNULL，也就是說擁有主鍵的那一列，不能為空。

定義為主鍵的列，不允許有空值和重復(fù)值方法一、使用SQLServer企業(yè)管理器定義1、定義PRIMARYKEY約束【例5】對客戶表中的證件號定義PRIMARYKEY約束使用T-SQL語句來創(chuàng)建格式一：創(chuàng)建表的時(shí)候創(chuàng)建主鍵約束Createtable表名(……列名數(shù)據(jù)類型是否為空constraint主鍵約束名primarykey,……)格式二：創(chuàng)建好的表添加primarykey約束Altertable表名AddConstraint主鍵約束名primarykey(主鍵列)1、在創(chuàng)建表的同時(shí)定義PRIMARYKEY約束【例6】創(chuàng)建客戶表的同時(shí)定義列“證件號”為PRIMARYKEY約束

Use手機(jī)Createtable客戶表(客戶編號varchar(10)notnull,客戶姓名varchar(10)notnull,客戶密碼char(6)notnull,性別char(2)notnull，證件號varchar(18)notnullConstraintpk_證件號primarykey,月收入smallmoneynull)創(chuàng)建工資表的同時(shí)為職工號創(chuàng)建主鍵createtable工資(職工號char(20)notnullconstraintccprimarykey,姓名char(20)null,工資char(20)null)假設(shè)創(chuàng)建工資表時(shí)未有主鍵現(xiàn)添加主鍵約束,約束名稱是aaaaltertable工資addconstraintaaaprimarykey(職工號)2、刪除PRIMARYKEY約束語法:Dropconstraint主鍵名稱【例8】刪除例5、9中為證件號添加的PRIMARYKEY約束Use手機(jī)Altertable客戶表Dropconstraintpk_客戶表

練習(xí)1.創(chuàng)建表學(xué)生(學(xué)號,姓名,籍貫,入學(xué)時(shí)間,性別)同時(shí)，為入學(xué)時(shí)間加個(gè)check約束，將時(shí)間約束在2009-9-1號以后2.添加check約束cc,將性別約束成為男或者為女3.將表成績(學(xué)號，姓名，成績)的學(xué)號添加為主鍵約束，約束名為:abc5.2.3FOREIGNKEY約束

FOREIGNKEY是用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的關(guān)系的一列或多列。通過將保存表中主鍵值的一列或多列添加到另一個(gè)表中，可創(chuàng)建兩個(gè)表之間的關(guān)系，這個(gè)列就成為第二個(gè)表的外鍵。

例如，數(shù)據(jù)庫‘教學(xué)(teach)’中的‘選課(xk)表’與‘學(xué)生(student)表’有關(guān)系，因?yàn)樵趯W(xué)生和選課之間存在邏輯聯(lián)系?！x課表’中的學(xué)號(s_no)列與‘學(xué)生表’中的主鍵列學(xué)號(s_no)相對應(yīng)，這時(shí)從表‘選課表’中的學(xué)號(s—no)列是主表‘學(xué)生表’的外鍵

定義外鍵約束時(shí)應(yīng)注意以下幾點(diǎn)：1、定義表之間的參照關(guān)系時(shí)應(yīng)該先選定主表并定義其主鍵，再對從表定義外鍵約束.2、定義外鍵之前必須保證從表與主表相同列的數(shù)據(jù)滿足要求。（相同列從表的數(shù)據(jù)小于或等于主表數(shù)據(jù)).3、一個(gè)表最多能夠建立253個(gè)外鍵關(guān)系。4、外鍵約束同時(shí)也可以參照自身表中的其他列5、外鍵約束，只能參照本身數(shù)據(jù)庫中的某個(gè)表，而不能參照其他數(shù)據(jù)庫中的表。跨數(shù)據(jù)庫的參照只能通過觸發(fā)器來實(shí)現(xiàn)。createtable宿舍表(學(xué)號char(20)nullconstraintfk_ssjbforeignkeyreferences基本情況表(學(xué)號),宿舍號char(20)notnullprimarykey,宿舍名char(20)nullconstraintck_ssmcheck(宿舍名='1棟'or宿舍名='2棟'),管理員char(20)constraintmrglydefault'劉月明')使用T-SQL語句來創(chuàng)建、刪除FOREIGNKEY約束

創(chuàng)建表的同時(shí)創(chuàng)建外鍵約束Createtable表名(….列名數(shù)據(jù)類型是否為空constraint約束名ForeignkeyReferences主表(列名),…)已創(chuàng)建好的表中添加外鍵約束Altertable表名AddConstraint約束名稱foreignkey(列名)References主表(列名)Use手機(jī)Createtable訂單表

( 訂單編號 varchar(10) notnullprimarykey,

客戶編號varchar(10)notnullconstraintggforeignkeyReferences客戶表(客戶編號),

商品編號varchar(10)notnull,

訂購數(shù)量int,

銷售額money,

購物時(shí)間smalldatetime)5.2.4UNIQUE約束

UNIQUE約束用于表中的非主鍵列，UNIQUE約束保證一列或者多列的數(shù)據(jù)完整性，確保這些列不會輸入重復(fù)的值，即沒有相同的兩列值。例如，客戶表中客戶編號列為主鍵，但是其中還包括證件號碼列，由于所有證件號碼不可能出現(xiàn)重復(fù)，所以可以在此列上建立UNIQUE約束，確保不會輸入重復(fù)的證件號碼。與PRIMARYKEY約束類似，UNIQUE約束也強(qiáng)制惟一性，為表中的一列或多列提供實(shí)體完整性，且每個(gè)UNIQUE約束要建立一個(gè)惟一索引；UNIQUE約束和PRIMARYKEY約束主要區(qū)別如下：

1）UNIQUE約束用于非主鍵的一列或者多列組合；

2）一個(gè)表可以定義多個(gè)UNIQUE約束，而只能定義一個(gè)PRIMARYKEY約束；

3）UNIQUE約束可以用于定義允許空值的列，而PRIMARYKEY約束則不能用于允許空值的列。Use手機(jī)Createtable客戶表

(客戶編號varchar(10)notnull,

客戶姓名varchar(10)notnull,

客戶密碼char(6)notnull,

性別char(2)notnull，證件號varchar(18)nullConstraintwyunqiue,

月收入smallmoneynull)altertable工資addconstraintfk_職工_工資foreignkey(職工號)references職工(職工號)Createtable宿舍分配表(職工號varchar(20)notnullprimarykeyconstraintxxforeignkeyreferences職工(職工號),姓名char(20)null,寢室號char(20)null)Unique約束確保數(shù)據(jù)庫在的元組在非主鍵列上取值不同。比如說一個(gè)學(xué)生信息表，學(xué)號是主鍵，假設(shè)在表里還有一個(gè)身份證號的列，該列不作為主鍵，但它的取值也不能相同，所以，可以用unique約束保證輸入數(shù)據(jù)庫中的身份證號是不同的。用SQL創(chuàng)建UNIQUE約束1.創(chuàng)建表時(shí)加約束Createtable表名(……,列名數(shù)據(jù)類型nullConstraint約束名unique,…)2.往已經(jīng)創(chuàng)建好的表中添加Altertable表名AddConstraint約束名Unique(列名)5.2.5DEFAULT約束

如對表中的某列定義了default約束后,用戶在插入新的數(shù)據(jù)行時(shí),如果沒有為該列制定數(shù)據(jù),那么系統(tǒng)將默認(rèn)值賦予該列,默認(rèn)值可以為空值(null)

方法一、使用SQLServer企業(yè)管理器定義、刪除DEFAULT約束1、使用SQLServer企業(yè)管理器定義DEFAULT約束【例16】為客戶表中的性別列定義default約束，使其性別的默認(rèn)值為“男”

用Sql語言創(chuàng)建默認(rèn)約束1.創(chuàng)建表的同時(shí)添加Createtable表名(……列名數(shù)據(jù)類型nullConstraint約束名Default‘值’,…)2.創(chuàng)建完了后添加Altertable表名AddConstraint約束名default‘值’for列名創(chuàng)建宿舍分配表，并將宿舍號默認(rèn)成’101’createtable宿舍分配(職工號varchar(20)notnull,姓名char(20)null,宿舍號char(20)nullconstraintmr_宿舍號default'101')將職工表的籍貫列默認(rèn)成’成都’altertable職工addconstraintmr_籍貫default'成都'for籍貫練習(xí)1.創(chuàng)建表：學(xué)生(學(xué)號,姓名,性別,專業(yè),身份證號)且創(chuàng)建的同時(shí)將學(xué)號設(shè)置主鍵約束，身份證號設(shè)定unique約束2.創(chuàng)建表:成績(學(xué)號,姓名,成績,獎學(xué)金)創(chuàng)建的同時(shí)將獎學(xué)金設(shè)置默認(rèn)為:無3.以成績?yōu)閺谋?，學(xué)生為主表，為成績創(chuàng)建一個(gè)外鍵約束cc4.為成績表中的姓名添加unique約束，名稱為ll5.為學(xué)生表中的“專業(yè)”添加默認(rèn)約束ff,使專業(yè)默認(rèn)為”計(jì)算機(jī)”createtable學(xué)生(學(xué)號char(20)notnullconstraintpk_xhprimarykey,姓名char(20)null,性別char(2)null,專業(yè)char(20)null,身份證號char(18)nullconstraintun_sfzhunique)createtable成績(學(xué)號char(20)notnull,姓名char(20)null,成績floatnull,獎學(xué)金moneynullconstraintde_jxjdefault'無')altertable成績addconstraintfk_xhforeignkey(學(xué)號)references學(xué)生(學(xué)號)altertable成績addconstraintllunique(專業(yè))altertable學(xué)生addconstraintffdefault'計(jì)算機(jī)'for專業(yè)5.3默認(rèn)與規(guī)則5.3.1默認(rèn)我們在向數(shù)據(jù)表中輸入數(shù)值時(shí)，希望表里面的某些列已經(jīng)具有一些默認(rèn)值，不必用戶一一輸入，或者是用戶現(xiàn)在還不準(zhǔn)備輸入但又不想空著。例如，輸入學(xué)生性別的時(shí)候，先默認(rèn)所有學(xué)生的性別為“男”，如果輸入的是個(gè)男生則“性別”列不必每次輸入了，如果是女生就將“男”改為“女”，這樣大大減少輸入數(shù)據(jù)的工作量。又如輸入一個(gè)班的課程信息，由于全班同學(xué)在一個(gè)教室上課，那么設(shè)定教室一個(gè)默認(rèn)值，則每個(gè)同學(xué)的上課教室就不必輸入了。

默認(rèn)對象在功能上與默認(rèn)約束是一樣的，但在使用上有所區(qū)別：默認(rèn)約束是在創(chuàng)建表或者修改表時(shí)定義的，嵌入到被定義的表結(jié)構(gòu)中，在刪除表的同時(shí)默認(rèn)約束也被刪除；默認(rèn)對象需要用CREATEDEFAULT語句進(jìn)行定義，作為一種單獨(dú)存儲的數(shù)據(jù)庫對象，它獨(dú)立于表結(jié)構(gòu)之外，默認(rèn)對象的使用必須要通過綁定，刪除表時(shí)并不能刪除默認(rèn)對象，而需要使用DROPDEFAULT語句進(jìn)行刪除。

創(chuàng)建默認(rèn)格式:Createdefault默認(rèn)名as‘值’例如createdefaultddas'男’創(chuàng)建完后，需要綁定在表中的某列，才能真正起到默認(rèn)的作用綁定到表中的某列格式：Sp_bindefault默認(rèn)名,’表名.列名’例如sp_bindefaultdd,'職工.性別‘刪除默認(rèn)綁定去除格式:sp_unbindefault‘表名.列名’例如sp_unbindefault'成績.專業(yè)‘刪除格式:Dropdefault默認(rèn)名創(chuàng)建默認(rèn)zy，值是”物流”Createdefaultzyas‘物流’將它綁定到學(xué)生成績表中的專業(yè)列上Sp_bindfaultzy,’學(xué)生成績.專業(yè)’刪除默認(rèn)sp_unbindefault'成績.專業(yè)‘Dropdefaultzy5.3.2規(guī)則

規(guī)則就是創(chuàng)建一套準(zhǔn)則，并將其結(jié)合到表的列或用戶自定義數(shù)據(jù)類型上，它的作用類似于CHECK約束，添加完之后它會檢查添加的數(shù)據(jù)或者對表所作的修改是否滿足所設(shè)置的條件。

規(guī)則也是一種獨(dú)立的數(shù)據(jù)庫對象，規(guī)則可以綁定到一列或者多列上，也可以綁定到用戶自定義數(shù)據(jù)類型上。規(guī)則與其作用的表或用戶自定義數(shù)據(jù)類型是相互獨(dú)立的，即表或用戶自定義對象的刪除、修改不會對與之相連的規(guī)則產(chǎn)生影響。

規(guī)則對象在功能上與CHECK約束是一樣的，但在使用上也有區(qū)別：

1、CHECK約束是在創(chuàng)建表或者修改表時(shí)定義的，嵌入到被定義的表結(jié)構(gòu)中，在刪除表的同時(shí)CHECK約束也被刪除；

2、規(guī)則對象需要用CREATERULE語句進(jìn)行定義，作為一種單獨(dú)存儲的數(shù)據(jù)庫對象，它獨(dú)立于表結(jié)構(gòu)之外，規(guī)則對象的使用必須要通過綁定，刪除表時(shí)并不能刪除規(guī)則對象，而需要使用DROPRULE語句進(jìn)行刪除。

方法二、使用T-SQL語言CREATERULE語句創(chuàng)建規(guī)則語法格式:Createrule規(guī)則名稱as@規(guī)則名稱條件表達(dá)式

【例27】以例26為例，用CREATERULE語句創(chuàng)建規(guī)則