數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)第五章課件

數(shù)據(jù)庫系統(tǒng)概論An

Introduction

to

Database

System第五章數(shù)據(jù)庫完整性東莞理工學(xué)院城市學(xué)院計(jì)算機(jī)與信息科學(xué)系第五章 數(shù)據(jù)庫完整性定義指數(shù)據(jù)的正確性和相容性目的為了防止不合語義的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫。現(xiàn)實(shí)世界的實(shí)例：學(xué)生的性別只能是男或女；本科生的年齡必須是整數(shù)，取值范圍為14－50；學(xué)生的學(xué)號(hào)一定是唯一的；學(xué)生所在的系必須是學(xué)校開設(shè)的系；第五章 數(shù)據(jù)庫完整性完整性和安全性的區(qū)別目的不同完整性是防止數(shù)據(jù)庫中存在不正確的數(shù)據(jù);安全性是防止數(shù)據(jù)庫被惡意破壞或非法存取。防范對(duì)象不同完整性是防范不合語義、不正確的數(shù)據(jù);安全性是防范非法用戶和非法操作。數(shù)據(jù)庫完整性完整性控制機(jī)制包括：完整性約束條件定義機(jī)制DBMS應(yīng)提供定義數(shù)據(jù)庫完整性約束條件，并把它們作為模式的一部分存入數(shù)據(jù)庫中完整性檢查的方法檢查用戶發(fā)出的操作請求是否違背了完整性約束條件，一般在：INSERT、UPDATE、DELETE語句執(zhí)行后以及事務(wù)提交時(shí)檢查。數(shù)據(jù)庫完整性違約處理如果發(fā)現(xiàn)用戶的操作請求使數(shù)據(jù)違背了完整性約束條件，則采取一定的動(dòng)作來保證數(shù)據(jù)的完整性，包括：拒絕（NO

ACTION）執(zhí)行

該操作，或級(jí)聯(lián)（CASCADE）執(zhí)行其他操作。第五章數(shù)據(jù)庫完整性實(shí)體完整性參照完整性用戶定義的完整性完整性約束命名子句觸發(fā)器小結(jié)5.1.1實(shí)體完整性定義·

在CREATE

TABLE中用PRIMARY

KEY定義，可以是列級(jí)約束條件也可以是表級(jí)約束條件，但有些時(shí)候只能定義表級(jí)約束條件。5.1.1實(shí)體完整性定義【例1】將Student表中的Sno屬性定義為碼CREATE

TABLE

Student

/*列級(jí)定義主碼*/(Sno CHAR(9)

PRIMARY

KEY,Sname

CHAR(20)

NOT

NULL,SsexSageSdeptCHAR(2),SMALLINT,CHAR(15));5.1.1實(shí)體完整性定義CREATE

TABLE

Student

/*表級(jí)定義主碼*/(Sno CHAR(9)

,Sname

CHAR(20)

NOT

NULL,SsexSageSdeptCHAR(2)

,SMALLINT,CHAR(15),PRIMARY

KEY

(Sno))；5.1.1實(shí)體完整性定義【例2】將SC表中的Sno，Cno屬性組定義為碼CREATE

TABLE

SC

/*只能在表級(jí)定義主碼*/(SnoCnoGradeCHAR(9)

NOT

NULL,CHAR(4)

NOT

NULL,SMALLINT,PRIMARY

KEY

(Sno,

Cno));5.1.2實(shí)體完整性檢查和違約處理檢查當(dāng)用戶程序?qū)颈聿迦胍粭l記錄或?qū)χ鞔a進(jìn)行更新操作時(shí)進(jìn)行檢查。包括：檢查主碼的值是否唯一，不唯一則拒絕操作；檢查主碼的各個(gè)屬性是否為空，只要有一個(gè)為空就拒絕操作。第五章數(shù)據(jù)庫完整性實(shí)體完整性參照完整性用戶定義的完整性完整性約束命名子句觸發(fā)器小結(jié)第五章數(shù)據(jù)庫完整性附：創(chuàng)建Course表的SQL語句CREATE

TABLE

