1、第七章,數(shù)據(jù)庫完整性,1,PPT學(xué)習(xí)交流,學(xué)習(xí)要點(diǎn),數(shù)據(jù)庫完整性概述 SQL2005完整性實(shí)現(xiàn) 觸發(fā)器 概述 創(chuàng)建、修改、查看、刪除 使用 說明 AFTER觸發(fā)器 INSTEAD OF觸發(fā)器,2,PPT學(xué)習(xí)交流,數(shù)據(jù)庫完整性概述,數(shù)據(jù)完整性 （Data Integrity） 定義 指數(shù)據(jù)的準(zhǔn)確性和一致性。 目的 防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯(cuò)誤 信息的輸入輸出造成無效操作或錯(cuò)誤信息 分類 實(shí)體完整性：用于保證數(shù)據(jù)庫中數(shù)據(jù)表的每一個(gè)特定實(shí)體的記 錄都是唯一的。 域完整性：用于保證指定列的數(shù)據(jù)具有正確的數(shù)據(jù)類型、格 式和有效的數(shù)據(jù)范圍。 參照完整性：確保同一鍵值在所有相關(guān)聯(lián)表之間都

2、一致。 用戶定義完整性 ：由用戶自定義的不屬于其他任何完 整性分類的特定業(yè)務(wù)規(guī)則。,3,PPT學(xué)習(xí)交流,數(shù)據(jù)庫完整性概述,數(shù)據(jù)完整性 （Data Integrity） 為維護(hù)數(shù)據(jù)庫的完整性，DBMS必須： 提供定義完整性約束條件的機(jī)制 提供完整性檢查的方法 違約處理,學(xué)習(xí)要點(diǎn),4,PPT學(xué)習(xí)交流,完整性實(shí)現(xiàn),SQL Server 2005完整性實(shí)現(xiàn) DEFAULT約束 CHECK約束 PRIMARY KEY約束 UNIQUE約束 FOREIGN KEY約束 NOT NULL約束 標(biāo)識(shí)列 默認(rèn)值 規(guī)則,學(xué)習(xí)要點(diǎn),5,PPT學(xué)習(xí)交流,約束,DEFAULT約束 定義 在用戶未輸入某些列的數(shù)據(jù)時(shí)，數(shù)據(jù)

3、庫系統(tǒng)為用戶提供的默認(rèn)值。 （默認(rèn)值必須與所約束列的數(shù)據(jù)類型相一致） 是可以避免不允許為空值的數(shù)據(jù)錯(cuò)誤 可以加快用戶的輸入速度 創(chuàng)建 SSMS方式 T-SQL方式,約束列表,6,PPT學(xué)習(xí)交流,使用SSMS工具,DEFAULT約束,DEFAULT約束,7,PPT學(xué)習(xí)交流,CREATE TABEL BookInfo ( b_ID VARCHAR(16), b_Detail VARCHAR(100) DEFAULT 暫無描述 ),使用T-SQL定義（一）,DEFAULT約束,CREATE TABEL 數(shù)據(jù)表名 ( 列名 數(shù)據(jù)類型 CONSTRAIN 約束名 DEFAULT 默認(rèn)值 ，. ),列級(jí)約

