SQLServerでDEFAULT制約付きのカラムの属性を変更する

SQLServerでDEFAULT制約付きのカラムの属性を変更する場合は、一旦付与されている制約を外してから変更しなければエラーになります。

その回避方法を解説します。

DEFAULT制約付きのカラムの属性を変更する

SQLServerでDEFAULT制約付きのカラムの属性を変更する場合は、ちょっとしたテクニックが必要です。

いきなり変更するとエラーになる

SQLServerでDEFAULT制約付きのカラムの属性を変更する場合は、一旦付与されている制約を外してから変更しなければ

オブジェクト 'DF_xxx_xxx' は 列 'xxxxx' に依存しています。
メッセージ 4922、レベル 16、状態 9、サーバー XXXXXX、
行 6 ALTER TABLE ALTER COLUMN xxxxx は失敗しました。
1 つ以上のオブジェクトがこの列を参照しています。 

のようにエラーになります。

一度設定されている制約を削除して作成する

上記で見たようにすでに制約が存在する場合はエラーになるので一旦削除してから作成します。

ただ、制約の名前は「DF_xxx_xxx」のように自動で生成された文字列でそれを毎回調べて削除して新しい制約を付与するのは面倒なので、ちょっとしたプログラムを作成しました。

----------------------------------
--使用する変数の宣言
----------------------------------
DECLARE @table_name VARCHAR(50)
DECLARE @col_name   VARCHAR(50)
DECLARE @table_id   INT
DECLARE @col_id     INT
DECLARE @col_const   VARCHAR(128)

SELECT @table_name = 'test_table' --属性を変更するテーブル
SELECT @col_name   = 'cnt'        --属性を変更するカラム

----------------------------------
--テーブルを作成
----------------------------------
EXEC('CREATE TABLE '+@table_name
                 +' ( cnt INT NOT NULL DEFAULT 0 )')

----------------------------------
--データを挿入
----------------------------------
EXEC('INSERT INTO '+@table_name+' (cnt) VALUES (10)')
EXEC('INSERT INTO '+@table_name+' (cnt) VALUES (11)')

----------------------------------
--挿入結果の確認
----------------------------------
EXEC('SELECT * FROM '+@table_name)

----------------------------------
--カラムの属性を変更
----------------------------------
--テーブルのIDを取得
SELECT @table_id = id FROM sys.sysobjects
    WHERE xtype = 'U' AND name = @table_name
--カラムのIDを取得
SELECT @col_id = column_id FROM sys.columns
    WHERE object_id =  @table_id AND name = @col_name 
--制約の名称を取得
SELECT @col_const = name FROM sys.sysobjects
    WHERE id = (
        SELECT constid FROM sys.sysconstraints
            WHERE id = @table_id AND colid = @col_id )

EXEC('ALTER TABLE '+@table_name+' DROP CONSTRAINT '+@col_const)
EXEC('ALTER TABLE '+@table_name+' ALTER COLUMN '
                    +@col_name+' DECIMAL(8,2) NOT NULL')
EXEC('ALTER TABLE '+@table_name+' ADD DEFAULT 0 FOR '+@col_name)

EXEC('INSERT INTO '+@table_name+' (cnt) VALUES (999999.99)')
EXEC('INSERT INTO '+@table_name+' (cnt) VALUES (999999.81)')

----------------------------------
--カラムの属性変更結果の確認
----------------------------------
EXEC('SELECT * FROM '+@table_name)

----------------------------------
--カラムの属性を変更（元に戻す）
----------------------------------
--テーブルのIDを取得
SELECT @table_id = id FROM sys.sysobjects
    WHERE xtype = 'U' AND name = @table_name
--カラムのIDを取得
SELECT @col_id = column_id FROM sys.columns
    WHERE object_id =  @table_id AND name = @col_name
--制約の名称を取得
SELECT @col_const = name FROM sys.sysobjects
    WHERE id = (
        SELECT constid FROM sys.sysconstraints
            WHERE id = @table_id AND colid = @col_id)

EXEC('ALTER TABLE '+@table_name+' DROP CONSTRAINT '+@col_const)
EXEC('ALTER TABLE '+@table_name+' ALTER COLUMN '+@col_name+' INT NOT NULL')
EXEC('ALTER TABLE '+@table_name+' ADD DEFAULT 0 FOR ' + @col_name)

----------------------------------
--カラムの属性変更結果の確認
----------------------------------
EXEC('SELECT * FROM '+@table_name)

----------------------------------
--テーブルの削除
----------------------------------
EXEC('DROP TABLE '+@table_name )

実行結果は
元のテーブルの内容

属性をDECIMAL(8,2)に変更したテーブルの内容

属性をINTに戻したテーブルの内容

のような感じになります。