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

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

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

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

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

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

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

參照完整性，也可稱為引用完整性，指兩個表（被引用表）的主關(guān)鍵字和（引用表的）外部關(guān)鍵字的數(shù)據(jù)應對應一致。它確保了有主關(guān)鍵字的表中對應其它表的外關(guān)鍵字的行存在，即保證了表之間的數(shù)據(jù)的一致性，防止了數(shù)據(jù)丟失或無意義的數(shù)據(jù)在數(shù)據(jù)庫中擴散。例如，在一個數(shù)據(jù)庫中有兩張表，即人事表和財務表。人事表中記錄了本單位的所有員工的基本信息，財務表記錄了本單位員工的借款信息。一般地，如果某個公司員工有借款，那么他就不能直接從人事表中刪除。數(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約束/外鍵約束:要求指定的列（外鍵）中正被插入或更新的新值，必須在被參照表（主表）的相應列（主鍵）中已經(jīng)存在。4、UNIQUE約束/唯一約束:要求表中在指定的列上沒有兩行具有相同的值。5、DEFAULT約束/缺省約束：當向數(shù)據(jù)庫中的表插入數(shù)據(jù)時，如果用戶沒有明確給出某列的值，

SQL

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

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

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

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

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

(客戶編號varchar(10)notnull,

客戶姓名varchar(10)notnull,

客戶密碼char(6)notnull,

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

證件號varchar(18)notnull,

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

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

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

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

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

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

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

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

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

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

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

( 訂單編號 varchar(10) notnullprimarykey,

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

商品編號varchar(10)notnull,

訂購數(shù)量int,

銷售額money,

購物時間smalldatetime)5.2.4UNIQUE約束

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

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

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

3）UNIQUE約束可以用于定義允許空值的列，而PRIMARYKEY約束則不能用于允許空值的列。Use手機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ù)庫在的元組在非主鍵列上取值不同。比如說一個學生信息表，學號是主鍵，假設在表里還有一個身份證號的列，該列不作為主鍵，但它的取值也不能相同，所以，可以用unique約束保證輸入數(shù)據(jù)庫中的身份證號是不同的。用SQL創(chuàng)建UNIQUE約束1.創(chuàng)建表時加約束Createtable表名(……,列名數(shù)據(jù)類型nullConstraint約束名unique,…)2.往已經(jīng)創(chuàng)建好的表中添加Altertable表名AddConstraint約束名Unique(列名)5.2.5DEFAULT約束

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

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

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

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

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

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

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

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

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

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

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

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