數(shù)據(jù)完整性在SQL中的實(shí)現(xiàn).

1、數(shù)據(jù)完整性在SQL中的實(shí)施數(shù)據(jù)完整性問(wèn)題：數(shù)據(jù)完整性的含義？問(wèn)題：數(shù)據(jù)完整性的含義？ 作用：作用：防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫(kù)中存在不正確的數(shù)據(jù)防范對(duì)象防止數(shù)據(jù)庫(kù)中存在不正確的數(shù)據(jù)防范對(duì)象不合語(yǔ)義的、不正確的數(shù)據(jù)不合語(yǔ)義的、不正確的數(shù)據(jù)數(shù)據(jù)完整性 問(wèn)題：數(shù)據(jù)完整性的類(lèi)型有哪幾類(lèi)？問(wèn)題：數(shù)據(jù)完整性的類(lèi)型有哪幾類(lèi)？實(shí)體完整性實(shí)體完整性 問(wèn)題：實(shí)體完整性的含義？問(wèn)題：實(shí)體完整性的含義？ 常見(jiàn)的實(shí)體完整性機(jī)制包括：常見(jiàn)的實(shí)體完整性機(jī)制包括：主鍵（主鍵（Primary Key）；）；惟一碼（惟一碼（Unique）；）；參照完整性參照完整性 問(wèn)

2、題：參照完整性指的是什么？問(wèn)題：參照完整性指的是什么？可以防止數(shù)據(jù)丟失或者無(wú)意義的數(shù)據(jù)；可以防止數(shù)據(jù)丟失或者無(wú)意義的數(shù)據(jù)；可以禁止在從表中插入被參照表中不存在的關(guān)可以禁止在從表中插入被參照表中不存在的關(guān)鍵字的記錄。鍵字的記錄。 參照完整性的常見(jiàn)實(shí)現(xiàn)機(jī)制包括：參照完整性的常見(jiàn)實(shí)現(xiàn)機(jī)制包括：外鍵（外鍵（Foreign Key）、觸發(fā)器（）、觸發(fā)器（Trigger）；）；域完整性域完整性 域完整性要求列（字段）的數(shù)據(jù)具有正確的數(shù)據(jù)域完整性要求列（字段）的數(shù)據(jù)具有正確的數(shù)據(jù)類(lèi)型、格式和有效的數(shù)值范圍。類(lèi)型、格式和有效的數(shù)值范圍。 域完整性的常見(jiàn)實(shí)現(xiàn)機(jī)制包括：域完整性的常見(jiàn)實(shí)現(xiàn)機(jī)制包括：默認(rèn)值（默認(rèn)值（

3、Default）檢查（檢查（Check）數(shù)據(jù)類(lèi)型（數(shù)據(jù)類(lèi)型（Data type）規(guī)則（規(guī)則（Rule）用戶自定義完整性用戶自定義完整性 用戶定義的完整性就是針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須用戶定義的完整性就是針對(duì)某一具體應(yīng)用的數(shù)據(jù)必須滿足的語(yǔ)義要求；可直接由滿足的語(yǔ)義要求；可直接由RDBMS提供，而不必由應(yīng)提供，而不必由應(yīng)用程序承擔(dān)；系統(tǒng)將實(shí)現(xiàn)數(shù)據(jù)完整性的要求直接定義用程序承擔(dān)；系統(tǒng)將實(shí)現(xiàn)數(shù)據(jù)完整性的要求直接定義在表上或列上。在表上或列上。 常見(jiàn)的實(shí)現(xiàn)機(jī)制包括：常見(jiàn)的實(shí)現(xiàn)機(jī)制包括：規(guī)則（規(guī)則（Rule）、創(chuàng)建數(shù)據(jù)表時(shí)的所有約束）、創(chuàng)建數(shù)據(jù)表時(shí)的所有約束（Constraint）、觸發(fā)器（）、觸發(fā)器（T

