外键ON DELETE 两种方法的测试

/**测试目的，对外键的 ON DELETE 的两种方法的测试，了解是如何工作的。这两种方法是：ON DELETE CASCADE SET NULLON DELETE CASCADE首先建立两张表，T1和T11T1表是主表，T11表是明细表。T1表的主键是T11表的外键。下面以SQL Server 2012 为例测试**/IF EXISTS (SELECT NAME From sysobjects Where type='U' and NAME='App_Officer')  DROP TABLE T1;GO-- T1表主键为FKEY，主键约束的名称为：PK_T1_FKEYCREATE TABLE T1(  FKEY  INT,  FNAME VARCHAR(20),  CONSTRAINT PK_T1_FKEY PRIMARY KEY (FKEY));-- 查看主键情况SELECT M.name Table_name,I.name Primary_key_name,C.name ColName,C.column_id,IC.key_ordinal Primary_key_idFROMSYS.objects MJOINSYS.columns CON M.object_id = C.object_idJOINSYS.indexes ION M.object_id = I.object_idJOINSYS.index_columns ICON M.object_id = IC.object_id AND I.index_id = IC.index_id AND C.column_id = IC.column_idWHERE M.name = 'T1'/** 查看主键结果如下---------------------------------------------------------------------Table_name  Primary_key_name    ColName column_id   Primary_key_idT1          PK_T1_FKEY          FKEY    1           1---------------------------------------------------------------------**/INSERT INTO T1 VALUES (1,'PAUL');SELECT * FROM T1;/** 查询结果如下---------------------------------------------------------------------FKEY    FNAME1       PAUL---------------------------------------------------------------------**/IF EXISTS (SELECT NAME From sysobjects Where type='U' and NAME='App_Officer')  DROP TABLE T11;GOCREATE TABLE T11(  SKEY INT,  PID  INT,  CONSTRAINT PK_T11_SKEY PRIMARY KEY (SKEY),  CONSTRAINT FK_T11_PID  FOREIGN KEY (PID) REFERENCES T1(FKEY) ON DELETE SET NULL);-- 查看主键情况SELECT M.name Table_name,I.name Primary_key_name,C.name ColName,C.Column_id,IC.key_ordinal Primary_key_idFROMSYS.objects MJOINSYS.columns CON M.object_id = C.object_idJOINSYS.indexes ION M.object_id = I.object_idJOINSYS.index_columns ICON M.object_id = IC.object_id AND I.index_id = IC.index_id AND C.column_id = IC.column_idWHERE M.name = 'T11'/** 查看主键结果如下---------------------------------------------------------------------Table_name  Primary_key_name    ColName Column_id   Primary_key_idT1          PK_T11_SKEY         SKEY    1           1---------------------------------------------------------------------**/-- 查看外键情况SELECT M.name Table_name,F.name Foreign_key_name,C.name ColName,C.Column_id,FC.referenced_column_id Referenced_Column_idFROMSYS.objects MJOINSYS.columns CON M.object_id = C.object_idJOINSYS.foreign_keys FON M.object_id = F.parent_object_idJOINSYS.foreign_key_columns FCON M.object_id = FC.parent_object_id AND F.object_id = FC.constraint_object_id AND C.column_id = FC.parent_column_idWHERE M.name = 'T11'/** 查看外键结果如下------------------------------------------------------------------------Table_name  Primary_key_name    ColName Column_id   Referenced_Column_idT11         FK_T11_PID          PID     2           1------------------------------------------------------------------------**/INSERT INTO T11 VALUES (1,1);SELECT * FROM T11 WHERE SKEY = 1;/** 查询结果如下---------------------------------------------------------------------SKEY    PID1       1---------------------------------------------------------------------**/DELETE FROM T1 WHERE FKEY = 1;SELECT * FROM T11 WHERE SKEY = 1;/** 查询结果如下---------------------------------------------------------------------SKEY    PID1       NULL---------------------------------------------------------------------**/DELETE FROM T11 WHERE SKEY = 1;ALTER TABLE T11 DROP CONSTRAINT FK_T11_PID;ALTER TABLE T11 ADD CONSTRAINT FK_T11_PID FOREIGN KEY(PID) REFERENCES T1(FKEY) ON DELETE CASCADE;INSERT INTO T1 VALUES (1,'PAUL');INSERT INTO T11 VALUES (1,1);SELECT * FROM T11;DELETE FROM T1 WHERE FKEY = 1;SELECT * FROM T11;/** 查询结果如下---------------------------------------------------------------------SKEY    PID---------------------------------------------------------------------**/-- 结论-- ON DELETE CASCADE 级联删除子表的记录, -- ON DELETE CASCADE SET NULL 将子表记录的外键值设置为 NULL