A first course in database systems(7).ppt

7ConstraintsandTriggersinSQL Aconstraintisaexpressionorstatementstoredinthedatabase Aconstraintisanactive 主动性 element itexecutewhenacertaineventoccursorthedatabasechanges SQL2providesintegrityconstraints key referentialintegrity domainconstraints checkconstraints assertions 断言 SQL3providestrigger 触发器 whichisaformofactiveelementthatiscalledintoplayoncertainspecifiedevents 7 1KeysandForeignKeys Keyisthemostimportantkindofconstraint Eachtablemayhaveseveralsetsofattributescalledcandidatekeys 候选键 Eachtablecanhaveanuniqueprimarykey IfsomeattributeisdeclaredtobeUnique thenitisacandidatekey 7 1 1DeclaringPrimaryKeys TherearetwowaytodeclareaprimarykeyinSQLstatement 1 Wemaydeclareanattributetobeaprimarykeywhenthatattributeislistedintherelationschema Example CREATETABLEdepartment deptidintegerPRIMARYKEY namechar 40 NOTNULL headeridchar 10 NULL 7 1 1DeclaringPrimaryKeys 2 Wemayaddtothelistofitemsintheschemaanadditionaldeclarationthatsaysaparticularattributeorsetofattributesformstheprimarykey Example CREATETABLEsalesitem ordernochar 10 linenochar 4 prodidchar 6 NOTNULL unitpricefloatNOTNULL quantityintNOTNULL PrimaryKey orderno lineno 7 1 2KeysDeclaredWithUnique ThedistinguishandrelationshipbetweenPrimaryKeyandUnique Atablehasandonlyhasaprimarykey whileitmayhasanynumberofUniquedeclarations AnUniquedeclarationcanbeasetofattributes andanUniqueattributecanbenull Ifaprimarykeyhasonlyoneattribute thentheattributeisunique Ifaprimarykeyhasmorethanoneattribute thennoneoftheattributesisunique 7 1 3EnforcingKeyConstraints Whichoperationsmayresultkeyconstraintviolations Delete Insert Update SQLsystemcheckakeyconstraintonlywhenaninsertionorupdatetoarelationoccurs 7 1 4DeclaringForeign KeyConstraints Referentialintegrityisthatvaluesforcertainattributesmustmakesense Wemaydeclareanattributeorattributesofonerelationtobeaforeignkey referencingsomeattribute s ofasecondrelation Payattentiontothedistinctionbetweenreferencedattribute s andreferencingattribute s 7 1 4DeclaringForeign KeyConstraints Therearetwowaystodeclareaforeignkey Followtheforeignkey snameandtypebyREFERENCES FollowthelistofitemsbyFOREIGNKEYREFERENCES WhereThereferencedattributesshouldbeprimarykeyoruniqueattributeofthereferencedtable 7 1 4DeclaringForeign KeyConstraints Example CREATETABLEdepartment deptidintegerPRIMARYKEY namechar 40 NOTNULL headeridchar 10 REFERENCESsalesman empid isequivalenttoCREATETABLEdepartment deptidintegerPRIMARYKEY namechar 40 NOTNULL headeridchar 10 FOREIGNKEYheaderidREFERENCESsalesman empid 7 1 4DeclaringForeign KeyConstraints Example CREATETABLEElective SnoCHAR 10 NOTNULL CnoCHAR 6 NOTNULL ScoreINT PRIMARYKEY Sno Cno FOREIGNKEYSnoReferencesStudent Sno FOREIGNKEYCnoReferencesCourse Cno 7 1 4DeclaringForeign KeyConstraints Canwedeclareatabletoreferenceitself Yes Example salesman empid idno name managerid depid CanwedeclareaforeignkeytohaveNULLvalues Yes 7 1 5MaintainingReferentialIntegrity Howtomaintainreferentialintegrityinthefaceofmodificationstothedatabase Therearethreealternativespolicy 策略 TakeexampleforSalesorder orderno custid referencingCustomer custid theRestrict 限制 policy defaultpolicy ForSalesorder referencingtable thefollowingactionswillberejected InsertanewSalesordertuplewhosecustidvalueisnotNULLandisnotthecustidcomponentofanyCustomertuple UpdateaSalesordertupletochangethecustidcomponenttoanon NULLvaluethatisnotthecustidcomponentofanyCustomertuple ForCustomer referencedtable thefollowingactionswillberejected DeleteaCustomertuple anditscustidcomponentappearsasthecustidcomponentofoneormoreSalesmantuples UpdateaCustomertupleinawaythatchangesthecustidvalue andtheoldcustidisthevalueofcustidofsomeorderform 7 1 5MaintainingReferentialIntegrity thecascade 级联 policyWhenwedeleteaCustomer referencedtable tuple thenthereferencingtuple s isdeletedfromSalesorder referencingtable WhenweupdateaCustomertupleinawaythatchangesthecustidvalue thenthecustidcomponentofreferencingtuple s inSalesorderisupdatedbysystem 7 1 5MaintainingReferentialIntegrity theset NULL 置空 policyTakeexampleforDepartment deptid headerid referencingSalesman empid Firstly headeridcanbesetNULL WhenwedeleteaSalesman referencedtable tuple thentheheaderidcomponentofreferencingtuplesaresetnullinDepartment refrencingtable WhenweupdateaSalesmantupleinawaythatchangestheempidvalue thentheheaderidcomponentofreferencingtuplesaresetnullinDepartment refrencingtable 7 1 5MaintainingReferentialIntegrity Grammar references listofattributes Action Action ON Update Delete Restrict Cascade SetNull NotethatUpdateandDeletecanbedeclareddifferentpoliciesforaforeignkey Example CREATETABLEdepartment deptidintegerPRIMARYKEY namechar 40 NOTNULL headeridchar 10 FOREIGNKEYheaderidReferencessalesman empid ONDELETESETNULLONUPDATECASCADE 7 2ConstraintsonAttributesandTuples Limitthevaluesthatmayappearincomponentsforsomeattribute MainideasNot nullconstraintsAttribute BasedCHECKConstraintsTuple BasedCHECKConstraints 7 2 1Not NullConstraints HowtodeclareanattributeisNOTNULL TheconstraintisdeclaredbythekeywordsNOTNULLfollowingthedeclarationoftheattributeinaCREATETABLEstatement ThedefaultisNULL TheeffectofNOTNULL wecannotupdatethevaluetobeNULL whenweinsertatuple wemustgiveanonemptyvaluefortheattribute wecannotusetheset nullpolicy NotethatprimarykeyisNOTNULL 7 2 2Attribute BasedCHECKConstraints Howtorestrictthevalueofanattributetobeinalimitedrange DeclareCHECK condition followingthedeclarationoftheattribute ThegrammarofconditionissameasthatoftheconditioninWHEREclauses Example Makeanycomponentofcustidmorethan0incustomer custid custidINTCHECK custid 0 Example Makeanycomponentofgendereither0 female or1 male insalesman empid gender genderINTCHECK genderIN 0 1 Anattribute basedCHECKconstraintischeckedwheneveranytuplegetsanewvalueforthisattribute Iftheconstraintisviolatedbythenewvalue thenthemodificationisrejected 7 2 2Attribute BasedCHECKConstraints Whenanattribute basedCHECKconstraintischecked Anupdateorainsertisexecutedforthetable NotethataCHECKconditionmayincludeattributesofotherrelations Example Makeanydeptidvalueofsalesman empid deptid beavalueofprimarykeyofdepartment deptidINTCHECK deptidIN SELECTdeptidFROMdepartment 7 2 3Tuple BasedCHECKConstraints Example Foreachtuple requireaudit 审核 datenotearlierthansigndateinsalesorder orderno signdate auditdate Addthefollowingconstraint CHECK auditdate signdate Whencheck Insertorupdate Notethatiftherearebothattribute baseCHECKconstraintsandtuple basedCHECKconstraintsinarelation thencheckattribute baseCHECKconstraintsfirstly 7 2ConstraintsonAttributesandTuples ExercisesP3327 2 2 7 3ModificationofConstraints 定义表中的约束时 可以用Constraint短语给约束命名 例如 custidINTConstraintCKcustidCheck custid 0 或ordernoINTConstraintPKordernoPRIMARYKEY可以使用AlterTable指令增加或删除约束 例如 AlterTablesalesorderADDConstraintCKauditdateCheck auditdate signdate 或AlterTablecustomerDROPConstraintCKcustid 没有命名的约束会由系统自动命名 7 4AssertionsandTriggers 断言和触发器是数据库模式的一部分 断言 是SQL逻辑表达式 任何引起断言条件为假的操作均被拒绝 触发器 是一系列与某个事件相关的动作 当事件发生时 动作被执行 7 4 1Assertions SQLServer不支持 断言可用于限定不同表的元组或属性间应满足的相关条件 断言Assertion的建立 CREATEASSERTIONCHECK 例如 要求salesman empid deptid 表的deptid属性值必须是department表的键值 CREATEASSERTIONASdeptidCHECK NOTEXISTS Select FromsalesmanwheredeptidNOTIN SelectdeptidFromdepartment 7 4 2Triggers 触发器 基于特定事件触发的特定的约束检验 一个触发器trigger是存储在某个表中的一个命名的数据库对象 当该表进行某种数据更新时 将自动触发一组SQL语句的执行 触发器基于所谓 事件Event 条件Condition 动作Action 规则 即ECA规则 Event事件 对特定表的数据更新指令 insert delete updateCondition条件 当事件发生后 检查条件是否满足 若不满足 则不执行动作而状态转移 若满足 则执行一组动作之后状态转移 Action动作 一组SQL指令 动作也可用于撤销事件 7 4 3TriggersinSQLServer 不同的DBMS 其触发器的定义语句不同 SQLServer中触发器定义语句的简单格式为 CREATETRIGGERtrigger nameON table view FOR AFTER INSTEADOF DELETE INSERT UPDATE AS IFUPDATE column AND OR UPDATE column n sql statement n 7 4 3TriggersinSQLServer 其中 trigger name 触发器名 table view 表名或视图名 AFTER 执行指定的操作后 激活触发器 不能在视图上定义AFTER触发器 INSTEADOF 执行触发器而不执行触发SQL语句 从而替代触发语句的操作 DELETE INSERT UPDATE 激活触发器的事件 AS 触发器要执行的操作 IFUPDATE column 测试在指定的列上进行的INSERT或UPDATE操作 不能用于DELETE操作 deleted和inserted是逻辑表 在结构上类似于定义触发器的表 用于保存用户操作删除的旧值或插入的新值 7 4 3TriggersinSQLServer 实例 Salesman empid idno name gender phone deptid Department deptid name headerid 要求 每个销售员只属于某一个部门 部门经理只能由本部门中某个销售员担任 仅靠外键建立的参照完整性 不能解决的问题 当updatedepartmentsetheaderid wheredeptid 时 不能保证新headerid在Salesman中是本部门成员 当updatesalesmansetdeptid whereempid 时 若该销售员是部门经理 而要改变其所在部门时 不能保证Department中该部门的headerid自动置空值NULL 7 4 3TriggersinSQLServer 第一个问题解决方法 在department表中定义触发器 CREATETRIGGERupdate headeridONdepartmentFORUPDATEASIFUPDATE headerid Beginupdatessets deptid i deptidfromsalesmans insertediwheres empid i headeridEnd执行 updatedepartmentsetheaderid A0044 wheredeptid 2 时 自动执行 updatesalesmansetdeptid 2whereempid A0044 7 4 3TriggersinSQLServer 第二个问题解决方法 在salesman表中定义触发器 CREATETRIGGERupdate deptidONsalesmanAFTERUPDATEASIF UPDATE deptid ANDExists select fromdepartmentdep deleteddelwheredep headerid del empid beginupdatedepsetdep headerid nullfromdepartmentdep deleteddelwheredep deptid del deptidend执行 updates