4、rigger）、存儲(chǔ)過(guò)程）、存儲(chǔ)過(guò)程（Stored Produre）；在SQL Server 2005中實(shí)現(xiàn)數(shù)據(jù)完整性 SQL Server 2005提供了完善的數(shù)據(jù)完整性提供了完善的數(shù)據(jù)完整性機(jī)制，主要包括約束、默認(rèn)值和規(guī)則機(jī)制，主要包括約束、默認(rèn)值和規(guī)則3類(lèi)；類(lèi)；創(chuàng)建及管理約束及約束對(duì)象創(chuàng)建及管理約束及約束對(duì)象 默認(rèn)值約束及默認(rèn)值對(duì)象默認(rèn)值約束及默認(rèn)值對(duì)象創(chuàng)建及管理規(guī)則對(duì)象創(chuàng)建及管理規(guī)則對(duì)象創(chuàng)建及管理約束及約束對(duì)象創(chuàng)建及管理約束及約束對(duì)象 主鍵主鍵Primary key約束約束 惟一值惟一值Unique約束約束 外鍵外鍵Foreign key約束約束 檢查檢查Check約束約束 默認(rèn)默認(rèn)D

5、efault約束約束定義PRIMARY KEY約束語(yǔ)法結(jié)構(gòu)：語(yǔ)法結(jié)構(gòu)：CREATE TABLE table_name(column_name data_type (NULL| NOT NULL) CONSTRAINT constraint_name PRIMARY KEY | unique, .n 注意注意: 每一個(gè)表只能定義一個(gè)每一個(gè)表只能定義一個(gè)PRIMARY KEY約束約束,主鍵列的輸入值必須是主鍵列的輸入值必須是唯一的唯一的,不允許空值不允許空值,該唯一性索引在默認(rèn)情況下為簇索引該唯一性索引在默認(rèn)情況下為簇索引,如果表中已如果表中已有簇索引應(yīng)先刪除再建立索引有簇索引應(yīng)先刪除再建立索引.

6、 create table st1 (sno varchar(8) primary key,sname varchar(10)問(wèn)題：?jiǎn)栴}：1、在哪種操作下驗(yàn)證？、在哪種操作下驗(yàn)證？2、INSERT數(shù)據(jù)后觀察數(shù)據(jù)的順序有什么特點(diǎn)數(shù)據(jù)后觀察數(shù)據(jù)的順序有什么特點(diǎn)定義UNIQUE約束 UNIQUE約束約束:指定表中某一個(gè)列不能有相同數(shù)據(jù)指定表中某一個(gè)列不能有相同數(shù)據(jù)的兩個(gè)行的兩個(gè)行. 當(dāng)表中已有主鍵值當(dāng)表中已有主鍵值,還要強(qiáng)調(diào)另外列的唯一性時(shí)還要強(qiáng)調(diào)另外列的唯一性時(shí),用用UNIQUE約束約束.例例:create table st2(number int primary key ,name varcha

7、r(8) null unique)name允許空值允許空值,一個(gè)表可以有多個(gè)一個(gè)表可以有多個(gè)UNIQUE 約束約束.ii）通過(guò)修改表時(shí)同時(shí)創(chuàng)建）通過(guò)修改表時(shí)同時(shí)創(chuàng)建primary key約束或約束或unique約束約束 語(yǔ)法格式：語(yǔ)法格式：ALTER TABLE table_nameADD CONSTRAINT constraint_name primary key | unique(column ,.n)參照完整性參照完整性 參照完整性是指在兩個(gè)表的主鍵和外鍵之間數(shù)據(jù)的完整參照完整性是指在兩個(gè)表的主鍵和外鍵之間數(shù)據(jù)的完整性，其含義包括：性，其含義包括：參照完整性保證被參照表和參照表之間數(shù)據(jù)的

