1、數(shù)據(jù)庫技術(shù)與應(yīng)用,袁寶庫 ,實(shí)現(xiàn)數(shù)據(jù)完整性約束,數(shù)據(jù)完整性基本概念 實(shí)現(xiàn)聲明完整性 實(shí)現(xiàn)過程完整性,數(shù)據(jù)完整性基本概念,數(shù)據(jù)的完整性是為了防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)。 這些加在數(shù)據(jù)庫數(shù)據(jù)之上的語義約束條件就是數(shù)據(jù)完整性約束條件。 這些約束條件作為表定義的一部分存儲在數(shù)據(jù)庫中。 DBMS檢查數(shù)據(jù)是否滿足完整性條件的機(jī)制就稱為完整性檢查。,完整性約束條件的作用對象,列級約束 對數(shù)據(jù)類型的約束 對數(shù)據(jù)格式的約束 對取值范圍或取值集合的約束 對空值的約束 元組約束 元組中各個字段之間的聯(lián)系的約束，如：開始日期小于結(jié)束日期。 關(guān)系約束 是若干元組之間、關(guān)系之間的聯(lián)系的約束。,實(shí)現(xiàn)數(shù)據(jù)完整性的方法,

2、一種是在定義表時(shí)聲明數(shù)據(jù)完整性，稱為聲明完整性， 另一種是在服務(wù)器端編寫觸發(fā)器來實(shí)現(xiàn)，稱為過程完整性。 在執(zhí)行對數(shù)據(jù)的增、刪、改操作時(shí)，數(shù)據(jù)庫管理系統(tǒng)自動檢查用戶定義的完整性約束條件。,實(shí)現(xiàn)聲明完整性,主碼約束 每個表只能有一個PRIMARY KEY約束； 用PRIMARY KEY約束的列取值不能有重復(fù)，而且不允許有空值； 添加主碼約束的語法格式： ALTER TABLE 表名 ADD CONSTRAINT 約束名 PRIMARY KEY （ , n ）,實(shí)現(xiàn)聲明完整性,例：對雇員表和工作表添加主碼約束 ALTER TABLE 雇員表 ADD CONSTRAINT PK_EMP PRIMARY

3、 KEY （雇員編號） ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY （工作編號）,UNIQUE約束,用于限制在一個列中不能有重復(fù)的值。 用在事實(shí)上具有惟一性的屬性列上，比如每個人的身份證號碼、駕駛證號碼等均不能有重復(fù)值。 注意： 允許有一個空值； 在一個表中可以定義多個UNIQUE約束； 可以在一個列或多個列上定義UNIQUE約束。,添加UNIQUE約束,添加UNIQUE約束的語法格式為： ALTER TABLE 表名 ADD CONSTRAINT 約束名 UNIQUE（ , n ） 例為雇員表的“電話”列添加UNIQUE約束。 ALTE

4、R TABLE 雇員表 ADD CONSTRAINT UK_SID UNIQUE（電話）,外碼約束,實(shí)現(xiàn)引用完整性。 外碼所引用的列必須是有PRIMARY KEY約束或UNIQUE約束的列。 添加FOREIGN KEY約束的語法格式為： ALTER TABLE 表名 ADD CONSTRAINT 約束名 FOREIGN KEY（） REFERENCES 引用表名（）,示例,例為雇員表的工作編號添加外碼引用約束。 ALTER TABLE 雇員 ADD CONSTRAINT FK_job_id FOREIGN KEY （工作編號）REFERENCES 工作表 （工作編號）,DEFAULT約束,用于

5、提供列的默認(rèn)值。 只有在向表中插入數(shù)據(jù)時(shí)才檢查DEFAULT約束。 添加DEFAULT約束的語法格式為： ALTER TABLE 表名 ADD CONSTRAINT 約束名 DEFAULT 默認(rèn)值 FOR 列名 例定義雇員表的工資的默認(rèn)值為1000。 ALTER TABLE 雇員 ADD CONSTRAINT DF_SALARY DEFAULT 1000 FOR 工資,CHECK約束,用于限制列的取值在指定的范圍內(nèi)，使數(shù)據(jù)庫中存放的值都是有意義的。 系統(tǒng)在執(zhí)行INSERT語句和UPDATE語句時(shí)自動檢查CHECK約束。 CHECK約束可約束同一個表中多個列之間的取值關(guān)系。 添加CHECK約束的

