第五部分?jǐn)?shù)據(jù)庫完整教學(xué)課件

1、第五章第五章 數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性數(shù)據(jù)庫完整性v 數(shù)據(jù)是數(shù)據(jù)庫的核心，數(shù)據(jù)的正確和完整與否將直接影響數(shù)據(jù)庫的使用。v 數(shù)據(jù)的完整性完整性和安全性安全性是兩個不同概念； 數(shù)據(jù)的安全性保護數(shù)據(jù)庫防止惡意的破壞和非法的存取防范對象：非法用戶和非法操作 數(shù)據(jù)的完整性防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，也就是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù)防范對象：不合語義的、不正確的數(shù)據(jù)本次課內(nèi)容本次課內(nèi)容一數(shù)據(jù)完整性概述二在sql server 2005中實現(xiàn)數(shù)據(jù)完整性三其他相關(guān)知識觸發(fā)器v1、dbms必須能夠提供 提供定義完整性約束條件的機制提供定義完整性約束條件的機制 完整性約束條件 = = 完整性規(guī)

2、則，由sql的ddl語句來實現(xiàn)； 提供完整性檢查的機制提供完整性檢查的機制 檢查數(shù)據(jù)是否滿足完整性約束條件的機制； 違約處理違約處理 若用戶操作違反完整性約束條件，采取一定的動作，如拒絕執(zhí)行（no action）、級聯(lián)執(zhí)行或其他操作；一、數(shù)據(jù)庫完整性概述一、數(shù)據(jù)庫完整性概述v2、數(shù)據(jù)庫完整性約束機制分類完整性約束條件作用的對象有關(guān)系、元組、列三種；可分為： 實體完整性實體完整性：實體完整性是為了保證表中的數(shù)據(jù)唯一； 參照完整性：參照完整性：參照完整性用于確保相關(guān)聯(lián)的表間的數(shù)據(jù)應(yīng)保持一致，避免因一個表的記錄修改，造成另一個表的內(nèi)容變?yōu)闊o效的值一般來說，參照完整性是通過外鍵和主鍵來維護的； 域完整

3、性：域完整性：域完整性可以保證數(shù)據(jù)的取值在有效的范圍內(nèi)； 用戶定義完整性：用戶定義完整性：由用戶自行定義的，不同于前面種的完整性，也可以說一種強制數(shù)據(jù)定義；1）實體完整性）實體完整性v 實體（entity）：是數(shù)據(jù)邏輯模型中的一個概念。現(xiàn)實世界是一組實體的基本對象和這些對象的聯(lián)系（relation）構(gòu)成的。在數(shù)據(jù)庫中，我們可以理解一條記錄就是一個實體。v 實體完整性：在現(xiàn)實世界中，任何一個實體都有區(qū)別于其他實任何一個實體都有區(qū)別于其他實體的特征體的特征，即在數(shù)據(jù)庫中，所有的記錄都應(yīng)該有惟一的標(biāo)識，這就是實體完整性的含義（規(guī)則2.1：實體完整性規(guī)則）。v 常見的實體完整性機制包括： 主鍵（pri

4、mary key）；惟一碼（unique）；2）參照完整性）參照完整性v 參照完整性是指在兩個表的主鍵和外鍵之間數(shù)據(jù)的完整性，其含義包括： 參照完整性保證被參照表和參照表之間數(shù)據(jù)的一致性； 可以防止數(shù)據(jù)丟失或者無意義的數(shù)據(jù)； 可以禁止在從表中插入被參照表中不存在的關(guān)鍵字的記錄。v 參照完整性的常見實現(xiàn)機制包括： 外鍵（foreign key）、檢查（check）、觸發(fā)器（trigger）；存儲過程（stored procedure）v 在輸入或刪除記錄時，可以用來保持所有表之間定義的關(guān)系，以確保鍵值在所有表中一致。v 關(guān)系模型的參照完整性定義 在create table中用foreign ke

5、y短語定義哪些列為外碼 用references短語指明這些外碼參照哪些表的主碼3）域完整性）域完整性v 域：指列（字段），所以域完整性就是指列的完整性；v 域完整性要求列（字段）的數(shù)據(jù)具有正確的數(shù)據(jù)類型、格式和有效的數(shù)值范圍。v 域完整性的常見實現(xiàn)機制包括： 默認(rèn)值（default） 檢查（check） 數(shù)據(jù)類型（data type） 規(guī)則（rule）4）用戶自定義完整性）用戶自定義完整性v 用戶定義的完整性就是針對某一具體應(yīng)用的數(shù)據(jù)必須滿足的語義要求；可直接由rdbms提供，而不必由應(yīng)用程序承擔(dān)；系統(tǒng)將實現(xiàn)數(shù)據(jù)完整性的要求直接定義在表上或列上。v 常見的實現(xiàn)機制包括： 規(guī)則（rule）、創(chuàng)建