8、一致性；參照完整性保證被參照表和參照表之間數(shù)據(jù)的一致性；可以防止數(shù)據(jù)丟失或者無(wú)意義的數(shù)據(jù)；可以防止數(shù)據(jù)丟失或者無(wú)意義的數(shù)據(jù)；可以禁止在從表中插入被參照表中不存在的關(guān)鍵字的可以禁止在從表中插入被參照表中不存在的關(guān)鍵字的記錄。記錄。 參照完整性的常見(jiàn)實(shí)現(xiàn)機(jī)制包括：參照完整性的常見(jiàn)實(shí)現(xiàn)機(jī)制包括：外鍵（外鍵（Foreign Key）、觸發(fā)器（）、觸發(fā)器（Trigger）；）；定義FOREIGN KEY約束 FOREIGN KEY 約束約束:參考同一個(gè)表或者另外參考同一個(gè)表或者另外一個(gè)表中的一個(gè)表中的PRIMARY KEY 或者或者UNIQUE約束。約束。 語(yǔ)法語(yǔ)法: :CONSTRAINT const

9、raint_name FOREIGN KEY (column,n) REFERENCES ref_table (ref_col,n)定義FOREIGN KEY約束Create table s1（sno char(9) primary key，sname）Create table c1（cno char(4) primary key，sname）Create table sc1（sno char(9) foreign key references s1(sno) ,cno char(4) foreign key references c1(cno) ,Grade smallint ）注意：建表的

