德瑞克：SQL Server 學習筆記: [SQL Server]：全部停用 (DISABLE) 資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)

因故需要全部停用 (DISABLE) 資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)。

停用 FOREIGN KEY 條件約束的效益

優化效能，經由停用 FOREIGN KEY 條件約束，讓新增、修改與刪除(INSERT、UPDATE、DELETE)資料的速度更快。
無須依據 PK、FK 順序來匯入資料。

舉例來講，可應用於：測試用資料庫、快速匯入資料、複寫(Replication)機制訂閱者端(Subscriber)的資料庫等情境。

注意事項：
使用「ALTER TABLE tbname NOCHECK CONSTRAINT ALL」，將停用資料表上的 FOREIGN KEY 條件約束(CONSTRAINT) 與 CHECK 條件約束(CONSTRAINT)。

可以使用以下 T-SQL 陳述式達成

-- 全部停用：資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
USE UserDB
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

-- 全部啟用：資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
-- 不檢查現有資料
USE UserDB
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL"
GO

示範環境：SQL Server 2016

無法利用 SSMS 觀察到 FOREIGN KEY 條件約束(CONSTRAINT) 是否被停用或啟用。

練習一：違反 FOREIGN KEY 條件約束，無法修改資料

示範如下

-- 練習一：違反 FOREIGN KEY 條件約束，無法修改資料

/*
主索引資料表：Region
外部索引資料表：Territories
使用 RegionID 作為外部索引資料行(Foreign Key Coumn)

Region 資料表的 RegionID 的值是：1、2、3、4。
*/

USE Northwind
GO

-- 00_查詢：資料庫內 FOREIGN KEY 條件約束(CONSTRAINT)的相關狀態
SELECT f.name N'物件名稱', is_disabled N'已停用(1)', is_not_trusted N'不檢查現有資料(1)',
 s.name N'結構描述', o.name N'外部索引資料表', 
 sc.name N'結構描述', r.name N'主索引資料表', delete_referential_action_desc N'在進行刪除時，宣告的參考動作之描述',
 update_referential_action_desc N'在進行更新時，宣告的參考動作之描述', f.type_desc N'物件描述'
FROM sys.foreign_keys f
 INNER JOIN sys.objects o ON f.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
 INNER JOIN sys.objects r ON f.referenced_object_id = r.object_id
 INNER JOIN sys.schemas sc ON r.schema_id = sc.schema_id
ORDER BY o.name
GO

-- 01_失敗：刻意更新為不存在 主索引資料表的 RegionID：100
-- 因為有 FOREIGN KEY 條件約束 的保護
UPDATE [dbo].[Territories]
SET RegionID =100
WHERE TerritoryID = 01581
GO

/*
錯誤訊息

訊息 547，層級 16，狀態 0，行 11
UPDATE 陳述式與 FOREIGN KEY 條件約束 "FK_Territories_Region" 衝突。
衝突發生在資料庫 "Northwind"，資料表 "dbo.Region", column 'RegionID'。
陳述式已經結束。
*/

-- 001_查詢全部資料表的FOREIGN KEY 條件約束

-- 002_失敗：刻意更新為不存在主索引資料表的 RegionID：100

練習二：因已停用 FOREIGN KEY 條件約束，測試資料可以寫入

示範如下

-- 01_全部停用：資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
USE Northwind
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

-- 02_查詢：資料庫內 FOREIGN KEY 條件約束(CONSTRAINT)的相關狀態
SELECT f.name N'物件名稱', is_disabled N'已停用(1)', is_not_trusted N'不檢查現有資料(1)',
 s.name N'結構描述', o.name N'外部索引資料表', 
 sc.name N'結構描述', r.name N'主索引資料表', delete_referential_action_desc N'在進行刪除時，宣告的參考動作之描述',
 update_referential_action_desc N'在進行更新時，宣告的參考動作之描述', f.type_desc N'物件描述'
FROM sys.foreign_keys f
 INNER JOIN sys.objects o ON f.parent_object_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
 INNER JOIN sys.objects r ON f.referenced_object_id = r.object_id
 INNER JOIN sys.schemas sc ON r.schema_id = sc.schema_id
ORDER BY o.name
GO

-- 03_成功：刻意更新為不存在 主索引資料表的 RegionID：100
-- 因為已經停用 FOREIGN KEY 條件約束

UPDATE [dbo].[Territories]
SET RegionID =100
WHERE TerritoryID = 01581
GO

-- 04_驗證：確認已經寫入違反 FOREIGN KEY 條件約束
SELECT TerritoryID, TerritoryDescription, RegionID FROM  [dbo].[Territories]
WHERE TerritoryID = 01581
GO

-- 05_全部啟用：資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
-- 不檢查現有資料
USE Northwind
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? WITH NOCHECK CHECK CONSTRAINT ALL"
GO

-- 06_驗證：確認已經寫入違反 FOREIGN KEY 條件約束
SELECT TerritoryID, TerritoryDescription, RegionID FROM  [dbo].[Territories]
ORDER BY TerritoryID
GO

-- 013_全部停用：資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)