6、數(shù)據(jù)表時的所有約束（constraint）、觸發(fā)器（trigger）、存儲過程（stored produre）；二、在二、在sql server 2005中實現(xiàn)數(shù)據(jù)完整性中實現(xiàn)數(shù)據(jù)完整性vsql server 2005提供了完善的數(shù)據(jù)完整性機制，主要包括約束、默認(rèn)值和規(guī)則3類； 創(chuàng)建及管理約束及約束對象 默認(rèn)值約束及默認(rèn)值對象 創(chuàng)建及管理規(guī)則對象1、創(chuàng)建及管理約束及約束對象、創(chuàng)建及管理約束及約束對象v 主鍵primary key約束v 惟一值unique約束v 外鍵foreign key約束v 檢查check約束1）創(chuàng)建及刪除主鍵約束及惟一值約束）創(chuàng)建及刪除主鍵約束及惟一值約束v單屬性構(gòu)成的碼

7、的兩種說明方法單屬性構(gòu)成的碼的兩種說明方法 ：定義為列級約束條件，定義為表級約束條件v多個屬性構(gòu)成的碼只有一種說明方法多個屬性構(gòu)成的碼只有一種說明方法：定義為表級約束條件創(chuàng)建約束方式：i.創(chuàng)建表時同時創(chuàng)建primary key約束或unique約束ii.修改表時同時創(chuàng)建primary key約束或unique約束i）創(chuàng)建表時同時創(chuàng)建）創(chuàng)建表時同時創(chuàng)建primary key約束或約束或unique約束約束v 語法結(jié)構(gòu)：create table table_name(column_name data_type (null| not null) constraint constraint_name

8、primary key | unique, .n例例1：定義數(shù)據(jù)表：定義數(shù)據(jù)表student時，為學(xué)號時，為學(xué)號sno字段創(chuàng)建主鍵約束，對字段創(chuàng)建主鍵約束，對姓名姓名sname字段定義惟一值約束；字段定義惟一值約束；方法1：在列級定義主碼use stucreate table student（sno char（9） constraint sno_pk primary key,sname char(8) constraint sname_uk unique,ssex char(2) ，sage smallint ，sdept varchar(20)）方法2：在表級定義主碼create table

9、 student (sno char(9) ， sname char(20) ， ssex char(2) ， sage smallint， sdept char(20)， primary key (sno) )實體完整性檢查：v 插入或?qū)χ鞔a列進行更新操作時，rdbms按照實體完整性規(guī)則自動進行檢查。包括： 1. 檢查主碼值是否唯一，如果不唯一則拒絕插入或修改 2. 檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改檢查方法：全表掃描、索引ii）通過修改表時同時創(chuàng)建）通過修改表時同時創(chuàng)建primary key約束或約束或unique約束約束v 語法格式：alter table tab

10、le_nameadd constraint constraint_name primary key | unique(column ,.n)例例1：對數(shù)據(jù)表：對數(shù)據(jù)表student學(xué)號學(xué)號sno字段添加主鍵約束，對學(xué)生姓名字段添加主鍵約束，對學(xué)生姓名添加惟一值約束；添加惟一值約束；alter table studentadd constraint sno_pk primary key snoalter table studentadd constraint sname_uk unique sname刪除刪除primary key約束或約束或unique約束約束v 語法格式：alter tabl

11、e table_namedrop constraint constraint_name , .n例例1：刪除：刪除student中的主鍵約束和惟一值約束中的主鍵約束和惟一值約束alter table studentdrop constraint sno_pk，sname_ukv 查看主鍵約束的定義文本exec sp_helptext v 查看表中主鍵約束exec sp_helpconstraint v 查看數(shù)據(jù)庫中所有主鍵約束select name from sysobjects where type=k備注： k：primary key或unique約束；f：foreign keyc：che

12、ck約束；d：默認(rèn)值；r：規(guī)則；tr：觸發(fā)器；u：用戶表； v：視圖2）創(chuàng)建及管理外鍵約束）創(chuàng)建及管理外鍵約束v 對于兩個相關(guān)聯(lián)的表（主表與從表）進行數(shù)據(jù)插入和刪除時，通過參照完整性保證他們之間數(shù)據(jù)的一致性。v 利用foreign key定義從表的外碼，primary key或者unique定義主表中的主碼或惟一碼（不允許為空），可實現(xiàn)主表與從表之間的參照完整性。v 定義表間參照關(guān)系：先定義主表主碼（或惟一碼），再對從表定義外碼約束?？赡芷茐膮⒄胀暾缘那闆r及違約處理被參照表（例如student）參照表（例如sc）違約處理可能破壞參照完整性 插入元組拒絕可能破壞參照完整性 修改外碼值拒絕刪除元

