在資料庫結構調整、資料遷移或效能優化時,常需刪除外鍵約束。關鍵在於必須先知道確切的約束名稱,才能執行 ALTER TABLE ... DROP CONSTRAINT。本文提供 SQL Server 2000/2005/2008+ 通用的查詢與刪除方法。
SQL Server 2000:
EXEC sp_helpconstraint '資料表名稱'
在結果中尋找 constraint_name 欄位,約束類型為 FOREIGN KEY 者即為目標。
SQL Server 2005 及以上版本(推薦):
SELECT name
FROM sys.foreign_key_columns fk
JOIN sys.objects obj ON fk.constraint_object_id = obj.object_id
WHERE fk.parent_object_id = OBJECT_ID('資料表名稱')
或使用更直觀的查詢:
SELECT fk.name AS FK_Name,
OBJECT_NAME(fk.parent_object_id) AS Table_Name,
COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS Column_Name,
OBJECT_NAME(fk.referenced_object_id) AS Ref_Table_Name,
COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS Ref_Column_Name
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
WHERE fk.parent_object_id = OBJECT_ID('資料表名稱')
ALTER TABLE [資料表名稱] DROP CONSTRAINT [外鍵約束名稱]
範例:ALTER TABLE test2 DROP CONSTRAINT FK__test2__id__08EA5793
重複執行步驟 1 的查詢,確認該資料表不再有外鍵約束記錄。
-- 建立測試環境
CREATE TABLE test1 (id INT PRIMARY KEY NOT NULL, value INT)
INSERT INTO test1 VALUES (1, 2)
CREATE TABLE test2 (id INT REFERENCES test1(id), value INT)
-- 1. 查詢外鍵名稱 (SQL Server 2005+)
SELECT name FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('test2')
-- 結果範例:FK__test2__id__08EA5793
-- 2. 刪除外鍵
ALTER TABLE test2 DROP CONSTRAINT FK__test2__id__08EA5793
-- 3. 驗證
SELECT name FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('test2')
-- 無結果表示刪除成功
若需一次移除某資料表所有外鍵,可使用動態 SQL:
DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(name) + '; '
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('目標資料表名稱')
EXEC sp_executesql @sql
FK__test2__id__08EA5793),不同環境名稱不同,不可硬編碼在部署腳本中CONSTRAINT FK_Test2_Test1_ID FOREIGN KEY (id) REFERENCES test1(id),利於後續維護SSMS (SQL Server Management Studio) 中:
掌握查詢約束名稱的系統檢視表用法,即可輕鬆應對任何版本 SQL Server 的外鍵管理需求。