复试-877学长数据库002课件0版

7

Constraints

and

TriggersA

constraint

is

a

expression

or

statement

storedinthe

database.A

constraint

is

an

active(主动性)element,

itexecute

when

a

certain

event

occurs

or

thedatabase

changes.SQL2

provides

integrity

constraints:–

key,

referential

integrity, constraints,

checkconstraints,

assertions(断言).SQL3

providestrigger(触发器)which

is

a

formofactive

element

that

is

called

into

play

oncertain

specified

events.Keys

and

Foreign

KeysKey

is

the

most

important

kind

ofconstraint.Each

table

may

have

several

sets

ofattributes

called

candidate

keys(候选键).Each

table

can

have

an

unique

primary

key.If

some

attribute

is

declared

to

be

Unique,then

it

is

a

candidate

key.Keys

and

Foreign

KeysThere

are

two

way

to

declare

a

primary

key

inSQL

statement.1.

We

may

declare

an

attribute

to

be

a

primary

keywhen

that

attribute

is

listed

in

the

relation

schema.Example:CREATE

TABLE

department(deptid

int

PRIMARY

KEY,name

char(40)

NOT

NULL,headerid

char(5)

NULL);7.1

Keys

and

Foreign

Keys2.

We

may

add

to

the

list

of

items

in

the

schema

anadditional

declaration

thatsays

a

particularattribute

or

set

of

attributes

forms

the

primary

key.Example:CREATE

TABLE

salesitem(ordernolinenoprodidint,int,char(6)NOT

NULL,unitprice decimal(8,

2)NOT

NULL,ty

int NOT

NULL,Primary

Key (orderno,lineno));7.1

Keys

and

Foreign

KeysThe

distinguish

and

relationship

betweenPrimary

Key

and

Unique.Any

two

tuples

in

the

relationcannot

agree

on

all

oftheattributesof

Primary

Key

or

Unique

attributes

set.Any

attempt

to

violate

the

rule

is

rejected

bythesystem.A

table

hasand

only

has

one

Primary

Key,while

itmayhas

any

number

of

Unique

declarations.An

Unique

attribute

can

be

NULL.Attributes

in

Primary

Key

arenot

allowed

to

haveNULL.7.1

Keys

and

Foreign

KeysWhich

operations

may

result

key

constraintviolations?–

Delete?

Insert?

Update?SQL

system

check

a

key

constraint

onlywhen

an

Insertion

or

Update

to

a

relationoccurs.Declaring

Foreign-Key

ConstraintsReferential

integrity

is

that

values

forcertain

attributes

must

make

sense.We

may

declare

an

attribute

orattributes

of

one

relation

to

be

a

ForeignKey,

referencing

some

attribute(s)

of

asecond

relation.Pay

attention

to

the

distinction

betweenreferenced

attribute(s)

and

referencingattribute(s).Declaring

Foreign-Key

ConstraintsThere

are

two

ways

to

declare

a

foreign

key.Follow

the

foreign

key’s

name

and

type

byREFERENCES

<referenced

table>

(<referencedattribute>)Follow

the

list

ofitems

byFOREIGN

KEY

<referencing

attributes>REFERENCES

<referenced

table>

(<referencedattribute>)WhereThe

referencedattributes

should

be

Primary

Key

orUnique

attribute

of

the

referenced

table.Declaring

Foreign-Key

ConstraintsExample:CREATE

TABLE

department(deptid

int

PRIMARY

KEY,name

char(40) NOT

NULL,headerid

char(5)

REFERENCES

salesman(empid));is

equivalent

toCREATE

TABLE

department(deptid

int

PRIMARY

KEY,name

char(40) NOT

NULL,headerid

char(5),FOREIGN

KEY

headeridREFERENCES

salesman(empid));Declaring

Foreign-Key

ConstraintsExample:CREATE

TABLE

Enrollment(Sno

CHAR(10) NOT

NULL,Cno

CHAR(6) NOT

NULL,score

INT,PRIMARY

KEY (Sno,

Cno),FOREIGN

KEY

Sno

References

Students(Sno)，FOREIGN

KEY

Cno

References

Courses(Cno));Declaring

Foreign-Key

ConstraintsCan

we

declare

a

table

to

reference

itself?Yes.Example:salesman(empid,

idno,

name,

managerid,

depid,

...)Can

we

declare

a

foreign

key

to

haveNULL

values?

Can

we

declare

a

foreignkey

to

have

NULL

values?Yes.7.1.2

Maintaining

Referential

IntegrityThere

are

three

alternatives

policy(策略)to

maintainreferential

integrity