13、組 可能破壞參照完整性拒絕/級連刪除/設(shè)置為空值修改主碼值 可能破壞參照完整性拒絕/級連修改/設(shè)置為空值v 當(dāng)主碼外碼發(fā)生不一致時，系統(tǒng)可采取以下策略： 1. 拒絕(no action)執(zhí)行 默認(rèn)策略 2. 級聯(lián)(cascade)操作 3. 設(shè)置為空值（set-null） 對于參照完整性，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值i）創(chuàng)建表時同時定義foreign key約束v 語法格式：create table table_name(column_name data_type foreign keyrefernces ref_table(ref_column) on delete casc

14、ade | no action -cascade表示主從表刪除時一致，進行級聯(lián)刪除-no action 表示回滾主表的刪除操作,為默認(rèn)設(shè)置 on update cascade | no action-主從表更新時一致, .n )-n表示可定義多個字段例例1：在數(shù)據(jù)庫：在數(shù)據(jù)庫stu中創(chuàng)建主表中創(chuàng)建主表student和和course，主鍵分別為，主鍵分別為sno和和cno，創(chuàng)建從表，創(chuàng)建從表sc，sc.sno為外碼，與數(shù)據(jù)表為外碼，與數(shù)據(jù)表student主鍵主鍵對應(yīng)，當(dāng)對主表進行更新或刪除操作時，對從表采用級聯(lián)操作；對應(yīng)，當(dāng)對主表進行更新或刪除操作時，對從表采用級聯(lián)操作；o為外碼，與數(shù)據(jù)表為外碼

15、，與數(shù)據(jù)表course主鍵對應(yīng)，當(dāng)對主表進行更新主鍵對應(yīng)，當(dāng)對主表進行更新或刪除操作時，對從表采用或刪除操作時，對從表采用no action方式。方式。use stucreate table student（sno char(9) primary key，sname）create table course （cno char(4) primary key，sname）create table sc（sno char(9) foreign key references student(sno) on delete cascade on update cascade，cno char(4) for

16、eign key references course(cno) on delete no action on update no action ，grade smallint ）ii）通過修改表定義foreign key約束v 語法格式：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例例1：假設(shè)數(shù)據(jù)庫：假設(shè)數(shù)

17、據(jù)庫stu中中student和和course為主表，主鍵分別為為主表，主鍵分別為sno和和cno， sc為從表，將為從表，將sc.sno定義為外碼。定義為外碼。use stualter table scadd constraint sc_fk foreign key sno references student(sno) v iii）刪除表間的參照關(guān)系刪除表間的參照關(guān)系，刪除從表的外碼約束即可；語法格式：alter table table_namedrop constraint constraint_name ,niv）查看外鍵約束sp_helptext 、sp_helpconstraint、

18、select name from sysobjects where type=f3）創(chuàng)建及刪除檢查約束）創(chuàng)建及刪除檢查約束v 對插入列中的值進行限制，以強制域的完整性；v 該約束在執(zhí)行insert語句或update時起作用；v 使用該約束可實現(xiàn)當(dāng)用戶在向表中插入或更新數(shù)據(jù)時，由sql server檢查新行中的帶有該約束的列值使其必須滿足約束條件。v check約束作用域 單列：列級check約束 多列：表級check約束i）創(chuàng)建數(shù)據(jù)表時創(chuàng)建檢查）創(chuàng)建數(shù)據(jù)表時創(chuàng)建檢查check約束約束語法格式：create table table_name(column_name data_type (null

19、|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)）v 例例2：在定義：在定義student中，要求男生的年齡在中，要求男生的年齡在1525歲之間，女生的歲之間，女生的年齡在年齡在

20、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=0 and sage=100) 例例2：在表：在表student的列的列sno上添加一個上添加一個check約束，要求學(xué)號必約束，要求學(xué)號必須由須由9個數(shù)字字符組成，并且不等于個數(shù)字字符組成，并且不等于0000000000.alter ta

21、ble studentadd constraint sno_ckcheck （sno like 0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9 and sno000000000)iii）查看約束的定義信息）查看約束的定義信息v 查看約束的定義文本exec sp_helptext v 查看表中約束exec sp_helpconstraint v 查看數(shù)據(jù)庫中所有約束select name from sysobjects where type=c例1：查看表student上約束sno_ck的定義文本use stuexec sp_helptext sno_ckgo例2：查看數(shù)

