吉林大学大型数据库设计与实现dba-13.ppt

MaintainingDataIntegrity

ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:ImplementdataintegrityconstraintsMaintainintegrityconstraintsObtainconstraintinformationfromthedatadictionary

DataIntegrityApplication

codeTableDataIntegrity

constraintDatabase

trigger

TypesofConstraintsConstraint

NOTNULL

UNIQUE

PRIMARYKEY

FOREIGNKEY

CHECK

Description

Specifiesthatacolumncannotcontainnullvalues

Designatesacolumnorcombinationofcolumnsasunique

Designatesacolumnorcombinationofcolumnsasthetable’sprimarykey

Designatesacolumnorcombinationofcolumnsastheforeignkeyinareferentialintegrityconstraint

Specifiesaconditionthateachrowofthetablemustsatisfy

ConstraintStatesENABLE

NOVALIDATEENABLE

VALIDATEExistingdataNewdataDISABLE

NOVALIDATEDISABLE

VALIDATE==

ConstraintCheckingDMLstatementChecknondeferredconstraintsCOMMITCheckdeferredconstraints

DefiningConstraintsas

ImmediateorDeferredUsetheSETCONSTRAINTSstatementtomakeconstraintseitherDEFERREDorIMMEDIATETheALTERSESSIONstatementalsohasclausestoSETCONSTRAINTStoDEFERREDorIMMEDATE

PrimaryandUniqueKeyEnforcementIsanindex

available

foruse?YesNoNoYesYesNoCreatenonunique

indexCreateunique

indexDonotuse

indexUseexisting

indexKeyenabled?Constraint

deferrable?NoYesConstraintDeferrable?Istheindexnon-unique?No

ForeignKeyConsiderationsDesiredActionAppropriateSolutionDropparenttableCascadeconstraintsTruncateparenttableDisableordropforeignkeyDroptablespacecontainingparenttableUsetheCASCADECONSTRAINTSclausePerformDMLonchildtableEnsurethetablespacecontainingtheparentkeykeyisonline

DefiningConstraintsWhile

CreatingaTableCREATETABLEhr.employee(

idNUMBER(7)

CONSTRAINTemployee_id_pkPRIMARYKEY

DEFERRABLE

USINGINDEX

STORAGE(INITIAL100KNEXT100K)

TABLESPACEindx,last_nameVARCHAR2(25)

CONSTRAINTemployee_last_name_nnNOTNULL,dept_i