in

the

face

of

modifications

to

thedatabase.Example:Department(deptid,

name,

headerid)Salesman(empid,idno,

name,

gender,

phone,

deptid)The

Restrict(限制)policy(default

policy):For

Salesman(referencing

table),

the

following

actionswill

berejected.Insert

a

new

Salesman

tuple

whose

deptid

value

is

notNULL

and

is

not

the

deptid

component

of

anyDepartment

tuple.Update

a

Salesman

tuple

to

change

the

deptidcomponent

to

anon-NULL

value

thatisnotthedeptidcomponent

of

any

Department

tuple.7.1.2

Maintaining

Referential

IntegrityExample:Department(deptid,

name,

headerid)Salesman(empid,

idno,

name,

gender,

phone,

deptid)The

Restrict(限制)policy(default

policy):For

Department(referenced

table),

the

following

actionswill

be

rejected.Delete

a

Department

tuple,

andits

deptid

componentappears

as

the

deptid

component

of

one

or

moreSalesman

tuples.Update

a

Department

tuple

in

a

way

that

changesthedeptid

value,and

theolddeptidis

the

value

of

deptidof

some

Salesman

tuples.7.1.2

Maintaining

Referential

IntegrityExample:Department(deptid,

name,

headerid)Salesman(empid,

idno,

name,

gender,

phone,

deptid)The

cascade(级联)policyFor

Department(referenced

table).When

we

deleteaDepartment

tuple,

then

thereferencing

tuple(s)

is

deleted

from

Salesman.Whenwe

update

a

Department

tuple

in

a

way

thatchanges

the

deptidvalue,

then

the

deptid

componentof

referencing

tuple(s)

in

Salesman

is

updated

bysystem.7.1.2

Maintaining

Referential

IntegrityExample:Department(deptid,

name,

headerid)Salesman(empid,

idno,

name,

gender,

phone,

deptid)The

set-NULL(置空)policyFor

Department(referenced

table).When

we

deleteaDepartment

tuple,

then

the

deptidcomponent

of

referencing

tuple(s)

are

set

NULL

inSalesman.Whenwe

update

a

Department

tuple

in

a

way

thatchanges

the

deptid

value,

then

the

deptid

componentof

referencing

tuple(s)

are

setNULL

in

Salesman.7.1.2

Maintaining

Referential

IntegrityGrammar:References

<referenced

table>

(list

of

attributes)

[Action]Action:

ON

{

Update|

Delete

}

{

Restrict

|

Cascade

|

Set

Null

}–

Note

that

Update

and

Delete

can

be

declared

differentpolicies

for

a

foreign

key.Example:CREATE

TABLE

department(deptid

int

PRIMARY

KEY,name

char(40) NOT

NULL,headerid

char(5),FOREIGN

KEY

headerid

References

salesman(empid)ONDELETE SET

NULLON

UPDATE

CASCADE);Keys

and

Foreign

KeysExercise:修改你的

馆数据库的表的SQL定义，说明必要的主键和外键，并说明参照完整性约束的

策略。7.2

Constraints

on

Attributes

and

TuplesLimit

the

values

that

may

appear

incomponents

for

some

attribute.Main

ideasNot-null

constraintsAttribute-Based

CHECK

ConstraintsTuple-Based

CHECK

ConstraintsNot-Null

ConstraintsTo

declare

an

attribute

is

NOT

NULLThe

constraint

isdeclaredby

the

keywords

NOTNULLfollowing

thedeclaration

of

the

attribute

ina

CREATE

TABLE

statement.The

default

is

NULL.The

effect

of

NOT

NULL.We

can

not

update

the

value

to

be

NULL.Whenwe

insert

a

tuple,

we

must

giveanonemptyvalue

for

the

attribute.We

can

not

use

the

Set-Null

policy.Note

that

primary

key

is

NOT

NULL.7.2.2

Attribute-Based

CHECK

ConstraintsHow

to

restrict

the

value

of

an

attribute

to

be

in

alimitedrange?Declare

CHECK(condition)

following

the

declaration

oftheattribute.The

grammar

of

condition

is

same

as

that

ofthecondition

in

WHERE

clauses.Example:

Make

any

component

ofdeptid

more

than

0

indepartment.CREATE

TABLE

department(deptid

int

CHECK(

deptid

>

0

),name

char(40) NOT

NULL,headerid

char(5),PRIMARY

KEY

(deptid),FOREIGN

KEY

headerid

References

salesman(empid));7.2.2

Attribute-Based

CHECK

ConstraintsExample:

Make

any

component

of

gender

either0(female)

or

1(male)