10、順序以及如何合理的處理表與表間的數(shù)據(jù)關(guān)系注意：建表的順序以及如何合理的處理表與表間的數(shù)據(jù)關(guān)系(insert,update,delete時(shí)應(yīng)該注意什么？）時(shí)應(yīng)該注意什么？）外碼約束選項(xiàng)說(shuō)明 ON DELETE CASCADE：級(jí)聯(lián)刪除。當(dāng)刪除主表中的記：級(jí)聯(lián)刪除。當(dāng)刪除主表中的記錄時(shí)，如果在子表中有對(duì)這些記錄的值的引用，則一起錄時(shí)，如果在子表中有對(duì)這些記錄的值的引用，則一起刪掉；刪掉； ON DELETE NO ACTION：限制刪除。當(dāng)刪除主表中的：限制刪除。當(dāng)刪除主表中的記錄時(shí)，如果在子表中有對(duì)這些記錄的值的引用，則拒記錄時(shí)，如果在子表中有對(duì)這些記錄的值的引用，則拒絕刪除主表中的記錄；絕刪除

11、主表中的記錄； ON UPDATE CASCADE：級(jí)聯(lián)更新。當(dāng)更新主表中的：級(jí)聯(lián)更新。當(dāng)更新主表中的有子表引用的列的值時(shí)，如果在子表中有對(duì)這個(gè)列值的有子表引用的列的值時(shí)，如果在子表中有對(duì)這個(gè)列值的引用，則一起更改；引用，則一起更改； ON UPDATE NO ACTION：限制更新。當(dāng)更新主表中的：限制更新。當(dāng)更新主表中的有子表引用的列的值時(shí)，如果在子表中有對(duì)這個(gè)列值的有子表引用的列的值時(shí)，如果在子表中有對(duì)這個(gè)列值的引用，則拒絕更改主表中的記錄；引用，則拒絕更改主表中的記錄；定義FOREIGN KEY約束 CREATE TABLE sc2(sno char(9) not null ,cno

12、char(4) not null,grade int , primary key (sno,cno), foreign key(cno) references c1(cno) on d e l e t e c a s c a d e o n u p d a t e cascade,foreign key (sno) references s1(sno)on delete cascade on update cascade)注意：與上題處理方式的不同？注意：與上題處理方式的不同？定義FOREIGN KEY約束 在在FOREIGN KEY語(yǔ)句中列的數(shù)量和數(shù)據(jù)類(lèi)型必須和語(yǔ)句中列的數(shù)量和數(shù)據(jù)類(lèi)型必須和

13、REFERENCES子句中的列的數(shù)量和數(shù)據(jù)類(lèi)型匹配子句中的列的數(shù)量和數(shù)據(jù)類(lèi)型匹配. 不像不像PRIMARY KEY 或者或者UNIQUE 約束約束,FOREIGN KEY 約束不能自動(dòng)創(chuàng)建索引約束不能自動(dòng)創(chuàng)建索引. 參考同一個(gè)表中的列時(shí)參考同一個(gè)表中的列時(shí),必須只使用必須只使用REFERENCES 子句子句,不能使用不能使用FOREIGN KEY 子句子句. CREATE TABLE suc(number1 char(8) UNIQUE,number2 char(8) REFERENCES suc(number1)注意：注意：number2的數(shù)據(jù)只能取哪兩種？的數(shù)據(jù)只能取哪兩種？ ii）通過(guò)修

14、改表定義通過(guò)修改表定義Foreign key約束約束 語(yǔ)法格式：語(yǔ)法格式：ALTER TABLE table_nameADD constraint constraint_nameFOREIGN KEY (column,n)REFERNCES ref_table(ref_column ,n) on delete CASCADE | no action on update CASCADE | no action定義check約束i）創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建檢查）創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建檢查check約束約束語(yǔ)法格式：CREATE TABLE table_name(column_name data_type (NU

15、LL|NOT NULL) CONSTRAINT check_name CHECK (logical_expression)，n ）例例1：定義：定義student表，并限定表，并限定ssex只允許取只允許取男男或或女女；CREATE TABLE student（ Sno char(9) Primary Key,Sname char(8),Ssex char(2) check （ssex in （男男，女女） ，Sage smallint ，Sdept varchar(20)）定義約束練習(xí)：在定義練習(xí)：在定義Student中，要求男生的年齡在中，要求男生的年齡在1525歲之間，女生歲之間，女生的

16、年齡在的年齡在1524歲之間；歲之間；CREATE TABLE Student (Sno char(9) Primary Key,Sname char(8),ssex char(2), Sage smallint, Sdept varchar(20),CHECK(SAGE=15 AND ( (SSEX=男 AND SAGE=25) OR (SSEX=女 AND SAGE=24) )定義default約束默認(rèn)值約束的作用：就是當(dāng)用戶沒(méi)有為定義為默認(rèn)值約束的作用：就是當(dāng)用戶沒(méi)有為定義為default約束的字段輸入約束的字段輸入數(shù)據(jù)時(shí)數(shù)據(jù)時(shí), 由由default 約束提供默認(rèn)的數(shù)據(jù)。約束提供默認(rèn)的數(shù)

17、據(jù)。定義默認(rèn)值約束語(yǔ)法格式：定義默認(rèn)值約束語(yǔ)法格式：Create table table_name( column_name datatype not null / null Constraint constraint_name DEFAULT (constraint_expression),n-參數(shù)參數(shù)n表示可定義多個(gè)數(shù)據(jù)字段表示可定義多個(gè)數(shù)據(jù)字段)-定義列名、數(shù)據(jù)類(lèi)型、是否空值及默認(rèn)值約束定義列名、數(shù)據(jù)類(lèi)型、是否空值及默認(rèn)值約束定義約束 例例create table student(number char(8) primary key , name varchar(8), age int

18、CONSTRAINT aged DEFAULT 20 ,depa varchar(6), sex char(2) not null DEFAULT 女女)注意注意: 該約束只能用于該約束只能用于INSERT 語(yǔ)句語(yǔ)句,一個(gè)列只能定義一個(gè)一個(gè)列只能定義一個(gè)DEFAULT約束約束. ii）修改數(shù)據(jù)表時(shí)添加檢查）修改數(shù)據(jù)表時(shí)添加檢查check約束約束語(yǔ)法格式：語(yǔ)法格式：ALTER TABLE table_nameADD CONSTRAINT check_name - check約束名稱(chēng)約束名稱(chēng)CHECK (logical_expression)-check約束表達(dá)式約束表達(dá)式舉例CREATE TAB

19、LE 工作工作( 工作編號(hào)工作編號(hào) char(8) PRIMARY KEY, 最低工資最低工資 int , 最高工資最高工資 int, CHECK ( 最低工資最低工資 = 1000 ), 電話號(hào)碼電話號(hào)碼 char(8) NOT NULL UNIQUE )舉例CREATE TABLE Student ( Sno char(7) PRIMARY KEY, Sname char(10) NOT NULL, Ssex char(2), Sage tinyint check(sage=20 and sage=0 and grade20 and age=25) alter table student1

20、add constraint default1 default 男 for sex alter table sc1 add constraint f11-key foreign key (sno) references s(sno)在已有的表上創(chuàng)建約束注意：注意： 1、系統(tǒng)在執(zhí)行時(shí)先檢查表中原有的數(shù)據(jù)，如系統(tǒng)在執(zhí)行時(shí)先檢查表中原有的數(shù)據(jù)，如果原有的數(shù)據(jù)與現(xiàn)在要果原有的數(shù)據(jù)與現(xiàn)在要 增加的約束矛盾，要么修增加的約束矛盾，要么修改原有數(shù)據(jù)，要么不能增加現(xiàn)在的約束。改原有數(shù)據(jù)，要么不能增加現(xiàn)在的約束。 2、 如果要修改如果要修改 一個(gè)約束，應(yīng)該先刪除原來(lái)的一個(gè)約束，應(yīng)該先刪除原來(lái)的約束，約束， 命令

21、為：命令為： ALTER TABLE 表名表名 DROP constraint 約束約束名，再重新創(chuàng)建（增加）約束。名，再重新創(chuàng)建（增加）約束。規(guī)則RULE對(duì)象 規(guī)則：對(duì)錄入數(shù)據(jù)列中的數(shù)據(jù)所實(shí)施的完整性約束條件，規(guī)則：對(duì)錄入數(shù)據(jù)列中的數(shù)據(jù)所實(shí)施的完整性約束條件，它指定了插入到數(shù)據(jù)列中的可能值。它指定了插入到數(shù)據(jù)列中的可能值。 規(guī)則是規(guī)則是SQL Server 2005數(shù)據(jù)庫(kù)中獨(dú)立于表和視圖的數(shù)據(jù)數(shù)據(jù)庫(kù)中獨(dú)立于表和視圖的數(shù)據(jù)對(duì)象，與其作用的表是獨(dú)立的，刪除表時(shí)不會(huì)刪除規(guī)則。對(duì)象，與其作用的表是獨(dú)立的，刪除表時(shí)不會(huì)刪除規(guī)則。 規(guī)則就相當(dāng)于數(shù)據(jù)表的數(shù)據(jù)驗(yàn)收員，當(dāng)插入或修改的數(shù)規(guī)則就相當(dāng)于數(shù)據(jù)表的數(shù)據(jù)

22、驗(yàn)收員，當(dāng)插入或修改的數(shù)據(jù)放入數(shù)據(jù)表時(shí)，它就檢查數(shù)據(jù)是否符合要求，符合要據(jù)放入數(shù)據(jù)表時(shí)，它就檢查數(shù)據(jù)是否符合要求，符合要求的放入數(shù)據(jù)表，不符合的拒之門(mén)外。這樣就保證了放求的放入數(shù)據(jù)表，不符合的拒之門(mén)外。這樣就保證了放入數(shù)據(jù)表的數(shù)據(jù)是正確的。入數(shù)據(jù)表的數(shù)據(jù)是正確的。 如果列同時(shí)有與之相關(guān)聯(lián)的默認(rèn)值和規(guī)則，則默認(rèn)值必如果列同時(shí)有與之相關(guān)聯(lián)的默認(rèn)值和規(guī)則，則默認(rèn)值必須在規(guī)則定義的范圍內(nèi)。與規(guī)則沖突的默認(rèn)值永遠(yuǎn)不能須在規(guī)則定義的范圍內(nèi)。與規(guī)則沖突的默認(rèn)值永遠(yuǎn)不能被插入。被插入。定義規(guī)則對(duì)象 規(guī)則對(duì)象的使用方法與默認(rèn)值對(duì)象使用步驟類(lèi)型；規(guī)則對(duì)象的使用方法與默認(rèn)值對(duì)象使用步驟類(lèi)型；定義規(guī)則對(duì)象定義規(guī)則對(duì)象

23、將規(guī)則對(duì)象綁定到列或用戶自定義類(lèi)型將規(guī)則對(duì)象綁定到列或用戶自定義類(lèi)型 定義規(guī)則對(duì)象的語(yǔ)法結(jié)構(gòu)定義規(guī)則對(duì)象的語(yǔ)法結(jié)構(gòu)CREATE RULE rule AS condition_expression 將規(guī)則對(duì)象綁定到自定義類(lèi)型或列將規(guī)則對(duì)象綁定到自定義類(lèi)型或列語(yǔ)法格式：語(yǔ)法格式：sp_bindrule rulename = rule , objname = object_name , futureonly = futureonly_flag 規(guī)則對(duì)象例例1：創(chuàng)建一個(gè)規(guī)則，用于指定性別為：創(chuàng)建一個(gè)規(guī)則，用于指定性別為男男或者或者女女，并綁定到數(shù)據(jù)表，并綁定到數(shù)據(jù)表student的的ssex字段。字段

24、。 CREATE RULE ssex_ruleAS sex =男男 or sex =女女EXEC sp_bindrule ssex_rule , student.ssex例例2：創(chuàng)建一個(gè)規(guī)則，用于指定學(xué)號(hào)首位以：創(chuàng)建一個(gè)規(guī)則，用于指定學(xué)號(hào)首位以1-9開(kāi)頭，其他位以開(kāi)頭，其他位以0-9結(jié)尾，并綁定結(jié)尾，并綁定到數(shù)據(jù)表到數(shù)據(jù)表student的的sno字段。字段。CREATE RULE sno_ruleAS num like 1-90-90-9 0-9 0-9 0-9 0-9 0-9 0-9EXEC sp_bindrule sno_rule , student.sno2）刪除規(guī)則對(duì)象 刪除規(guī)則對(duì)象之

25、前，首先應(yīng)使用系統(tǒng)存儲(chǔ)過(guò)程刪除規(guī)則對(duì)象之前，首先應(yīng)使用系統(tǒng)存儲(chǔ)過(guò)程sp_unbindrule解除被綁定對(duì)象與規(guī)則對(duì)象之間的綁定關(guān)解除被綁定對(duì)象與規(guī)則對(duì)象之間的綁定關(guān)系。系。解除規(guī)則綁定語(yǔ)法格式解除規(guī)則綁定語(yǔ)法格式： sp_unbindrule objname = object_name , futureonly = futureonly_flag刪除規(guī)則語(yǔ)法格式刪除規(guī)則語(yǔ)法格式：DROP rule rule , n例1：解除規(guī)則對(duì)象解除規(guī)則對(duì)象sdept_rule與數(shù)據(jù)表與數(shù)據(jù)表student的的sdept字段的綁定，然后刪除規(guī)則對(duì)象字段的綁定，然后刪除規(guī)則對(duì)象sdept_rule;If exists (select name from sysobjects where name = sdept_rule and type=R）BeginExec sp_unbindrule student.sdeptDrop rule sdept_ruleEnd 創(chuàng)建及綁定DEFAULT對(duì)象 創(chuàng)建: C R E AT E D E FA U LT d e f a u l t _ n a m e A S constant_expression例：C