22、據(jù)庫stu中所有的check約束 use stu select name from sysobjects where type=civ）刪除檢查check約束語法格式：alter table table_namedrop constraint check_name例1：刪除數(shù)據(jù)表student中sno字段的check約束alter table studentdrop constraint sno_ck2、默認(rèn)值約束、默認(rèn)值約束v 默認(rèn)值約束的作用：就是當(dāng)用戶沒有為定義為default約束的字段輸入數(shù)據(jù)時, 由default 約束提供默認(rèn)的數(shù)據(jù)。v 默認(rèn)值對象：作為一種單獨的數(shù)據(jù)庫對象，它是獨立

23、于表的，刪除表并不能刪除默認(rèn)值，而是需要使用drop default命令才能刪除。v 對于某些字段，可在程序中指定默認(rèn)值，以方便用戶，一個字段默認(rèn)值的建立可通過如下兩種方式實現(xiàn)： 在定義表或修改表時，定義默認(rèn)值約束； 先定義默認(rèn)值對象，然后將對象綁定到表的相應(yīng)字段；1）在表中定義及刪除默認(rèn)值約束）在表中定義及刪除默認(rèn)值約束v默認(rèn)值約束的定義 1）定義表時定義字段的默認(rèn)值約束 2）修改表時添加一個字段的同時定義相應(yīng)約束 3）對表中指定的列定義默認(rèn)值v默認(rèn)值約束的刪除i）定義表時定義字段的默認(rèn)值約束）定義表時定義字段的默認(rèn)值約束v 定義默認(rèn)值約束語法格式：create table table_na

24、me( column_name datatype not null / null constraint constraint_name default (constraint_expression),n-參數(shù)n表示可定義多個數(shù)據(jù)字段)-定義列名、數(shù)據(jù)類型、是否空值及默認(rèn)值約束例1：定義數(shù)據(jù)表student中字段ssex的默認(rèn)值為女；create table student (sno char(9) primary key， sname char(8) not null， ssex char(2) default (女) ， sage smallint， sdept char(20) )ii）修

25、改表時添加一個字段的同時定義相應(yīng)約束）修改表時添加一個字段的同時定義相應(yīng)約束v 修改默認(rèn)值約束語法格式：alter table table_name( add column column_name datatype not null | nullconstraint constraint_name -指定約束名 default (constraint_expression) with values -默認(rèn)值約束表達式 -with values 僅用于在對表添加新字段的情況下) 例2：在數(shù)據(jù)表student中添加字段入學(xué)日期ruxue_date，默認(rèn)值為當(dāng)前日期；alter table stud

26、entadd ruxue_date smalldatetime null constraint date default getdate() with valuesiii）對表中指定的列定義默認(rèn)值約束）對表中指定的列定義默認(rèn)值約束v 語法格式：alter table table_name( add constraint constraint_name -指定約束名 default constraint_expression -默認(rèn)值約束表達式for column)例3：為數(shù)據(jù)表sc中字段grade添加默認(rèn)值為0；alter table scadd constraint grade_defaul

27、tdefault 0for gradeiv）默認(rèn)值約束的刪除）默認(rèn)值約束的刪除v 語法格式：alter table table_namedrop constraint constraint_name -刪除指定約束名例1：刪除表sc中字段grade的默認(rèn)值；alter table scdrop constraint grade_default三、其他知識三、其他知識v規(guī)則v觸發(fā)器1、創(chuàng)建及管理規(guī)則對象、創(chuàng)建及管理規(guī)則對象v 規(guī)則：對錄入數(shù)據(jù)列中的數(shù)據(jù)所實施的完整性約束條件，它指定了插入到數(shù)據(jù)列中的可能值。v 規(guī)則是sql server 2005數(shù)據(jù)庫中獨立于表和視圖的數(shù)據(jù)對象，與其作用的表是獨

28、立的，刪除表時不會刪除規(guī)則。v 規(guī)則就相當(dāng)于數(shù)據(jù)表的數(shù)據(jù)驗收員，當(dāng)插入或修改的數(shù)據(jù)放入數(shù)據(jù)表時，它就檢查數(shù)據(jù)是否符合要求，符合要求的放入數(shù)據(jù)表，不符合的拒之門外。這樣就保證了放入數(shù)據(jù)表的數(shù)據(jù)是正確的。v 如果列同時有與之相關(guān)聯(lián)的默認(rèn)值和規(guī)則，則默認(rèn)值必須在規(guī)則定義的范圍內(nèi)。與規(guī)則沖突的默認(rèn)值永遠不能被插入。1）定義規(guī)則對象）定義規(guī)則對象v 規(guī)則對象的使用方法與默認(rèn)值對象使用步驟類型； 定義規(guī)則對象 將規(guī)則對象綁定到列或用戶自定義類型v 定義規(guī)則對象的語法結(jié)構(gòu)create rule rule as condition_expressionv 將規(guī)則對象綁定到自定義類型或列語法格式：sp_bind