in

salesman(empid,

idno,name,

gender,

phone,

deptid).–

gender

INT

CHECK(

gender

IN

(0,

1))An

attribute-based

CHECK

constraint

is

checkedwhenever

any

tuple

gets

a

new

value

for

thisattribute

(updateor

insert).

Iftheconstraint

isviolated

by

the

new

value,

then

the

modification

isrejected.7.2.2

Attribute-Based

CHECK

ConstraintsA

CHECK

condition

may

include

attributes

ofother

relations.Example:Makeany

deptid

value

of

salesman(empid,

idno,name,

gender,

phone,

deptid)

be

a

value

of

primarykey

of

department.deptid

INT

CHECK(

deptid

IN(SELECT

deptid

FROM department)

)Note

that:

if

we

change

the

departmentrelation,this

change

is

invisible

to

the

above

CHECKconstraint

.7.2.3

Tuple-Based

CHECK

ConstraintsExample:

For

each

order

form,require

auditdate(审核日期)not

earlier

than

signdate

in

salesorder(orderno,signdate,auditdate,empid,cusid).CREATE

TABLE

salesorder("orderno"

int

PRIMARY

KEY,"signdate"

datetime NOT

NULL

DEFAULT

getdate(),"auditdate"

datetime,"empid""custid"char(5) NOT

NULL,char(4) NOT

NULL,FOREIGN

KEY

empid

References

salesman(empid),FOREIGNKEY

custid

References

customer(custid),CHECK

(auditdate

>=

signdate))

;7.2.3

Tuple-Based

CHECK

ConstraintsIf

a

constraint

on

a

tuple

involves

more

thanoneattribute

of

that

tuple,

then

it

must

be

written

as

atuple-based

constraint

.The

condition

of

a

tuple-based

CHECKconstraint

is

cheched

every

time

a

tuple

isinserted

into

R

andevery

time

a

tuple

of

Risupdated.If

the

condition

mentions

some

other

relation

in

asubquery,and

a

change

to

that

relation

causes

thecondition

to e

false

for

some

tuple

ofR,thecheck

does

not

inhibit(抑制)this

change.7.2.4

Comparison

of

Tuple-

and

Attribute-Based

ConstraintsThe

tuple-based

constraint

will

be

checked

morefrequently

than

the

attribute-based

constraint

–whenever

any

attribute

of

the

tuple

changes,rather

than

only

when

the

attribute

mentioned

inthe

constraint

changes.If

there

are

both

attribute-base

CHECKconstraints

and

tuple-based

CHECK

constraintsin

a

relation,

then

check

attribute-base

CHECKconstraints

ly.7.2

Constraints

on

Attributes

and

TuplesExercisesP324

Modification

of

ConstraintsName

your

constraintsExample：custid

INT

Constraint

CKcustid

Check(

custid

>

0

)And:orderno

INT

Constraint

PKorderno

PRIMARY

KEYAltering

Constraintson

TableExample：Alter

Table

salesorder

ADD

Constraint

CKauditdateCheck(

auditdate

>=

signdate

)；Or:Alter

Table

customer

DROP

Constraint

CKcustid；It

is

a

good

idea

to

give

each

of

your

constraints

a

name.7.4

AssertionsAssertions

are

part

of

the

databaseschema.–

An

Assertion

is

a

boolean-valued

SQLexpression

that

must

be

true

at

all

times.Creating

Assertions

(SQL

Server

不支持)Creating

AssertionsCREATE

ASSERTION <assertion-name>CHECK

(<condition>)Example：for

salesman(empid,

…,

deptid)CREATE

ASSERTION

ASdeptid

CHECK(

NOT

EXISTS(

Select

*

From

salesmanwhere

deptid NOT

IN(Select deptid

From

department

)));Creating

Assertions

(SQL

Server

不支持)Example：for

