ALTER TABLE DROP COLUMN を使用してNOT NULL の列を削除したい

環境/言語:[SQLServer2005]
分類:[その他]

こんにちは。

DBの列修正などを行っております。
環境はSQL Server 2005です。

やりたいことは
・ このSQLを流せば、NULL許容しない列が追加できる。
・ 別のSQLを流せば、その列が削除できる。
この二つのSQL文を作成したいのです。

NULL許容する場合の列ならうまく動いています。

UsersテーブルにIDという列を追加するために、このようなSQLを発行しました。
　ALTER TABLE Users ADD ID nvarchar(40) NULL
そして、削除するために
　ALTER TABLE Users DROP COLUMN ID
このコマンドは、どちらもうまくいきます。


ですが、NULL値を許容しない列の場合うまくいきません。

　ALTER TABLE Users ADD ID nvarchar(40) NOT NULL DEFAULT 0
このコマンドでNULL値許容しない列は作れるのですが

　ALTER TABLE Users DROP COLUMN ID
このコマンドを行うと

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

このようなメッセージが表示されます。
xxxxxxxxで示した部分は、数値と英語の意味不明な羅列です。

NULLを許容しない列を作成すると、
"制約"が付属して、その制約のために削除もできなくなるようなのですが
回避方法はないでしょうか？

ALTER TABLE Users DROP COLUMN ID の前に
この制約を削除するSQL文が書ければいいのですが
xxxxxxxxの部分が特定できないため、
手動でSQLを作るしかないので困っています。

よろしくお願いします。

自己レスです。
調べていたのですが、そっくりのやりとりがみつかりました。
ですが、解決していません。

DEFAULT制約付きのカラムを削除したい
http://social.msdn.microsoft.com/forums/ja-JP/sqlserverja/thread/c876f70d-76f5-49f4-a524-dfb1fc59cbf7/

このリンク先と全く同じ問題にあっています。
そこでは
> alter column句を使ってDEFAULT制約を外した定義に変更した後でDROPをかければ良い
とあり、それで直ったことになっていますが、

実際には、DEFAULT制約を外しても
制約である DK__テーブル名__列名__xxxxxxxx が削除されてくれませんので
やはり、コマンドの動作が失敗します。

テーブルの設定によってDK__が削除されない場合などがあるのでしょうか。
もしくは、DK__テーブル名__列名__xxxxxxxx が消えるような
リフレッシュコマンドがあるのでしょうか？

■No26197に返信(FutoNekoさんの記事)
> NULLを許容しない列を作成すると、
> "制約"が付属して、その制約のために削除もできなくなるようなのですが
> 回避方法はないでしょうか？

制約も一緒に削除するなら、
  ALTER TABLE 表名 DROP CONSTRAINT 制約名, COLUMN 列名
という記述ができます。

> xxxxxxxxの部分が特定できないため、
そのため、作成時に分かりやすい制約名を付けておくと良いでしょう。
ALTER TABLE Users ADD ID nvarchar(40) CONSTRAINT DF_Users_ID_DEF DEFAULT 0 NOT NULL

そうすれば削除する際にも、制約名を明示できますし。
ALTER TABLE Users DROP CONSTRAINT DF_Users_ID_DEF, COLUMN ID

2010/01/07(Thu) 16:21:03 編集(投稿者)

動作しました！

>>xxxxxxxxの部分が特定できないため、
> そのため、作成時に分かりやすい制約名を付けておくと良いでしょう。
> ALTER TABLE Users ADD ID nvarchar(40) CONSTRAINT DF_Users_ID_DEF DEFAULT 0 NOT NULL
>
> そうすれば削除する際にも、制約名を明示できますし。
> ALTER TABLE Users DROP CONSTRAINT DF_Users_ID_DEF, COLUMN ID

すばらしいです。
制約名を指定しての、列追加ができるとは！

ありがとうございます。

■No26199に追記(魔界の仮面弁士の記事)
> そうすれば削除する際にも、制約名を明示できますし。
> ALTER TABLE Users DROP CONSTRAINT DF_Users_ID_DEF, COLUMN ID

制約名が分からない場合には、下記のような方法もあります。

DECLARE @列名 sysname
DECLARE @表名 sysname

SET @表名 = N'Users'
SET @列名 = N'ID'

DECLARE @制約名 sysname

SELECT @制約名 = OBJECT_NAME(sys.columns.default_object_id)
 FROM sys.columns
WHERE sys.columns.object_id = OBJECT_ID(@表名)
  AND sys.columns.name = @列名

EXEC('ALTER TABLE "' + @表名 + '" DROP CONSTRAINT "' + @制約名 + '", COLUMN "' + @列名 + '"')