29、rule rulename = rule , objname = object_name , futureonly = futureonly_flag 例例1：創(chuàng)建一個規(guī)則，用于指定性別為：創(chuàng)建一個規(guī)則，用于指定性別為男男或者或者女女，并綁定，并綁定到數(shù)據(jù)表到數(shù)據(jù)表student的的ssex字段。字段。 create rule ssex_ruleas sex =男 or sex =女exec sp_bindrule ssex_rule , student.ssex例例2：創(chuàng)建一個規(guī)則，用于指定學(xué)號首位以：創(chuàng)建一個規(guī)則，用于指定學(xué)號首位以1-9開頭，其他位以開頭，其他位以0-9結(jié)尾，并綁定到數(shù)據(jù)

30、表結(jié)尾，并綁定到數(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ī)則對象）刪除規(guī)則對象v 刪除規(guī)則對象之前，首先應(yīng)使用系統(tǒng)存儲過程sp_unbindrule解除被綁定對象與規(guī)則對象之間的綁定關(guān)系。解除規(guī)則綁定語法格式解除規(guī)則綁定語法格式： sp_unbindrule objname = object_name , futureonly = futureonly_flag刪除規(guī)則語法格式刪

31、除規(guī)則語法格式：drop rule rule , n例1：解除規(guī)則對象sdept_rule與數(shù)據(jù)表student的sdept字段的綁定，然后刪除規(guī)則對象sdept_rule;use stuif exists (select name from sysobjects where name = sdept_rule and type=r）beginexec sp_unbindrule student.sdeptdrop rule sdept_ruleend v對比： 約束:定義表中有效的數(shù)據(jù)，在事務(wù)處理前執(zhí)行，性能較好； 默認(rèn)和規(guī)則:提供獨立的可以與多個表聯(lián)系的對象實現(xiàn)數(shù)據(jù)完整性，在事務(wù)處理前執(zhí)行

32、2、觸發(fā)器、觸發(fā)器v 觸發(fā)器是一種特殊類型的存儲過程，只要對它所保護的數(shù)據(jù)進行修改，它就會自動觸發(fā)。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而被直接調(diào)用。v 一般情況下，對表數(shù)據(jù)的操作有插入、修改和刪除，對數(shù)據(jù)的更新、插入及刪除被視為數(shù)據(jù)修改事件。因而維護數(shù)據(jù)的觸發(fā)器也可分為3種類型：insert,update和delete。一個觸發(fā)器由三部分組成：v 事件事件。事件是指對數(shù)據(jù)庫的插入、刪除修改等操作。觸發(fā)器在這些事件發(fā)生時將開始工作。v 條件條件。觸發(fā)器將測試條件是否成立。如果條件成立，就執(zhí)行相應(yīng)的動作，否則什么也不作。v 動作動作。如果觸發(fā)器測試滿足預(yù)定的條件

33、，那么就由dbms執(zhí)行這些動作（即對數(shù)據(jù)庫的操作）。1）定義觸發(fā)器）定義觸發(fā)器v 語法格式：create trigger on for | after | instead of as - after用于說明觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，是默認(rèn)設(shè)置；- instead of指定用觸發(fā)器中的操作代替觸發(fā)語句的操作；- insert、delete、update；v 例例1：對于數(shù)據(jù)庫：對于數(shù)據(jù)庫stu，創(chuàng)建一個觸發(fā)器，創(chuàng)建一個觸發(fā)器update_student，當(dāng)用，當(dāng)用戶試圖向戶試圖向student表中添加或修改數(shù)據(jù)時，該觸發(fā)器向客戶端表中添加或修改數(shù)據(jù)時，該觸發(fā)器向客戶端顯示一條顯示一條“插入或更新了數(shù)據(jù)表插入或更新了數(shù)據(jù)表xs！”的消息的消息; use stuif exists (select name from sysobjectswhere name = update_student and type = tr)drop trigger update_studentcreate trigger update_student on student for insert, updateas print 插入或更新了數(shù)據(jù)表student！測試：向student表插入一條記錄結(jié)果：客戶端顯示插入或更新了數(shù)據(jù)表stu