Movies(title,

year,

length,incolor,

studioName,

producerC#)CREATE

ASSERTION

SumLength

CHECK(

10000

>=

ALL(

Select

SUM(length) From

MoviesGroup

By

studioName

));Drop

an

AssertionDROP

ASSERTION <assertion-name>Let

assertion

A

about

relation

Employees(name,

dept,salary)

be

declared

by

thefollowing:CREATE

ASSERTION

A

CHECK

(

‘Toy’

IN

(SELECT

deptFROM

EmployeesGROUP

BY

deptHAVING

AVG(salary)>=50000));Which

of

the

following

best

describes

the

constraint

enforcedby

this

assertion?Every

employee

making

at

least

$50,000

must

be

in

the

ToyDepartment.Only

the

Toy

Department

may

have

a age

salary

of$50,000

or

more.The

average

salary

of

employees

in

the

ToyDepartment

isatleast

$50,000.The

average

salary

ineach

department

other

than

theToyDepartment

is

less

than

$50,000.7.5

Triggers(触发器)Triggers

are

part

of

the

database

schema.A

Trigger

is

a

series

of

actions

that

areassociated

with

certain

events,

such

asinsertions

into

a

particular

relation,

andthat

are

performed

whenever

these

eventsarise.A

trigger

is

a

database

object

stored

in

some

relation.

When

certain

event

happens,a

sequence

of

operations

is

triggered.Triggers,

sometimes

called

event-condition-action

rules

or

ECA

rules.7.5

Triggers(触发器)Trigger

are

only

awakened

when

certain

eventsoccur.

The

sorts

of

events

allowed

are

usuallyinsert,

delete,

or

update

to

a

particular

relation.Once

awakened

by

its

triggering

event,

thetriggertests

a

condition.

Ifthe

condition

does

nothold,then

nothing

else

associated

with

thetriggerhappens

in

response

tothis

event.Ifthe

condition

of

the

trigger

is

satisfied,

theaction

associated

with

the

trigger

is

performed

bytheDBMS.

A

possible

action

is

to

modify

theeffects

oftheevent in

some

way,

even

abortingthe

transaction

of

which

the

event

is

part.A

SQL

trigger

applies

to

theMovieExec(name,address,

cert#,

netWorth)CREATE

TRIGGER

NetWorthTriggerAFTER

UPDATE

OF

netWorth

ON

MovieExecREFERENCINGOLD

ROW

AS

OldTupleNEW

ROW

AS

NewTupleFOR

EACH

ROWWHEN

(OldTWorth

>NewTWorth)UPDATE

MovieExecSET netWorth

=

OldTWorthWHERE cert#

=

NewTuple.cert#;7.5.1

Triggers

in

SQL触发器名条件对每个修改可的以

的一数次据触发事件动作OLD

TABLE

AS

OldStuffNEW

TABLE

AS

NewStuffFOR

EACH

STATEMENTWHEN

(

500000

>

(

SELECT

AVG(netWorth)FROM

MovieExec

)

)BEGINDELETE

FROM

MovieExecWHERE (name,

address,

cert#,

netWorth)

IN

NewStuff;INSERT

INTO

MovieExec(SELECT

*

FROM OldStuff

);END;7.5.1

Triggers

in

SQLCREATE

TRIGGER

AvgNetWorthTriggerAFTER

UPDATE

OF

netWorth

ON

MovieExecREFERENCINGAFTER|

BEFOREUPDATE

|

INSERT

|

DELETE7.5.2

Triggers

in

SQL

ServerTriggers

in

SQL

Server

：CREATE

TRIGGER

trigger_nameON {

table

|

view

}{{

FOR

|

AFTER|

INSTEAD

OF

}{

[

DELETE

]

[

,

]

[

INSERT

]

[

,

]

[

UPDATE

]

}AS[

IF

UPDATE

(

column

)[

{

AND

|

OR

}UPDATE

(

column

)

]

[

...n

]

]sql_statement

[

...n

]}7.5.2

Triggers

in

SQL

Server其中：trigger_name：触发器名；table

|

view：表名或视图名；

AFTER：执行指定的操作后，激活触发器。不能在视图上定义AFTER

触发器；

INSTEADOF

：执行触发器而不执行触发的

SQL

语句，从而替代触发语句的操作；[DELETE][,][INSERT][,][UPDATE]：激活触发器的事件；AS：触发器要执行的操作；IF

UPDATE(column)：测试在指定的列上进行的INSERT

或UPDATE

操作，不能用于DELETE

操作。deleted

和inserted

是逻辑表，在结构上类似于定义触发器的表，用于保存用户操作删除的旧值或

的新值。7.5.2

Triggers

in

SQL

ServerExample：Salesman(

empid,

idno,

name,

gender,

phone,

deptid

)Department(

deptid,

name,

headerid

)要求：每个销售员只属于某一个部门；部门经理只能由本部门中某个销售员担任。问题：当Update

department

Set

headerid='A0044'Wheredeptid=2时，不能保证'A0044'在Salesman中是部门2的成员。当Update

salesman

Set

deptid=2

Where

empid='A0044'时，若'A0044'

原是部门1的经理，则不能保证Department

中部门1的headerid

自动置NULL。7.5.2

Triggers

in

SQL

Server第一个问题解决方法。在department

表中定义触发器：CREATE

TRIGGER

update_header