SQL Server 外键约束的例子.doc

SQL Server 外键约束的例子外键约束的测试表与测试数据-创建测试主表.ID是主键.CREATETABLEtest_main(idINT,valueVARCHAR(10),PRIMARYKEY(id);-创建测试子表.CREATETABLEtest_sub(idINT,main_idINT,valueVARCHAR(10),PRIMARYKEY(id);-插入测试主表数据.INSERTINTOtest_main(id,value)VALUES(1,ONE);INSERTINTOtest_main(id,value)VALUES(2,TWO);-插入测试子表数据.INSERTINTOtest_sub(id,main_id,value)VALUES(1,1,ONEONE);INSERTINTOtest_sub(id,main_id,value)VALUES(2,2,TWOTWO);默认外键约束方式1ALTERTABLEtest_subADDCONSTRAINTmain_id_consFOREIGNKEY(main_id)REFERENCEStest_main;2go1DELETE2test_main3WHERE4ID=1;5go消息547，级别16，状态1，服务器HOME-BED592453CSQLEXPRESS，第1行DELETE语句与REFERENCE约束main_id_cons冲突。该冲突发生于数据库Stock，表dbo.test_sub,columnmain_id。语句已终止。测试完毕后，删除外键约束ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;DELETECASCADE方式1-创建外键(使用ONDELETECASCADE选项，删除主表的时候，同时删除子表)2ALTERTABLEtest_sub3ADDCONSTRAINTmain_id_cons4FOREIGNKEY(main_id)REFERENCEStest_mainONDELETECASCADE;56-测试删除主表数据.将成功地执行.7DELETE8TEST_MAIN9WHERE10ID=1;1112-测试检索子表，应该只有一条main_id=2的数据.13SELECT14*15FROM16test_sub;1718go(1行受影响)idmain_idvalue-22TWOTWO(1行受影响)测试完毕后，删除外键约束ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;UPDATECASCADE方式-创建外键(使用ONUPDATECASCADE选项，更新主表的主键时候，同时更新子表外键)1ALTERTABLEtest_sub2ADDCONSTRAINTmain_id_cons3FOREIGNKEY(main_id)REFERENCEStest_mainONUPDATECASCADE;4go-测试更新主表数据.将成功地执行.1UPDATEtest_mainSETid=5whereid=12go(1行受影响)-检查子表数据是否被同步更新1select*fromtest_sub2goidmain_idvalue-15ONEONE22TWOTWO测试完毕后，删除外键约束ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;SETNULL方式1-创建外键(使用ONDELETESETNULL选项，删除主表的时候，同时将子表的main_id设置为NULL)2ALTERTABLEtest_sub3ADDCONSTRAINTmain_id_cons4FOREIGNKEY(main_id)REFERENCEStest_mainONDELETESETNULL;567-测试删除主表数据.将成功地执行.8DELETE9TEST_MAIN10WHERE11ID=2;1213-测试检索子表，应该有一条的main_id=null14SELECT15*16FROM17test_sub;1819go(1行受影响)idmain_idvalue-2NULLTWOTWO(1行受影响)测试完毕后，删除外键约束ALTERTABLEtest_subDROPCONSTRAINTmain_id_cons;SETDEFAULT方式为了更容易理解，SETDEFAULT方式使用下面这样的测试表与测试数据.-创建测试主表.ID是主键.-班级表.CREATETABLEtest_main_class(idINTNOTNULL,valueVARCHAR(20),PRIMARYKEY(id);-创建测试子表.-学生表.CREATETABLEtest_sub_student(idINTNOTNULL,main_idINTDEFAULT0,valueVARCHAR(10),PRIMARYKEY(id);-插入测试主表数据.INSERTINTOtest_main_class(id,value)VALUES(0,暂无班级);INSERTINTOtest_main_class(id,value)VALUES(1,2013级1班);INSERTINTOtest_main_class(id,value)VALUES(2,2013级2班);-插入测试子表数据.INSERTINTOtest_sub_student(id,main_id,value)VALUES(1,1,张三);INSERTINTOtest_sub_student(id,main_id,value)VALUES(2,2,李四);首先测试ONDELETESETDEFAULTALTERTABLEtest_sub_studentADDCONSTRAINTfk_main_classFOREIGNKEY(main_id)REFERENCEStest_main_classONDELETESETDEFAULT;SELECTtest_main_class.valueAS班级,test_sub_student.valueAS学生FROMtest_main_classJOINtest_sub_studentON(test_main_class.id=test_sub_student.main_id);go班级学生-2013级1班张三2013级2班李四(2行受影响)1DELETEFROMtest_main_classWHEREID=1;2go(1行受影响)1SELECT2test_main_class.valueAS班级,3test_sub_student.valueAS学生4FROM5test_main_class6JOINtest_sub_student7ON(test_main_class.id=test_sub_student.main_id);8go班级学生-暂无班级张三2013级2班李四(2行受影响)1ONDELETESETDEFAULT指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用NULL作为该列的隐式默认值。因ONDELETESETDEFAULT而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。然后测试ONUPDATESETDEFAULT-删除前面的外键约束.ALTERTABLEtest_sub_studentDROPCONSTRAINTfk_main_class;GO-创建外键约束.ALTERTABLEtest_sub_studentADDCONSTRAINTfk_main_classFOREIGNKEY(main_id)REFERENCEStest_main_classONUPDATESETDEFAULT;-更新主表.1UPDATEtest_main_classSETID=20WHEREID=2;2go(1行受影响)12SELECT3test_main_class.valueAS班级,4test_sub_student.valueAS学生5FROM6test_main_class7JOINtest_sub_student8ON(test_main_class.id=test_sub_student.main_id);9go班级学生-暂无班级张三暂无班级李四(2行受影响)ONUPDATESETD