Course(Cno

CHAR(4)

PRIMARY

KEY,Cname

CHAR(40),Cpno

CHAR(4),Ccredit

SMALLINT,FOREIGN

KEY

Cpno

REFERENCES

Course(Cno));5.2.1參照完整性定義·

在CREATE

TABLE中用FOREIGN

KEY定義哪些列為外碼，用REFERENCES短語指明這些外碼參照哪些表的主碼。5.2.1參照完整性定義【例3】定義SC中的參照完整性

CREATE

TABLE

SC(Sno

CHAR(9),

Cno

CHAR(4),Grade

SMALLINT,PRIMARY

KEY

(Sno,

Cno),FOREIGN

KEY

Sno

REFERENCES

Student(Sno),FOREIGN

KEY

Cno

REFERENCES

Course(Cno));5.2.2參照完整性檢查和違約處理檢查當(dāng)用戶程序?qū)Ρ粎⒄毡砗蛥⒄毡頃r(shí)進(jìn)行增刪改操作時(shí)進(jìn)行檢查?！?/p>

處理：對(duì)參照表的增、改外碼值時(shí)可能破壞參照完整性，則以拒絕操作處理。對(duì)被參照表進(jìn)行刪、修改主碼值時(shí)可能破壞參照完整性，則以拒絕/級(jí)聯(lián)刪除/設(shè)置為空值操作處理5.2.2參照完整性檢查和違約處理【注意】系統(tǒng)默認(rèn)的處理方式是拒絕處理，如果要讓系統(tǒng)采用其他策略，則要在創(chuàng)建表時(shí)顯式定義。5.2.2參照完整性檢查和違約處理【例4】顯式說明參照完整性的違約處理示例CREATE

TABLE

SC(Sno

CHAR(9)

,Cno

CHAR(4)

,Grade

SMALLINT,PRIMARY

KEY

(Sno,

Cno),FOREIGN

KEY

Sno

REFERENCES

Student(Sno)ON

DELETE

CASCADEON

UPDATE

CASCADE,FOREIGN

KEY

Cno

REFERENCES

Course(Cno)ON

DELETE

NO

ACTIONON

UPDATE

CASCADE,);當(dāng)刪除Student表中的元組時(shí)，級(jí)聯(lián)刪除SC表中相應(yīng)的元組第五章數(shù)據(jù)庫完整性實(shí)體完整性參照完整性用戶定義的完整性完整性約束命名子句觸發(fā)器小結(jié)5.3.1屬性上的約束條件的定義在CREATE

TABLE中根據(jù)需要定義，包括：列值非空（NOT

NULL）列值唯一（UNIQUE）檢查列值是否滿足一個(gè)布爾表達(dá)式（CHECK）5.3.1屬性上的約束條件的定義·

用CHECK短語指定列值應(yīng)該滿足的條件【例8】SC表的Grade的值應(yīng)該在0和100之間。CREATE

TABLE

SC(Sno

CHAR(9)

NOT

NULL,Cno

CHAR(4)

NOT

NULL,Grade

SMALLINTCHECK

(Grade>=0

AND

Grade<=100),PRIMARY

KEY

(Sno,

Cno),FOREIGN

KEY

Sno

REFERENCES

Student(Sno),FOREIGN

KEY

Cno

REFERENCES

Course(Cno));5.3.2屬性上的約束條件檢查和違約處理檢查當(dāng)往表中插入元組或修改屬性的值時(shí)進(jìn)行檢查?！?/p>

處理：不滿足則以拒絕操作處理。5.3.3元組上的約束條件的定義·

用CHECK短語指定表級(jí)應(yīng)該滿足的條件【例9】當(dāng)學(xué)生的性別是男時(shí)，其名字不能以Ms.打頭。CREATE

TABLE

Student(Sno CHAR(9)

，Sname

CHAR(20)

NOT

NULL，SsexSageSdeptCHAR(2)

，SMALLINT，CHAR(15)，PRIMARY

KEY

(Sno),CHECK

(Ssex=‘女’OR

Sname

NOT

LIKE‘Ms.%’))；5.3.4元組上的約束條件檢查和違約處理檢查當(dāng)往表中插入元組或修改屬性的值時(shí)進(jìn)行檢查?！?/p>

