第五部分數據庫完整教學課件

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

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

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

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

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

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

7、的兩種說明方法單屬性構成的碼的兩種說明方法 ：定義為列級約束條件，定義為表級約束條件v多個屬性構成的碼只有一種說明方法多個屬性構成的碼只有一種說明方法：定義為表級約束條件創(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 語法結構：create table table_name(column_name data_type (null| not null) constraint constraint_name

8、primary key | unique, .n例例1：定義數據表：定義數據表student時，為學號時，為學號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 插入或對主碼列進行更新操作時，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：對數據表：對數據表student學號學號sno字段添加主鍵約束，對學生姓名字段添加主鍵約束，對學生姓名添加惟一值約束；添加惟一值約束；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 查看數據庫中所有主鍵約束select name from sysobjects where type=k備注： k：primary key或unique約束；f：foreign keyc：che

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

13、組 可能破壞參照完整性拒絕/級連刪除/設置為空值修改主碼值 可能破壞參照完整性拒絕/級連修改/設置為空值v 當主碼外碼發(fā)生不一致時，系統(tǒng)可采取以下策略： 1. 拒絕(no action)執(zhí)行 默認策略 2. 級聯(lián)(cascade)操作 3. 設置為空值（set-null） 對于參照完整性，除了應該定義外碼，還應定義外碼列是否允許空值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 表示回滾主表的刪除操作,為默認設置 on update cascade | no action-主從表更新時一致, .n )-n表示可定義多個字段例例1：在數據庫：在數據庫stu中創(chuàng)建主表中創(chuàng)建主表student和和course，主鍵分別為，主鍵分別為sno和和cno，創(chuàng)建從表，創(chuàng)建從表sc，sc.sno為外碼，與數據表為外碼，與數據表student主鍵主鍵對應，當對主表進行更新或刪除操作時，對從表采用級聯(lián)操作；對應，當對主表進行更新或刪除操作時，對從表采用級聯(lián)操作；o為外碼，與數據表為外碼

15、，與數據表course主鍵對應，當對主表進行更新主鍵對應，當對主表進行更新或刪除操作時，對從表采用或刪除操作時，對從表采用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：假設數據庫：假設數

17、據庫stu中中student和和course為主表，主鍵分別為為主表，主鍵分別為sno和和cno， sc為從表，將為從表，將sc.sno定義為外碼。定義為外碼。use stualter table scadd constraint sc_fk foreign key sno references student(sno) v iii）刪除表間的參照關系刪除表間的參照關系，刪除從表的外碼約束即可；語法格式：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)當用戶在向表中插入或更新數據時，由sql server檢查新行中的帶有該約束的列值使其必須滿足約束條件。v check約束作用域 單列：列級check約束 多列：表級check約束i）創(chuàng)建數據表時創(chuàng)建檢查）創(chuàng)建數據表時創(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約束，要求學號必約束，要求學號必須由須由9個數字字符組成，并且不等于個數字字符組成，并且不等于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 查看數據庫中所有約束select name from sysobjects where type=c例1：查看表student上約束sno_ck的定義文本use stuexec sp_helptext sno_ckgo例2：查看數

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

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

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

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

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

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

28、立的，刪除表時不會刪除規(guī)則。v 規(guī)則就相當于數據表的數據驗收員，當插入或修改的數據放入數據表時，它就檢查數據是否符合要求，符合要求的放入數據表，不符合的拒之門外。這樣就保證了放入數據表的數據是正確的。v 如果列同時有與之相關聯(lián)的默認值和規(guī)則，則默認值必須在規(guī)則定義的范圍內。與規(guī)則沖突的默認值永遠不能被插入。1）定義規(guī)則對象）定義規(guī)則對象v 規(guī)則對象的使用方法與默認值對象使用步驟類型； 定義規(guī)則對象 將規(guī)則對象綁定到列或用戶自定義類型v 定義規(guī)則對象的語法結構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ī)則，用于指定性別為男男或者或者女女，并綁定，并綁定到數據表到數據表student的的ssex字段。字段。 create rule ssex_ruleas sex =男 or sex =女exec sp_bindrule ssex_rule , student.ssex例例2：創(chuàng)建一個規(guī)則，用于指定學號首位以：創(chuàng)建一個規(guī)則，用于指定學號首位以1-9開頭，其他位以開頭，其他位以0-9結尾，并綁定到數據

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

31、除規(guī)則語法格式：drop rule rule , n例1：解除規(guī)則對象sdept_rule與數據表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對比： 約束:定義表中有效的數據，在事務處理前執(zhí)行，性能較好； 默認和規(guī)則:提供獨立的可以與多個表聯(lián)系的對象實現(xiàn)數據完整性，在事務處理前執(zhí)行

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

33、，那么就由dbms執(zhí)行這些動作（即對數據庫的操作）。1）定義觸發(fā)器）定義觸發(fā)器v 語法格式：create trigger on for | after | instead of as - after用于說明觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，是默認設置；- instead of指定用觸發(fā)器中的操作代替觸發(fā)語句的操作；- insert、delete、update；v 例例1：對于數據庫：對于數據庫stu，創(chuàng)建一個觸發(fā)器，創(chuàng)建一個觸發(fā)器update_student，當用，當用戶試圖向戶試圖向student表中添加或修改數據時，該觸發(fā)器向客戶端表中添加或修改數據時，該觸發(fā)器向客戶端顯示一條顯示一條“插入或更新了數據表插入或更新了數據表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 插入或更新了數據表student！測試：向student表插入一條記錄結果：客戶端顯示插入或更新了數據表stu