外鍵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