異動欄位型態時要注意該欄位是否有其他物件相依,Msg 5074, Level 16, State 1

日前將一張資料表中一個資料型態為varchar改為nvarchar時發生了錯誤,發生錯誤之原因是因為該欄位有Default的Constraint相依導致。本LAB來簡單模擬此一問題。

如下圖所示,我建立一張資料表myTable,其name欄位我社資料型態為varchar,並給予Default值。該Default的Constraint名為DF_myTable_name。

 

建立完資料表後我們用Alter Table的指令來將name的欄位型態由原來的varchar改成nvarchar看看。下圖中我們修改欄位型態失敗,其原因就是因為該欄位有Constraint相依,所以無法修改。

因此解法就是
1.先解除Constraint。
2.異動資料欄位型態。
3.建立剛剛解除的Constraint。
下圖就是我的流程,由圖中訊息可以知道已成功修改了欄位的資料型態。

是不是異動欄位的Schema都要這樣麻煩呢? 上述LAB我是將varcahr改為nvarchar所以會有物件相依問題而發生錯誤。但如果你只是要擴大欄位長度則不需要解掉Constraint。如下圖我將該name欄位由10擴大到12,並不需解開Constraint流程即可異動。

LAB過程中無意間發現一個有趣的現象,那就是我們在對欄位做型態異動時(例如varchar轉nvarchar),其實SQL在背地裡居然會做Update的動作。如下圖我將name再由nvarchar改回varchar。

改型態時我一併用Profiler來錄,看看Alter Table Alter Column會讓SQL做哪些動作呢? 如下圖我們看見的,SQL在改變該欄位的型態後對該欄位做了一次Update的動作(如下圖紅色圈選處)。

由上面簡易LAB後我們可以得知異動欄位型態時要注意該欄位是否有其他物件相依,還有就是改變欄位型態時SQL會針對該欄位做一次Update,如果你的資料表很巨大,那得好好注意這一個問題並評估甚麼樣的作法最有效率。

 

我是ROCK

rockchang@mails.fju.edu.tw