處理：不滿足則以拒絕操作處理。第五章數(shù)據(jù)庫完整性實(shí)體完整性參照完整性用戶定義的完整性完整性約束命名子句觸發(fā)器小結(jié)1完整性約束命名子句在CREATE

TABLE中提供了完整性約束命名子句：

CONSTRAINT。CONSTRAINT格式：·

CONSTRAINT

<完整性約束條件名>[PRIMARYKEY短語|FOREIGN

KEY短語|CHECK短語]1完整性約束命名子句【例10】建立學(xué)生登記表Student，要求學(xué)號(hào)在

20050001~20052999，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。

CREATE

TABLE

Student(Sno

NUMERIC(8)CONSTRAINT

C1

CHECK

(Sno

BETWEEN2005001

AND

20052999)，Sname

CHAR(20)CONSTRAINT

C2

NOT

NULL

，1完整性約束命名子句Ssex

CHAR(2)CONSTRAINT

C3

CHECK

(Ssex

IN(‘男’,’女’)),Sage

NUMERIC(3)CONSTRAINT

C4

CHECK

(Sage<30)),Sdept

CHAR(15)，CONSTRAINT

StudentKey

PRIMARY

KEY

(Sno))；1完整性約束命名子句【例11】建立教師表TEACHER，要求每個(gè)教師的應(yīng)發(fā)工資不低于3000元。CREATE

TABLE

TEACHER(EnoNUMERIC(4)PRIMARY

KEY,SnameCHAR(10),JobCHAR(8),SalNUMERIC(7,2),DeductDeptnoNUMERIC(7,2),NUMERIC(2),1完整性約束命名子句CONSTRAINT

EmpFKey

FOREIGN

KEY(Deptno)REFERENCES

DEPT(Deptno)，CONSTRAINT

C1

CHECK

(Sal+Deduct>=3000));2修改表中的完整性限制在ALTER

TABLE中修改表中完整性限制?？上葎h除原來的約束條件再增加新的約束條件。例子見課本P159例13。SQL

Server

2000中添加默認(rèn)值約束格式：ALTER

TABLE[用戶名].[表名]ADD

CONSTRAINT默認(rèn)值名DEFAULT‘具體值’FOR[字段名]刪除：ALTER

TABLE[用戶名].[表名]DROPCONSTRAINT默認(rèn)值名【例】:ALTER

TABLE

[dbo].[Student]

ADD

CONSTRAINTdf_Stu

DEFAULT‘計(jì)算機(jī)系’FOR

[Sdept];刪除：ALTER

TABLE

[dbo].[Student]

DROPCONSTRAINT

df_Stu;SQL

Server

2000中添加默認(rèn)值約束上節(jié)回顧第四章 數(shù)據(jù)庫安全性4.4審計(jì)、4.5數(shù)據(jù)加密、4.6統(tǒng)計(jì)數(shù)據(jù)庫安全性第五章 數(shù)據(jù)庫完整性5.1實(shí)體完整性、5.2參照完整性5.3用戶定義的完整性（CHECK）5.4完整性約束命名子句（

CONSTRAINT

）第五章數(shù)據(jù)庫完整性實(shí)體完整性參照完整性用戶定義的完整性完整性約束命名子句觸發(fā)器小結(jié)5.6觸發(fā)器觸發(fā)器(Trigger)是一種特殊類型的存儲(chǔ)過程。觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的。當(dāng)對(duì)某一表進(jìn)行諸如UPDATE、INSERT、

DELETE這些操作時(shí)，SQL

Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主碼和外碼所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。5.6.1定義觸發(fā)器·

格式：CREATE

TRIGGER

<觸發(fā)器名>{BEFORE|AFTER}<觸發(fā)事件>ON

<表名>

FOR

EACH{ROW|STATEMENT}[WHEN<觸發(fā)條件>]<觸發(fā)動(dòng)作體>5.6.1定義觸發(fā)器SQL2000中創(chuàng)建觸發(fā)器的一般格式：CREATE

TRIGGER

trigger_nameON

