データベーステーブルからレコードを削除するには「DELETE」を使いますが、列を削除することは出来ません。
列の削除に使うクエリは「ALTER TABLE」と「DROP COLUMN」を使用します。
そこで今回は、テーブルから列を削除するこの二つのクエリについて解説を行っていきたいと思います。
SELECT、INSERT、UPDATE、DELETEという4つの基本クエリほど重要性は高くありませんが、これらを覚えたら次はALTER TABLEを使ったクエリを覚えておくとスムーズに学習を進めることが出来ます。
是非この記事を機会に覚えてみましょう！
ALTER TABLEについて
まず冒頭で述べた「ALTER TABLE」についての基本的知識を学習しましょう。
“ALTER TABLE”とは、テーブルの定義変更する場合に使用するクエリです。
分かりやすく言うと、列と制約(INDEXやPRIMARY KEYなど)を追加 / 変更 / 削除することが出来ます。
このほかにも、ALTER TABLEを使用することでパーティションを再構築したり、制約やトリガーを無効化したり有効化したりも出来ます。
DROP COLUMNを使う
さて、冒頭でも述べた通りデータベーステーブルから列を削除する場合には「DROP COLUMN」を使用します。
記述方法は次の通りです。
ALTER TABLE [テーブル名] DROP COLUMN [カラム名];
記述はたったこれだけです。
これでデータベーステーブルから列を削除することが出来ます。
どのクエリにも言えることですが、SQLコマンドは至ってシンプルなのですぐ覚えられるのも魅力の一つです。
では実際にサンプルコードで実験してみましょう。
// 今回の実験で使用するデータベーステーブルの構造 no name age ----------- ---------- ----------- ALTER TABLE sample_table DROP COLUMN name; SELECT * FROM sample_table GO no age ----------- -----------
このサンプルコードでは、実験用に”sample_table”というテーブル名でデータベーステーブルを作成しています。
ALTER TABLEに続いてDROP COLUMNを使用することで、データベーステーブルから「name」という列が削除されていることが確認できました。
default制約の付いたカラムを削除する
ここまでは”ALTER TABLE”と”DROP COLUMN”の基本的な使い方について解説しましたが、この使い方では削除できないカラムというものが存在します。
それはdefault制約付きのカラムです。
DELETEは次の条件に該当するカラムは削除できないことになっています。
- インデックスで使用されているカラム及びPRIMARY KEY制約で使用されているカラム
- default定義の規定値として関連付けられたカラム及び規定値のオブジェクトにバインドしているカラム
- UNIQUE、CHECK、FOREIGN KEYが設定されているカラム
- ルールにバインドしているカラム
このことから、次のコードのようなdefault値が設定されているカラムは削除できません。
// 今回のサンプルコードで使用するテーブル
// flugにはdefault制約を設定
no name age flug
----------- ---------- ----------- -----------
1 Alex 25 1
2 Tomath 30 1
3 Sophia 21 1
4 Gordon 24 1
5 Alisia 28 1
ALTER TABLE sample_table
DROP COLUMN flug;
GO
メッセージ 5074、レベル 16、状態 1、サーバー DESKTOP-T3J7G2K\SQLEXPRESS、行 1
オブジェクト 'DF_sample_table_default' は 列 'flug' に依存しています。
メッセージ 4922、レベル 16、状態 9、サーバー DESKTOP-T3J7G2K\SQLEXPRESS、行 1
ALTER TABLE DROP COLUMN flug は失敗しました。1 つ以上のオブジェクトがこの 列 を参照しています。
default制約を設定している列を削除しようとするとこのようなエラーが排出されます。
これを解決するには、列に設定されている制約を削除する必要があります。
default制約のついた列を探す
今回のように予めdefault制約がついている列がわかっていれば苦労はしませんが、実際の現場ではDB仕様書がないデータベースを引き継いだ場合や共同作業で同時進行しているケースなど、制約の有無がはっきりしない方が多いと思います。
そこでまずはデータベーステーブルの中でdefault制約が設定されている列を探し出す方法から紹介します。
default制約は次のSQLコマンドで検索することが出来ます。
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT ← このスキーマコレクションでdefault制約の有無をチェックする FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME, ORDINAL_POSITION GO // 結果 COLUMN_NAME DATA_TYPE COLUMN_DEFAULT ----------- --------- -------------- no int NULL name nchar NULL age int NULL flug int ((1))
結果で表示されている一覧の中で最後の列がdefault制約が設定されているかどうかの判定となります。
“NULL”と表示されている場合にはdefault制約は設定されていません。
今回はflugにdefault制約を付加しているため、flugの行に「((1))」と表示されていることが確認できます。
なお「((1))」と表示されているのは、default制約で1という数値が設定されているためです。
これで下準備は整ったので、次からは制約の削除の方法について解説していきます。
制約を取得する
まず制約を削除するためには「制約名」を取得する必要があります。
制約名の取得にはテーブルIDとカラムIDが必要となるため、2段階に分けてこれらのIDを取得していきます。
テーブルIDの取得
まずはテーブルIDの取得方法です。
スキーマコレクションが用意されているため、次のコマンドで取得できます。
SELECT id FROM sys.sysobjects WHERE xtype = 'U' AND name = '[table_name]'
[table_name]の部分には検索をかけたいデータベーステーブル名を入力してください。
カラムIDの取得
テーブルIDが取得出来たら続いてカラムIDの取得です。
先ほど行ったdefault制約がついている列の確認でflugにdefaultがついていることが確認できたので、この列名を使いカラムIDを取得していきます。
取得方法については次の通りです。
SELECT column_id FROM sys.columns WHERE object_id = [table_id] AND name = '[column_name]'
[table_id]には先ほど取得したテーブルIDを入力します。
[column_name]には、default制約の設定がされていた列名を入力します。
制約名の取得
これでテーブルIDとカラムIDの両方が揃ったので、最後にこの二つを使い制約名を取得します。
SELECT name FROM sys.sysobjects WHERE id = ( SELECT constid FROM sys.sysconstraints WHERE id = [table_id] AND colid = [column_id] )
[table_id]と[column_id]には先ほど取得した各IDを入力します。
列の制約を削除
制約名が判明したので、この制約名を使い列から制約を削除します。
ALTER TABLE [table_name] DROP CONSTRAINT [制約名]
制約の削除が完了したので列を削除することが出来るようになりました。
まとめ
いかがでしたか？
今回はコマンドで列を削除する方法について解説してみました。
コマンドからの作業はあまりないので「覚える必要があるのか？」と疑問を持った方もいらっしゃると思いますが、少ないとは言え、全くないわけではありません。
また資格を目指すなら確実に必要になってくる知識でもありますので、これを機会にしっかりと覚えておくようにしましょう。
