MSSMS（Microsoft SQL Server Management Studio）のテーブル デザイナーから手動変更を行うとしたところ、[変更の保存が許可されていません～]というエラーメッセージが表示されました。
今回は、このメッセージが表示された場合の対処法をメモ。
設定方法が複数ありますが、ちゃんと理解して対応しないと痛い目にあいます。
[オプション]設定は場合によっては非推奨方法の様子
当初、MSSMSの設定かと思い、[プロパティ]や[オプション]を確認してみました。結果、[オプション]→[デザイナー]→[テーブル デザイナーおよびデータベース デザイナー]に
テーブルの再作成を必要とする変更を保存できないようにする(S)がありましたので、チェックを外す事でテーブル デザイナーから変更が可能になりました。
一件落着！
と、ここで記事が終わりそうですが、実は違います！
一概に[オプション]設定を変更してはしけません。
そもそも、なぜMSSMSが警告を出力するのだろうか？
今回のエラーメッセージについて、ふと立ち止まって考えてみました。- なぜ、この警告メッセージをMSSMSが出力してんだろうか
- なぜ、[オプション]に今回のチェックボックスがあるのか
Microsoft サポートページ
エラー メッセージ "変更の保存が許可されていません" が、SQL Server でテーブルを保存しようとすると表示されるを見ると、
このオプションを無効にすると、テーブルの再作成を回避するのに役立ちますが、変更内容が失われる可能性も生じます。と明記されています。
たとえば、SQL Server 2008 で変更の監視機能を有効にしてテーブルへの変更を追跡する場合を考えます。
テーブルが再作成される原因になる操作を実行すると、「現象」に記載されているエラー メッセージが表示されます。
ただし、このオプションを無効にしていると、テーブルが再作成される際に既存の変更の追跡情報が削除されます。
そのため、このオプションを無効にしてこの問題を回避することはお勧めしません。
この内容をまとめると
- [テーブル]の[変更の追跡]機能を有効（True）にしていると[テーブルの再作成を必要とする変更を保存できないようにする]オプションは有効になる
- 1の状況でオプションを無効（False）にし、テーブル デザイナーからテーブルのメタデータ情報を変更すると[変更の追跡]情報が削除される
運用中にテーブルのメタデータ情報を変更することはほぼないと思いますが・・・
正しい変更方法
Microsoft サポートページエラー メッセージ "変更の保存が許可されていません" が、SQL Server でテーブルを保存しようとすると表示されるによると、テーブル情報を変更する場合は
Transact-SQL ステートメントを使用しテーブルのメタデータ情報を変更しますと書かれています。
忘れてはいけないのですが、忘れそうなのでいくつかのサンプルをメモしておきます。
下記Transact-SQLはSQL Server 2008R2をベースにしています。
カラム定義を変更
ALTER TABLE テーブル名 ALTER COLUMN col01 varchar(10) NULL
カラムを追加
ALTER TABLE テーブル名 ADD col01 varchar(10) NOT NULL DEFAULT ‘999’稼働中のテーブルに列を追加する場合はNULL許可、ないしは、既定値(DEFAULT)が必要です。
カラムは最後尾に追加され、任意の場所に追加する事はSSMS上から行う必要があります。
ただし、この操作はベストプラクティスではない！とMS SQL Server ライブラリに明記されています。
他DBにあるAFTERがつかれば楽ですが、そもそも設計が悪い！というお話になりますかね・・・
カラムの削除
ALTER TABLE テーブル名 DROP COLUMN col01[制約]にDEFAULT定義(DF__テーブル名__列名__xxxxx)が定義されている場合、上記クエリだけでは失敗します。
運用上DEFAULT定義を削除してもいい場合、[列の削除]を行う前に[DEFAULT定義の削除]を行ってください。
ALTER TABLE テーブル名 DROP DF__テーブル名__testcol__08EA5793
go
ALTER TABLE テーブル名 DROP COLUMN testcol
go
ALTER TABLEの詳細
msdnの”ALTER TABLE (Transact-SQL)”をご覧ください。SQL Serverは他製品と同様、比較的簡単に導入できます。
しかし、いざ仕様の変更やトラブルシューティングとなると、知識と経験がないと一筋縄ではいきませんね。
今回も身をもって体験させてもらいました。。。
今日も精進したいと思います。
余談ですが、以前MySQLやPostgreSQLで列の追加や削除を行う時
- 列の追加や削除を適用した一時テーブルを作成
- 既存テーブルから一時テーブルへINSERT SELECTでデータを流し込み
- 既存テーブルを削除
- 既存テーブル名と同じテーブルを作成、一時テーブルのデータを流し込み
- 一時テーブルを削除
とはいえ、運用中は「出来るからやってみる！」のような安易な発想は御法度ですよね。
テスト、大事です！