外键ON DELETE 两种方法的测试

/**
测试目的，对外键的 ON DELETE 的两种方法的测试，了解是如何工作的。
这两种方法是：
ON DELETE CASCADE SET NULL
ON DELETE CASCADE
首先建立两张表，T1和T11
T1表是主表，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_FKEY
CREATE 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_id
FROM
SYS.objects M
JOIN
SYS.columns C
ON M.object_id = C.object_id
JOIN
SYS.indexes I
ON M.object_id = I.object_id
JOIN
SYS.index_columns IC
ON M.object_id = IC.object_id AND I.index_id = IC.index_id AND C.column_id = IC.column_id
WHERE M.name = 'T1'

/** 查看主键结果如下
---------------------------------------------------------------------
Table_name  Primary_key_name    ColName column_id   Primary_key_id
T1          PK_T1_FKEY          FKEY    1           1
---------------------------------------------------------------------
**/
INSERT INTO T1 VALUES (1,'PAUL');
SELECT * FROM T1;
/** 查询结果如下
---------------------------------------------------------------------
FKEY    FNAME
1       PAUL
---------------------------------------------------------------------
**/

IF EXISTS (SELECT NAME From sysobjects Where type='U' and NAME='App_Officer')
  DROP TABLE T11;
GO
CREATE 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_id
FROM
SYS.objects M
JOIN
SYS.columns C
ON M.object_id = C.object_id
JOIN
SYS.indexes I
ON M.object_id = I.object_id
JOIN
SYS.index_columns IC
ON M.object_id = IC.object_id AND I.index_id = IC.index_id AND C.column_id = IC.column_id
WHERE M.name = 'T11'

/** 查看主键结果如下
---------------------------------------------------------------------
Table_name  Primary_key_name    ColName Column_id   Primary_key_id
T1          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_id
FROM
SYS.objects M
JOIN
SYS.columns C
ON M.object_id = C.object_id
JOIN
SYS.foreign_keys F
ON M.object_id = F.parent_object_id
JOIN
SYS.foreign_key_columns FC
ON M.object_id = FC.parent_object_id AND F.object_id = FC.constraint_object_id AND C.column_id = FC.parent_column_id
WHERE M.name = 'T11'

/** 查看外键结果如下
------------------------------------------------------------------------
Table_name  Primary_key_name    ColName Column_id   Referenced_Column_id
T11         FK_T11_PID          PID     2           1
------------------------------------------------------------------------
**/

INSERT INTO T11 VALUES (1,1);
SELECT * FROM T11 WHERE SKEY = 1;
/** 查询结果如下
---------------------------------------------------------------------
SKEY    PID
1       1
---------------------------------------------------------------------
**/
DELETE FROM T1 WHERE FKEY = 1;
SELECT * FROM T11 WHERE SKEY = 1;
/** 查询结果如下
---------------------------------------------------------------------
SKEY    PID
1       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