6、語法格式為： ALTER TABLE 表名 ADD CONSTRAINT 約束名 CHECK （邏輯表達(dá)式）,示例,限制雇員的工資必須大于等于200。 ALTER TABLE 雇員 ADD CONSTRAINT CHK_Salary CHECK ( 工資 = 200 ) 限制工資表的最低工資小于等于最高工資。 ALTER TABLE 工作 ADD CONSTRAINT CHK_Job_Salary CHECK( 最低工資 = 最高工資 ),實(shí)現(xiàn)過程完整性,過程完整性是指在服務(wù)器端通過編寫實(shí)現(xiàn)約束的一段代碼來實(shí)現(xiàn)數(shù)據(jù)完整性約束，這段代碼就稱為觸發(fā)器。 觸發(fā)器是用編程的方法實(shí)現(xiàn)復(fù)雜的商業(yè)規(guī)則，它可

7、以實(shí)現(xiàn)一般的數(shù)據(jù)完整性約束實(shí)現(xiàn)不了的復(fù)雜的完整性約束。,事務(wù)的基本概念,事務(wù) 事務(wù)的特征 SQL事務(wù)處理模型,事務(wù),事務(wù)是用戶定義的數(shù)據(jù)操作系列，這些操作作為一個完整的工作單元，一個事務(wù)內(nèi)的所有語句被作為一個整體，要么全部執(zhí)行，要么全部不執(zhí)行。 例如：對于一個轉(zhuǎn)帳活動：A帳戶轉(zhuǎn)帳給B帳戶n元錢，這個活動包含兩個動作： 第一個動作：A帳戶 n 第二個動作：B帳戶 n,事務(wù)的特征,原子性（Atomicity） ：指事務(wù)是數(shù)據(jù)庫的邏輯工作單位，事務(wù)中的操作要么都做，要么都不做。 一致性（Consistency） ：指事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)。 隔離性（Isol

8、ation） ：指數(shù)據(jù)庫中一個事務(wù)的執(zhí)行不能被其它事務(wù)干擾。 持久性（Durability） ：也稱為永久性指事務(wù)一旦提交，則其對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久的。,事務(wù)的特征,保證事務(wù)的ACID特性是事務(wù)處理的重要任務(wù)。事務(wù)的ACID特性可能遭到破壞的因素有： 多個事務(wù)并行運(yùn)行時(shí)，不同事務(wù)的操作有交叉情況； 事務(wù)在運(yùn)行過程中被強(qiáng)迫停止。,SQL事務(wù)處理模型,隱式事務(wù)：隱式事務(wù)是每一條數(shù)據(jù)操作語句都自動地成為一個事務(wù)。 顯式事務(wù)：有顯式的開始和結(jié)束標(biāo)記的事務(wù)。 ISO事務(wù)處理模型 T-SQL事務(wù)處理模型,ISO事務(wù)處理模型,明尾暗頭事務(wù)的開頭是隱含的，事務(wù)的結(jié)束有明確標(biāo)記 A事務(wù)結(jié)束符 COMMI

9、T：事務(wù)成功結(jié)束符， ROLLBACK：事務(wù)失敗結(jié)束符， B事務(wù)提交方式 自動提交：每條SQL語句為一個事務(wù) 指定位置提交：在事務(wù)結(jié)束符或程序正常結(jié)束處提交,ISO事務(wù)處理模型,明尾暗頭事務(wù)的開頭是隱含的，事務(wù)的結(jié)束有明確標(biāo)記 C事務(wù)起始/終止位置 程序的首條SQL語句或事務(wù)結(jié)束符后的語句。 在程序正常結(jié)束處或COMMIT語句處成功終止； 在程序出錯處或或ROLLBACK處失敗終止。,示例,UPDATE 支付表 SET 帳戶總額 帳戶總額 n WHERE 帳戶名 A UPDATE 支付表 SET 帳戶總額 帳戶總額 n WHERE 帳戶名 B COMMIT,T-SQL事務(wù)處理模型,每個事務(wù)都有