4、束,示 例,NEXT,8,PPT學(xué)習(xí)交流,CREATE TABEL BookInfo ( b_Detail VARCHAR(100)， CONSTRAINT de_Detail DEFAULT 暫無描述 FOR b_Detail ),使用T-SQL定義（二）,DEFAULT約束,NEXT,CREATE TABEL 數(shù)據(jù)表名 ( 列名1 數(shù)據(jù)類型， CONSTRAIN 約束名 DEFAULT 默認(rèn)值 FOR (列名 ,n) ,. ),表級(jí)約束,示 例,9,PPT學(xué)習(xí)交流,使用T-SQL定義(三）,DEFAULT約束,ALTER TABEL 數(shù)據(jù)表名 ( ADD CONSTRAIN 約束名 DEF

5、AULT 默認(rèn)值 FOR (列名 ,n) ，. ),修改表：添加約束,ALTER TABEL BookInfo ( ADD CONSTRAINT de_Detail DEFAULT 暫無描述 FOR b_Detail ),示 例,返回,10,PPT學(xué)習(xí)交流,約束,CHECK約束 定義 限制用戶輸入數(shù)據(jù)的取值范圍、格式等。 一個(gè)列可以定義多個(gè)CHECK 約束。 當(dāng)往表中插入記錄或修改相應(yīng)列的值時(shí)，系統(tǒng)檢查約束條件是否滿足，若不滿足則拒絕執(zhí)行數(shù)據(jù)操作。 創(chuàng)建 SSMS方式 T-SQL方式,約束列表,11,PPT學(xué)習(xí)交流,使用SSMS工具,CHEECK約束,CHECK約束,12,PPT學(xué)習(xí)交流,CR

6、EATE TABEL 數(shù)據(jù)表名 ( 列名 數(shù)據(jù)類型 CONSTRAIN 約束名 CHECK (邏輯表達(dá)式) ，. ),CREATE TABEL BookInfo ( b_Quantity SMALLINT CHECK (b_Quantity=1 AND b_Quantity =100), ),使用T-SQL定義（一）,CHECK 約束,列級(jí)約束,示 例,NEXT,13,PPT學(xué)習(xí)交流,使用T-SQL定義（二、三）,CHECK 約束,返回,CREATE TABEL 數(shù)據(jù)表名 ( 列名1 數(shù)據(jù)類型， CONSTRAIN 約束名 CHECK (邏輯表達(dá)式) ,. ),表級(jí)約束,ALTER TABEL

7、 數(shù)據(jù)表名 ( ADD CONSTRAIN 約束名 CHECK (邏輯表達(dá)式) ，. ),修改表：添加約束,14,PPT學(xué)習(xí)交流,約束,PRIMARY KEY約束 定義 若是單列作為主鍵，可以在列級(jí)定義或表級(jí)定義 若是列集合作為主鍵，必須在表級(jí)定義 一個(gè)表只能有一個(gè)PRIMARY KEY約束，受約束的列或列集合的值不能為空和重復(fù)，必須能唯一標(biāo)識(shí)表中的行。 定義了PRIMARY KEY 約束的列的列寬不能更改。 創(chuàng)建 SSMS方式 T-SQL方式,約束列表,15,PPT學(xué)習(xí)交流,使用SSMS工具,返回,PRIMARY約束,16,PPT學(xué)習(xí)交流,使用T-SQL定義（一）,NEXT,PRIMARY約

8、束,CREATE TABEL 數(shù)據(jù)表名 ( 列名 數(shù)據(jù)類型 CONSTRAIN 約束名 PRIMARY KEY ，. ),CREATE TABEL BookInfo ( b_ID VARCHAR(16) PRIMARY KEY， ),列級(jí)約束,示 例,默認(rèn)的約束名為：PK_BookInfo,17,PPT學(xué)習(xí)交流,使用T-SQL定義（二）,PRIMARY約束,CREATE TABEL 數(shù)據(jù)表名 ( 列名1 數(shù)據(jù)類型， CONSTRAIN 約束名 PRIMARY KEY (列名1,n) ,. ),CREATE TABEL BorrowReturn ( s_ID CHAR(8) NOT NULL,

9、r_ID CHAR(8) NOT NULL, PRIMARY KEY (s_ID,r_ID) ),表級(jí)約束,示 例,組合鍵必須在表級(jí)定義 默認(rèn)的約束名為： PK_ BorrowReturn,NEXT,18,PPT學(xué)習(xí)交流,使用T-SQL定義（三）,返回,PRIMARY約束,ALTER TABEL 數(shù)據(jù)表名 ( ADD CONSTRAIN 約束名 PRIMARY KEY( 列名, ) ，. ),ALTER TABLE BorrowReturn ADD CONSTRAINT PK_ BorrowReturn PRIMARY KEY(s_ID,r_ID),示 例,修改表：添加約束,19,PPT學(xué)習(xí)交

10、流,約束,UNIQUE約束 定義 也稱唯一值約束，確保在非主鍵列中不輸入重復(fù)值。 一個(gè)表可以定義多個(gè)UNIQUE 約束。 在允許空值的列上保證唯一性時(shí)，應(yīng)使用UNIQUE約束。 當(dāng)往表中插入記錄或修改相應(yīng)列的值時(shí)，系統(tǒng)檢查是否有重復(fù)值，若有則拒絕執(zhí)行數(shù)據(jù)操作。 創(chuàng)建 SSMS方式 T-SQL方式,約束列表,20,PPT學(xué)習(xí)交流,使用SSMS工具,返回,UNIQUE約束,21,PPT學(xué)習(xí)交流,使用T-SQL定義（一）,NEXT,UNIQUE約束,CREATE TABEL 數(shù)據(jù)表名 ( 列名 數(shù)據(jù)類型 CONSTRAIN 約束名 UNIQUE ，. ),CREATE TABEL ReaderTyp

11、e ( rt_ Name VARCHAR(10) UNIQUE , ),列級(jí)約束,示 例,22,PPT學(xué)習(xí)交流,使用T-SQL定義（二、三）,UNIQUE約束,返回,CREATE TABEL 數(shù)據(jù)表名 ( 列名1 數(shù)據(jù)類型， CONSTRAIN 約束名 UNIQUE (列名1,n) ,. ),表級(jí)約束,ALTER TABEL 數(shù)據(jù)表名 ( ADD CONSTRAIN 約束名 UNIQUE (列名1,n) ，. ),修改表：添加約束,23,PPT學(xué)習(xí)交流,約束,FOREIGN KEY約束 定義 也稱為外鍵約束，用于強(qiáng)制實(shí)現(xiàn)參照完整性，建立兩個(gè)表之間的關(guān)系。 可以規(guī)定表中的某列參照同一個(gè)表或另外一

12、個(gè)表中已有的PRIMARY KEY 約束或UNIQUE 約束的列。 一個(gè)表可以有多個(gè)FOREIGN KEY 約束。 創(chuàng)建 SSMS方式 T-SQL方式,約束列表,24,PPT學(xué)習(xí)交流,使用SSMS工具 Step1,NEXT,FOREIGN KEY約束,25,PPT學(xué)習(xí)交流,使用SSMS工具 Step2,NEXT,FOREIGN KEY約束,26,PPT學(xué)習(xí)交流,使用SSMS工具 Step3,NEXT,FOREIGN KEY約束,27,PPT學(xué)習(xí)交流,使用SSMS工具 Step4,返回,FOREIGN KEY約束,28,PPT學(xué)習(xí)交流,使用T-SQL定義（一）,示例,CREATE TABEL 數(shù)

13、據(jù)表名 ( 列名 數(shù)據(jù)類型 CONSTRAIN 約束名 FOREIGN KEY REFERENCES 參照主鍵表 (參照列) ON DELETE CASCADE | ON UPDATE CASCADE ，. ),列級(jí)約束,FOREIGN KEY約束,NO ACITON：默認(rèn)選項(xiàng)。將引發(fā)錯(cuò)誤，撤銷對(duì)父表的刪除（更新）操作 CASCADE：當(dāng)在主表刪除（更新）一行，則自動(dòng)將子表中的對(duì)應(yīng)行刪除（更新） SET NULL：刪除（更新）父表的一行，子表相應(yīng)行的外鍵值被置為NULL SET DEFAULT：刪除（更新）父表的一行，子表相應(yīng)行的外鍵值被置為 外鍵的默認(rèn)值,29,PPT學(xué)習(xí)交流,使用T-SQL

14、定義（一）,FOREIGN KEY約束,CREATE TABEL BookInfo ( b_ID VARCHAR(16) PRIMARY KEY, b_Name VARCHAR(50) NOT NULL, bt_ID CHAR(10) REFERENCES BookType(bt_ID), ),示 例,NEXT,30,PPT學(xué)習(xí)交流,使用T-SQL定義（二）,示例,CREATE TABEL 數(shù)據(jù)表名 ( 列名1 數(shù)據(jù)類型， CONSTRAIN 約束名 FOREIGN KEY(列名) REFERENCES 參照主鍵表 (參照列) ON DELETE CASCADE | ON UPDATE CAS

15、CADE ,. ),表級(jí)約束,FOREIGN KEY約束,31,PPT學(xué)習(xí)交流,使用T-SQL定義（二）,NEXT,FOREIGN KEY約束,CREATE TABLE BorrowReturn ( br_ID CHAR(6) PRIMARY KEY , s_ID CHAR(8) REFERENCES NOT NULL, r_ID CHAR(8) REFERENCES NOT NULL, FOREIGN KEY (s_ID) REFERENCES BookStore(s_ID) ， FOREIGN KEY (r_ID) REFERENCES ReaderInfo(r_ID) ),示 例,32,

16、PPT學(xué)習(xí)交流,使用T-SQL定義（三）,UNIQUE約束,返回,ALTER TABEL 數(shù)據(jù)表名 ( ADD CONSTRAIN 約束名 FOREIGN KEY( 列名 ) REFERENCES 參照主鍵表 (參照列) ON DELETE CASCADE | ON UPDATE CASCADE ，. ),修改表：添加約束,ALTER TABEL BookInfo ( ADD FOREIGN KEY (bt_ID) REFERENCES BookType(bt_ID) ON DELETE CASCADE ON UPDATE CASCADE , ),示 例,33,PPT學(xué)習(xí)交流,約束,NOT N

17、ULL約束 實(shí)質(zhì) 表中列可以定義為允許或不允許空值。 默認(rèn)情況下，創(chuàng)建表列允許空值。一個(gè)列中出現(xiàn)NULL 值，意味著用戶還沒有為該列輸入值。 若設(shè)置為NOT NULL，用戶沒有輸入該列值，則引發(fā)錯(cuò)誤。 示例 SSMS方式 T-SQL方式,約束列表,34,PPT學(xué)習(xí)交流,使用SSMS工具,返回,NOT NULL約束,35,PPT學(xué)習(xí)交流,使用T-SQL定義,NOT NULL 約束,CREATE TABEL 數(shù)據(jù)表名 ( 列名 數(shù)據(jù)類型 CONSTRAIN 約束名 NULL | NOT NULL ， ),表級(jí)約束,CREATE TABEL BookInfo ( b_ID varchar(16) P

18、RIMARY KEY, b_Name varchar(50) UNIQUE NOT NULL, bt_ID char(10) REFERENCES BookType(bt_ID), b_Author varchar(20) NOT NULL, b_Translator varchar(20) NULL, ),示 例,返回,36,PPT學(xué)習(xí)交流,約束,標(biāo)識(shí)列（IDENTITY） 概述 當(dāng)用戶向表中插入新的數(shù)據(jù)行時(shí)，系統(tǒng)自動(dòng)為該行的IDENTITY 列賦值，并保證其值在表中的唯一性。 常與PRIMARY KEY 約束一起使用。 每個(gè)表中只能有一個(gè)IDENTITY 列，其列值不能由用戶更新，也不允許

19、綁定默認(rèn)值或建立DEFAULT約束 標(biāo)識(shí)列的有效數(shù)據(jù)類型可以是任何整數(shù)數(shù)據(jù)類型分類的數(shù)據(jù)類型(bit 數(shù)據(jù)類型除外)，也可以是decimal 數(shù)據(jù)類型，但不允許出現(xiàn)小數(shù)。 創(chuàng)建 SSMS方式 T-SQL方式,約束列表,37,PPT學(xué)習(xí)交流,使用SSMS工具,返回,標(biāo)識(shí)列（IDENTITY）,38,PPT學(xué)習(xí)交流,使用T-SQL定義,返回,CREATE TABEL 數(shù)據(jù)表名 ( 列名 數(shù)據(jù)類型 IDENTITY (種子, 增量) ， ，. ),CREATE TABEL BorrowReturn ( br_ID INT IDENTITY( 1 ,1 ) PRIMARY KEY， ),語法,示 例,

20、標(biāo)識(shí)列（IDENTITY）,39,PPT學(xué)習(xí)交流,DEFAULT（默認(rèn)值）,概 述 “默認(rèn)值”是一種數(shù)據(jù)庫對(duì)象，可以被綁定到列。如果綁定的列或者數(shù)據(jù)類型沒有明確提供值，則將默認(rèn)指定的數(shù)據(jù)插入。 “默認(rèn)值”的執(zhí)行與DEFAULT 約束功能相同，但是DEFAULT 約束定義和表存儲(chǔ)在一起，當(dāng)除去表時(shí)，將自動(dòng)除去它。 當(dāng)在多個(gè)列中，特別是不同表中的列中多次使用同一默認(rèn)值時(shí)，適合采用“默認(rèn)”技術(shù)。,管理,40,PPT學(xué)習(xí)交流,管理“默認(rèn)值” 創(chuàng)建 綁定 解綁,示例,CREATE DEFAULT 默認(rèn)名稱 AS 常數(shù)表達(dá)式,sp_bindefault 默認(rèn)名稱, 數(shù)據(jù)表名.列名,sp_unbindefa

21、ult 數(shù)據(jù)表名.列名,DEFAULT（默認(rèn)值）,41,PPT學(xué)習(xí)交流,示例： 創(chuàng)建 綁定 解綁,返回,sp_bindefault d_Ssex , Student.Ssex,sp_unbindefault Student.Ssex,USE Study GO CREATE DEFAULT d_Ssex AS 女,DEFAULT（默認(rèn)值）,42,PPT學(xué)習(xí)交流,RULE（規(guī)則）,概 述 “規(guī)則”是一種數(shù)據(jù)庫對(duì)象，可以綁定到列，定義之后可以反復(fù)使用。 執(zhí)行“規(guī)則”功能與CHECK約束相同。 每個(gè)列只能有一個(gè)綁定的規(guī)則。但列可以同時(shí)具有規(guī)則和多個(gè)CHECK 約束。,約束列表,43,PPT學(xué)習(xí)交流,R

22、ULE（規(guī)則）,管理“規(guī)則” 創(chuàng)建 綁定 解綁,示例,CREATE RULE 規(guī)則名稱 AS 條件表達(dá)式,sp_bindrule 規(guī)則名, 數(shù)據(jù)表名.列名,sp_unbindrule 數(shù)據(jù)表名.列名,44,PPT學(xué)習(xí)交流,RULE（規(guī)則）,示例： 創(chuàng)建 綁定 解綁,返回,sp_bindrule r_Ssex , Student.Ssex,sp_unbindrule Student.Ssex,USE Study GO CREATE RULE r_Ssex AS sex=女 OR sex=男,45,PPT學(xué)習(xí)交流,觸發(fā)器,概述 觸發(fā)器 觸發(fā)器是特殊類型的存儲(chǔ)過程，主要由操作事件(INSERT、UP

23、DATE、DELETE) 觸發(fā)而被自動(dòng)執(zhí)行。 與表相關(guān)聯(lián)：必須定義在表上。 不能直接調(diào)用，也不能傳遞或接受參數(shù) 觸發(fā)器可以實(shí)現(xiàn)比約束更復(fù)雜的數(shù)據(jù)完整性，經(jīng)常用于 加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則。 觸發(fā)器本身是一個(gè)特殊的事務(wù)單位。,46,PPT學(xué)習(xí)交流,觸發(fā)器,概述 觸發(fā)器的類型 按觸發(fā)時(shí)刻分 AFTER觸發(fā)器 執(zhí)行觸發(fā)操作和處理完約束后激發(fā)，只能定義于表上，同一個(gè)表上可定義多個(gè)。 INSTEAD觸發(fā)器 由觸發(fā)器的程序代替觸發(fā)語句的執(zhí)行，在處理約束之前激發(fā)，可在表或視圖上創(chuàng)建，同一個(gè)表或視圖只可定義一個(gè)。 按觸發(fā)語句分 INSERT觸發(fā)器 UPDATE觸發(fā)器、 DELETE觸發(fā)器,47,PPT學(xué)

24、習(xí)交流,觸發(fā)器,Inserted表和deleted表 觸發(fā)器觸發(fā)時(shí)，系統(tǒng)自動(dòng)在內(nèi)存創(chuàng)建deleted表或inserted表。它們是只讀的，不允許修改；觸發(fā)器執(zhí)行完成后，自動(dòng)刪除。 inserted 表 臨時(shí)保存了插入或更新后的記錄行 可以從inserted表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求，如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾插入操作。 deleted 表 臨時(shí)保存了刪除或更新前的記錄行 可以從deleted表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求，如果不滿足，則向用戶報(bào)告錯(cuò)誤消息，并回滾插入操作。,48,PPT學(xué)習(xí)交流,觸發(fā)器,觸發(fā)器使用的限制 CREATE TRIGGER 必須是批處理中的

25、第一條語句。 在同一個(gè)表中約束的執(zhí)行優(yōu)先于觸發(fā)器，若在操作中觸發(fā)器與約束發(fā)生沖突，觸發(fā)器將不執(zhí)行。 不能在臨時(shí)表上建立觸發(fā)器，但是在觸發(fā)器定義中可以引用臨時(shí)表。 觸發(fā)器中不允許以下 T-SQL 語句：,49,PPT學(xué)習(xí)交流,觸發(fā)器,Inserted表和deleted表 inserted表和deleted表存放的信息,50,PPT學(xué)習(xí)交流,觸發(fā)器,創(chuàng)建（SSMS）,51,PPT學(xué)習(xí)交流,觸發(fā)器,創(chuàng)建（SSMS）,創(chuàng)建觸發(fā)器的模板，只需修改相應(yīng)的參數(shù)就可創(chuàng)建觸發(fā)器,52,PPT學(xué)習(xí)交流,觸發(fā)器,創(chuàng)建（TSQL） 基本語句格式,CREATE TRIGGER 觸發(fā)器名 ON 表名或視圖名 FOR |

26、AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL語句,53,PPT學(xué)習(xí)交流,觸發(fā)器,創(chuàng)建（TSQL） 基本語句格式,CREATE TRIGGER 觸發(fā)器名 ON 表名或視圖名 FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS IF UPDATE(列名1) AND|OR UPDATE(列名2) .n SQL語句,54,PPT學(xué)習(xí)交流,觸發(fā)器,創(chuàng)建（TSQL） 參數(shù)說明,AFTER:執(zhí)行指定操作語句和處理完約束后激發(fā)觸

27、發(fā)器。 FOR:如果僅指定 FOR 關(guān)鍵字，則 AFTER 是默認(rèn)設(shè)置。 INSTEAD OF:指定語句激發(fā)觸發(fā)器并被代替執(zhí)行。 DELETE、INSERT、UPDATE：指定激活觸發(fā)器的數(shù)據(jù)修改語句，必須至少指定一個(gè)選項(xiàng)，若指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。 AS：指定觸發(fā)器要執(zhí)行的操作。 IF UPDATE(列名1) AND|OR UPDATE(列名2):指定何列數(shù)據(jù)修改時(shí)激發(fā)觸發(fā)器。不能用于DELETE語句。,55,PPT學(xué)習(xí)交流,觸發(fā)器,創(chuàng)建（TSQL） 示例1,CREATE TRIGGER tr_addbook ON BookInfo FOR INSERT,UPDATE AS BEGI