外鍵約束保障參考完整性,但架構變更、資料遷移、效能測試時常需暫時移除。本文提供從查詢約束名稱到安全刪除的標準化流程,支援 SQL Server 2000/2005/2008/2012+ 所有版本。
EXEC sp_helpconstraint 'ChildTable';
結果集中 `constraint_name` 欄位即為外鍵名 (通常格式 `FK__ChildTable__Col__XXXXXXXX`)。
-- 方法 1:sys.foreign_keys (推薦)
SELECT name AS FK_Name,
OBJECT_NAME(parent_object_id) AS ChildTable,
OBJECT_NAME(referenced_object_id) AS ParentTable
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('ChildTable');
-- 方法 2:詳細欄位對應
SELECT fk.name AS FK_Name,
pc.name AS ParentColumn,
rc.name AS ReferencedColumn
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id
JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
WHERE fk.parent_object_id = OBJECT_ID('ChildTable');
-- 標準語法 (所有版本通用)
ALTER TABLE ChildTable DROP CONSTRAINT FK_ChildTable_ParentTable_ColName;
-- 實例:假設查得約束名為 FK__test2__id__08EA5793
ALTER TABLE test2 DROP CONSTRAINT FK__test2__id__08EA5793;
-- 再次執行步驟 1 查詢,應無記錄返回
SELECT name FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('ChildTable');
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
+ N'.' + QUOTENAME(OBJECT_NAME(parent_object_id))
+ N' DROP CONSTRAINT ' + QUOTENAME(name) + N';' + CHAR(10)
FROM sys.foreign_keys
WHERE parent_object_id IN (OBJECT_ID('TableA'), OBJECT_ID('TableB')); -- 指定目標表
PRINT @sql; -- 審閱後執行
-- EXEC sp_executesql @sql;
-- 模式:FK_ChildTable_ParentTable_ColumnName
-- 範例:
CONSTRAINT FK_Order_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
CONSTRAINT FK_OrderDetail_Order_OrderID FOREIGN KEY (OrderID) REFERENCES [Order](OrderID)
建立「查詢 → 刪除 → 驗證」標準化 SOP,納入變更管理流程,配合命名規範與批量腳本,讓外鍵管理不再是部署路上的絆腳石。