10、顯式的開始和結(jié)束標(biāo)記。 事務(wù)的開始標(biāo)記是： BEGIN TRANSACTION | TRAN 事務(wù)的結(jié)束標(biāo)記為： COMMIT TRANSACTIONTRAN ROLLBACK TRANSACTIONTRAN,示例,例如前邊的轉(zhuǎn)帳例子用Transact-SQL事務(wù)處理模型描述為： BEGIN TRANSACTION UPDATE 支付表 SET 帳戶總額 帳戶總額 n WHERE 帳戶名 A UPDATE 支付表 SET 帳戶總額 帳戶總額 n WHERE 帳戶名 B COMMIT,觸發(fā)器,是一種特殊的存儲過程， 不需要由用戶調(diào)用執(zhí)行，而是當(dāng)用戶對表中的數(shù)據(jù)進(jìn)行UPDATE、INSERT或DE

11、LETE操作時(shí)自動觸發(fā)執(zhí)行的。 觸發(fā)器通常用于保證業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，其主要優(yōu)點(diǎn)是用戶可以用編程的方法來實(shí)現(xiàn)復(fù)雜的處理邏輯和商業(yè)規(guī)則，增強(qiáng)了數(shù)據(jù)完整性約束的功能。,觸發(fā)器的優(yōu)點(diǎn),完成比CHECK約束更復(fù)雜的數(shù)據(jù)約束。 為保證數(shù)據(jù)庫性能而維護(hù)的非規(guī)范化數(shù)據(jù)。 可實(shí)現(xiàn)復(fù)雜的商業(yè)規(guī)則。 觸發(fā)器也可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。,創(chuàng)建觸發(fā)器,語法格式： CREATE TRIGGER 觸發(fā)器名稱 ON 表名 FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE AS SQL 語句 . n ,兩個邏輯工作表,DELETED表存儲DELET

12、E和UPDATE語句所影響的行的副本。 INSERTED表存儲INSERT和UPDATE語句所影響的行的副本。,示例,創(chuàng)建限制最低工資必須大于等于400的觸發(fā)器。 CREATE TRIGGER tri_job_salary1 ON 工作表 FOR INSERT, UPDATE AS IF EXISTS( SELECT * FROM INSERTED WHERE 最低工資 400 ) BEGIN PRINT 最低工資必須大于等于400 ROLLBACK END,示例,創(chuàng)建實(shí)現(xiàn)限制最低工資必須小于最高工資的觸發(fā)器。 CREATE TRIGGER tri_job_salary2 ON 工作表 FOR

13、 INSERT, UPDATE AS IF EXISTS(SELECT * FROM INSERTED WHERE 最低工資 = 最高工資 ) BEGIN PRINT 最低工資必須小于最高工資 ROLLBACK END,示例,創(chuàng)建實(shí)現(xiàn)限制雇員的工資必須在工作表的相應(yīng)工作的最低工資和最高工資之間。 CREATE TRIGGER tri_emp_salary ON 雇員表 FOR INSERT, UPDATE AS IF EXISTS (SELECT * FROM INSERTED a JOIN 工作表 b ON a.工作編號 = b.工作編號 WHERE 工資 NOT BETWEEN 最低工資

14、AND 最高工資 ) ROLLBACK,限制更新數(shù)據(jù)的觸發(fā)器,限制將SC表中不及格學(xué)生的成績改為及格。 CREATE TRIGGER tri_grade ON SC FOR UPDATE AS IF UPDATE(Grade) IF EXISTS(SELECT * FROM INSERTED JOIN DELETED ON INSERTED.Sno = DELETED.Sno WHERE INSERTED.Grade = 60 AND DELETED.Grade 60) ROLLBACK,限制刪除的觸發(fā)器,限制刪除SC表中成績不及格學(xué)生的修課記錄。 CREATE TRIGGER tri_del_grade ON SC FOR DELETE AS IF EXISTS(SELECT *