资源描述:
《数据库系统英第三版课件衷宜2011版 constraints(7).ppt》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、Chapter7ConstraintsandTriggers第7章约束与触发器7ConstraintsandTriggersAconstraintisarelationshipamongdataelementsthattheDBMSisrequiredtoenforce.aexpressionorstatementstoredinthedatabase.anactiveelement,itexecutewhenacertaineventoccursorthedatabasechanges.Example:keyconstraints.Triggersar
2、eonlyexecutedwhenaspecifiedconditionoccurs,e.g.,insertionofatuple.Easiertoimplementthancomplexconstraints.7ConstraintsandTriggersSQL2providesintegrityconstraints:key,referentialintegrity,domainconstraints,CHECKconstraints,assertionsSQL3providestriggerwhichisaformofactiveelementth
3、atiscalledintoplayoncertainspecifiedevents.7.1KeysandForeignKeysKeyisthemostimportantkindofconstraint.Eachtablemayhaveseveralsetsofattributescalledcandidatekeys.Eachtablecanhaveanuniqueprimarykey.IfsomeattributeisdeclaredtobeUnique,thenitisacandidatekey.Referentialintegrityisthat
4、valuesforcertainattributesmustmakesense.7.1.1DeclaringKeysHowtodeclareaprimarykey?TherearetwowaytodeclareaprimarykeyinSQLstatement.1.Wemaydeclareanattributetobeaprimarykeywhenthatattributeislistedintherelationschema.Example:CREATETABLEdepartment(deptidintegerPRIMARYKEY,namechar(4
5、0)NOTNULL,headeridchar(10)NULL);7.1.1DeclaringKeys2.Wemayaddtothelistofitemsintheschemaanadditionaldeclarationthatsaysaparticularattributeorsetofattributesformstheprimarykey.Example:CREATETABLEsalesitem(ordernochar(10),linenochar(4),meridchar(6)NOTNULL,unitpricefloatNOTNULL,quant
6、ityintNOTNULL,PrimaryKey(orderno,lineno));ThewayofGUIismoreconvenient.7.1.2KeysDeclaredWithUNIQUECREATETABLESalesman(empidchar(10)PRIMARYKEY,IDNochar(18)UNIQUE,namechar(10)NOTNULL,…);7.1.2KeysDeclaredWithUNIQUEThedistinguishandrelationshipbetweenPrimaryKeyandUnique.Atablehasandon
7、lyhasaprimarykey,whileitmayhasanynumberofUniquedeclarations.Aprimarykeycanbeasetofattributes,whileanUniquedeclarationisforoneattribute.Ifaprimarykeyhasonlyoneattribute,thentheattributeisunique.Ifaprimarykeyhasmorethanoneattribute,thennoneoftheattributesisunique.7.1.3EnforcingKeyC
8、onstraintsWhichoperationsmayresultkeycon