SQL Server ALTER TABLE 修改欄位遭遇「物件相依」錯誤的完整解決方案

錯誤現象

訊息 5074，層級 16，狀態 1，第 2 行
物件 'DF__DDCZJLB_M__DDCZJ__5BBA8402' 相依於欄位 'DDCZJLB_GROUPID'。

訊息 4922，層級 16，狀態 9，第 2 行
由於一或多個物件存取此欄位，ALTER TABLE ALTER COLUMN DDCZJLB_GROUPID 失敗。

**原因**：該欄位存在 **預設約束（Default Constraint）**、檢查約束、外鍵參考、計算欄位、索引或視圖相依，SQL Server 禁止直接變更欄位定義。

解決方案一：圖形化介面移除約束（SSMS）

1. 物件總管右鍵資料表 → **設計**。
2. 右鍵欄位列 → **屬性** → **預設值/繫結** 清空 → **確定**。
3. 儲存資料表（Ctrl+S），SSMS 會自動產生 DROP CONSTRAINT + ALTER COLUMN + ADD CONSTRAINT 腳本。

解決方案二：T-SQL 腳本精準移除（自動化/批次適用）

步驟 1：查詢欄位相依的預設約束名稱

DECLARE @constraint_name SYSNAME;
SELECT @constraint_name = dc.name
FROM sys.default_constraints dc
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID('dbo.YourTable') AND c.name = 'YourColumn';

步驟 2：動態刪除約束

IF @constraint_name IS NOT NULL
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'ALTER TABLE dbo.YourTable DROP CONSTRAINT ' + QUOTENAME(@constraint_name);
    EXEC sp_executesql @sql;
END

步驟 3：執行欄位變更

ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn NEW_DATA_TYPE(...);

步驟 4：重建必要約束

ALTER TABLE dbo.YourTable ADD CONSTRAINT DF_YourTable_YourColumn DEFAULT (DEFAULT_VALUE) FOR YourColumn;

解決方案三：暴力斷開連線（最後手段）

若約束極多或無法識別，可嘗試：

1. 設定資料庫 SINGLE_USER WITH ROLLBACK IMMEDIATE 強制斷開所有連線。
2. 執行 ALTER TABLE ... DROP CONSTRAINT ... 逐一清理。
3. 完成後恢復 MULTI_USER。

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- 執行 DROP CONSTRAINT / ALTER COLUMN
ALTER DATABASE YourDB SET MULTI_USER;

常見相依物件類型與查詢

最佳實踐建議

• **命名約束**：建表時顯式命名 CONSTRAINT DF_Table_Col DEFAULT ...，避免系統產生難以辨識的亂碼名稱。
• **變更前備份**：SELECT * INTO Table_Bak FROM Table 或完整資料庫備份。
• **腳本化部署**：所有 DDL 納入版控，採用遷移腳本工具（Flyway、DbUp、RoundhousE）自動化執行。
• **測試環境先行**：在 Staging 完整演練變更腳本，驗證應用相容性。