{

table

|

view

}{{

{

FOR

|

AFTER}

{

[

INSERT

]

[

,

]

[

UPDATE

]

}AS[

{

IF

UPDATE

(

column

)[

{

AND

|

OR

}

UPDATE

(

column

)

][

...n]

]sql_statement

[

...n

]}}5.6.1定義觸發(fā)器·

說明：創(chuàng)建表的用戶才可以創(chuàng)建此表的觸發(fā)器，一個(gè)表只能創(chuàng)建一定數(shù)量的觸發(fā)器。觸發(fā)器名可以包含模式名，也可以不包含模式名，同一模式下，觸發(fā)器名必須是唯一的，且觸發(fā)器名和<表名>必須在同一模式下。表名為觸發(fā)器的目標(biāo)表的名稱。5.6.1定義觸發(fā)器觸發(fā)事件可以是：INSERT、DELETE、UPDATE或上述事件的組合，另外，UPDATE后面還可以有OF<觸發(fā)列，…>，即進(jìn)一步指明修改哪些列時(shí)觸發(fā)器激活。觸發(fā)器類型分為行級(jí)觸發(fā)器(FOR

EACH

ROW)和語句級(jí)觸發(fā)器(FOR

EACH

STATEMENT)。5.6.1定義觸發(fā)器觸發(fā)器被激活時(shí)，只有當(dāng)觸發(fā)條件為真時(shí)，觸發(fā)動(dòng)作體才執(zhí)行；否則觸發(fā)動(dòng)作體不執(zhí)行。如果省略WHEN觸發(fā)條件，則觸發(fā)動(dòng)作體在觸發(fā)器被激活后立即執(zhí)行。觸發(fā)器動(dòng)作體可以是SQL過程塊或?qū)σ褎?chuàng)建的存儲(chǔ)過程的調(diào)用。5.6.1定義觸發(fā)器【例18】定義一個(gè)BEFORE行級(jí)觸發(fā)器，為教師表

Teacher定義完整性規(guī)則“教授的工資不得低于4000元，如果低于4000元，則自動(dòng)改為4000元”。CREATE

TRIGGER

Insert_Or_Update_SalBEFORE

INSERT

OR

UPDATE

ON

Teacher/*觸發(fā)事件是插入或更新操作*/FOR

EACH

ROW

/*行級(jí)觸發(fā)器*/AS

BEGIN

/*定義觸發(fā)動(dòng)作體，是PL/SQL過程塊*IF

(new.Job=‘教授’)AND

(new.Sal<4000)THENnew.Sal

:=4000;END

IF;END;SQL

Sever

2000中的實(shí)現(xiàn)CREATE

TRIGGER

Insert_Or_Update_SalON

Teacher

for

INSERT,UPDATEASdeclare

@sal

numeric(7)/*以下為聲明三個(gè)變量*/declare

@eno

numeric(4)declare

@job

char(8)select

@sal=Sal

from

inserted

/*以下從剛插入的記錄中保存三個(gè)字段值*/select

@eno=Eno

from

insertedselect

@job=Job

from

insertedIF

(@job="教授")and

(@sal<4000)BEGIN

/*先刪除剛剛插入的記錄，再重新插入，但要注意保存剛插入的記錄的其他字段的值*/select

@sal=4000delete

from

Teacher

where

Eno=@enoinsert

into

Teacher(Eno

,Ename

,Job

,Sal

,Deduct

,Deptno

)

select

EnoJob

,@sal

,Deduct

,Deptno

from

insertedENDSQL

Sever

2000中的實(shí)現(xiàn)5.6.2激活觸發(fā)器觸發(fā)器的執(zhí)行由觸發(fā)事件激活，由數(shù)據(jù)庫服務(wù)器自動(dòng)執(zhí)行。當(dāng)一個(gè)表上定義了多個(gè)觸發(fā)器時(shí)，其執(zhí)行順序是：執(zhí)行該表上的BEFORE觸發(fā)器；激活觸發(fā)器的SQL語句；執(zhí)行該表上的AFTER觸發(fā)器；對(duì)于同一表上的多個(gè)BEFORE（或AFTER）觸發(fā)器，則按照“誰先創(chuàng)建誰先執(zhí)行”原則執(zhí)行。5.6.2激活觸發(fā)器【例20】