搜尋本站文章

2017-06-18

[SQL Server]:全部停用 (DISABLE) 資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)


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

停用 FOREIGN KEY 條件約束的效益

  1. 優化效能,經由停用 FOREIGN KEY 條件約束,讓新增、修改與刪除(INSERT、UPDATE、DELETE)資料的速度更快。
  2. 無須依據 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)



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



-- 015_成功:刻意更新為不存在 主索引資料表的 RegionID:100



-- 016_驗證:確認已經寫入違反 FOREIGN KEY 條件約束



-- 017_全部啟用:資料庫內的 FOREIGN KEY 條件約束(CONSTRAINT)、CHECK 條件約束(CONSTRAINT)
-- 不檢查現有資料


-- 018_驗證:確認已經寫入違反 FOREIGN KEY 條件約束





SSMS 檢視資料表上的 FOREIGN KEY 條件約束

示範環境:SQL Server 2016

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

-- 061_SSMS_ 觀察資料表_FK_PK



-- 062_SSMS_ 點選「關聯性」



-- 063_外部索引鍵關聯性



-- 064_FK_資料表及資料行規格



-- 065_FK_資料表和資料行



-- 066_停用FK_強制使用外部索引鍵條件約束



-- 067_變更FK屬性,需要異動參與的資料表



-- 068_無法識別,FK是否被停用



-- 069_資料庫關聯圖表_無法呈現是否被停用



-- 070_檢視_關聯性的屬性



-- 071_利用系統檢視觀察_FK已被停用





參考文件

[SQL Server]:全部停用 (DISABLE) 資料庫內的 CHECK 條件約束(CONSTRAINT)
http://sharedderrick.blogspot.tw/2017/06/sql-server-disable-check-constraint.html

[SQL Server]:全部停用 (DISABLE) 資料庫內的 DML 觸發程序(Trigger)
http://sharedderrick.blogspot.tw/2017/06/sql-server-disable-trigger.html

How to disable all triggers and constraints?
http://www.sqlusa.com/bestpractices2005/disabletriggerconstraint/

[SQL Server][Replication]:複寫使用者正在插入至 NOT FOR REPLICATION 識別欄位時...遭遇錯誤
http://sharedderrick.blogspot.tw/2017/06/sql-serverreplication-not-for.html

DISABLE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms189748.aspx

停用(Disable)與啟用(Enable)「觸發程序(Trigger)」
http://sharedderrick.blogspot.tw/2010/09/disableenabletrigger.html

停用複寫的檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms190235.aspx

唯一條件約束與檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms187550.aspx#Check