ORACLEのALTER TABLEのADD、MODIFY、DROP、RENAMEをどこよりも詳しく解説する

ALTER TABLEは表定義を変更するDDLです。表定義はCREATE文で表を作成する時に定義しますが、運用上、途中で列追加やデータサイズの変更が必要となるシーンはよくあります。DDLの中でも良く使う部類なので、しっかり理解しておく必要があります。ALTER TABLEを間違えると表の構造が変わる=入れるデータが変わる=データが壊れるという炎上スパイラルに入ります。

覚えるべきキーワード
・ALTER TABLE 表名 ADD 列名 データ型(サイズ)
・ALTER TABLE 表名 ADD CONSTRAINT | 制約名 制約(列名)
・ALTER TABLE 表名 MODIFY 列名 データ型(サイズ)
・ALTER TABLE 表名 MODIFY 列名 DEFAULT 値
・ALTER TABLE 表名 MODIFY 列名 制約名
・ALTER TABLE 表名 DROP COLUMN 列名 CASCADE CONSTRAINTS
・ALTER TABLE 表名 DROP COLUMN 列名 CONSTRAINT 制約名
・ALTER TABLE 表名 RENAME TO 変更後の表名
・ALTER TABLE 表名 RENAME COLUMN 列名 TO 変更の列名
・ALTER TABLE 表名 SET UNUSED 列名
・ALTER TABLE 表名 DISABLE CONSTRAINT 制約名

ADDとMODIFYとDROPがALTER TABLEの代表的なDDLです。ADDは列や制約、DEFAULTの追加、MODIFYは列や制約、DEFAULTの変更、DROPは列や制約の削除ができます。ADD、MODIFY、DROP以外にもRENAMEやUNUSEDなどありますが、基本使うのはこの3つです。

ALTER TABLE 表名 ADD 列名 データ型(サイズ)

それではまずADDから見ていきます。ADDは列やDEFAULT、制約の追加を行います。書き方はCREATE文で書くときと同じです。ADDを実行する上で注意しないといけないことがDEFAULT設定と制約の追加です。すでにデータが入っているテーブルにDEFAULT設定なしで列追加のADDを実行したら、そのデータ達のADDした列値には自動的にNULLが入ってしまいます。そのためNULL値を入れたくない列なら、列をADDするタイミングでDEFAULT指定を行いましょう。
制約の追加も注意が必要です。
すでにデータが入っている列に制約を追加する場合、その列に重複するデータがあるのに、PRIMARY KEY制約やUNIQUE制約は指定できません。すでに制約に違反しているデータがある列に制約は指定できません。制約を追加する場合は、対象列のデータを調査したうえでADD制約を実行しましょう。

ではさっそく、SQLのサンプルです。

ALTER TABLE 表名 ADD 列名 データ型(サイズ)

ALTER TABLE employee ADD deptno VARCHAR2(100);

employee表にVARCHAR2で100バイトのdeptnoを追加したSQLです。これでdeptnoは追加できます。ちなみにテーブルの中で列が追加する位置は最後になります。そのため表の最初に追加したい場合は、
ALTER TABLE employee ADD deptno VARCHAR2(100) FIRST;のように最後にFIRSTを付けてください。

ALTER TABLE 表名 ADD CONSTRAINT 制約名 制約(列名)

列に制約を追加する時は CREATE文の表レベル制約と同じ書き方です。

ALTER TABLE 表名 ADD CONSTRAINT 制約名 制約(列名)

ALTER TABLE employee ADD CONSTRAINT emp_id_uk UNIQUE(id);

これはemployee表にすでにあるid列にUNIQUE制約を制約名emp_id_ukで追加するSQLです。表レベル制約と同じ書き方ですね。UNIQUEを付けるので、すでにid列に重複するデータが入っていたらエラーになります。ADD制約を行う場合は、制約を追加できる列なのかを事前に調査する必要があります。

ALTER TABLE 表名 MODIFY 列名 データ型(サイズ)

次はMODIFYです。MODIFYは既存のテーブル定義を変更する時に使います。まずは列名のデータサイズを変更するSQLです。もともとは1~9までの一桁数字だけで良いと思っていたけど、システムを運用していくうちに10以降の二桁数字も使う必要性が出てきた場合などにMODIFYでデータ型を変更します。
次のSQLはnumber(4)のidとVARCHAR2(20)のname列で構成されているemployeeテーブルのnumberを10バイト、nameを100バイトに変更するSQLです。

ALTER TABLE 表名 MODIFY 列名 データ型(サイズ)

ALTER TABLE employee MODIFY
(id number(10),
name VARCHAR2(100));

ORACLEでは、この書き方でデータサイズが変更できます。変更前の定義は記載する必要がなく、変更後のサイズを書くだけでOKです。

ALTER TABLE 表名 MODIFY 列名 DEFAULT 値

DEFAULTを変更することも可能です。
次のSQLは、employeeテーブルのdeptno列をDEFAULT値1から0に変更しています。

ALTER TABLE 表名 MODIFY 列名 DEFAULT 値

ALTER TABLE employee MODIFY deptno default 1;

DEFAULTの後に変更後の値を書くだけです。簡単です。

ALTER TABLE 表名 MODIFY 列名 制約名

MODIFYは制約を変更することもできます。しかしながら、すべての制約が変更できるのではなく、変更できる制約は、NOT NULL制約のみです。MODIFYでNOT NULL制約の追加と削除ができます。
以下のSQLは、employeeのname列に設定されていたNOT NULL制約を削除するSQLです。制約名はemp_name_nnです。

ALTER TABLE 表名 MODIFY 列名 制約名

ALTER TABLE employee MODIFY name CONSTRAINT emp_name_nn NULL;

NOT NULL制約emp_name_nnを削除しました。書き方としては削除と言うより、NULL制約を付与するようなイメージです。(NULL制約というものはありませんが)
制約のついていないemployeeのid列にNOT NULL制約を追加するSQLは以下です。制約名はemp_id_nnにします。

ALTER TABLE employee MODIFY id CONSTRAINT emp_name_id NOT NULL;

これでid列にNOT NULL制約が定義されました。

NOT NULLの追加も削除も制約名を書いていますが、これは省略しても良いので、書くのが面倒な場合は CONSTRAINT 制約名を省略してください。

ALTER TABLE 表名 DROP COLUMN 列名 | CASCADE CONSTRAINTS

列や制約の削除に使うDROPですが、まずは列削除からです。次のSQLはemployee表のid列を削除しています。

ALTER TABLE 表名 DROP COLUMN 列名

ALTER TABLE employee DROP COLUMN id;

COLUMNの記載を忘れずに。あと、もし、このid列が他表の列から外部キー制約を指定されていたら(つまり、親キー列だったら)このSQLではエラーになります。
親キー設定されている列を削除するのは、CASCADE CONSTRAINTSを最後に付けてください。

ALTER TABLE 表名 DROP COLUMN 列名 CASCADE CONSTRAINTS

ALTER TABLE employee DROP COLUMN id CASCADE CONSTRAINTS;

これでエラーなく、実行できます。

ALTER TABLE 表名 DROP COLUMN 列名 CONSTRAINT 制約名

DROPは制約の削除もできます。employee表のid列にemp_id_ukという制約名のUNIQUE制約がついていたら、以下のように書けば削除できます。

ALTER TABLE 表名 DROP COLUMN 列名 CONSTRAINT 制約名

ALTER TABLE employee DROP COLUMN id CONSTRAINT emp_id_uk;

ALTER TABLE 表名 RENAME TO 変更後の表名

表名の変更はRENAMEで実行可能です。employee表をjyugyouin表に変更する場合は以下のように書きます。

ALTER TABLE 表名 RENAME TO 変更後の表名

ALTER TABLE employee RENAME TO jyugyouin;

RENAMEで簡単にテーブル名は変更できますが、正直、運用中の表に対するRENAMEはあまりオススメしません。テーブル名を変えるとアプリ側のSQL記述のテーブル名もそれに合わせて書き変えないといけないからです。手間が増えるので、簡単にはRENAMEしない方が良いです。ただ、やむ負えない場合やまだ運用前(リリース前)ならRENAMEしても良いと思います。

ALTER TABLE 表名 RENAME COLUMN 列名 TO 変更の列名

RENAMEで列名も変更可能です。運用中の表の列名なら、あまりRENAMEするのはおすすめしません。SQL記述ですでに列名を指定して取得していたり、列名でテーブル同士を結合していたりすると、RENAMEより、それらの列名まで書き変える手間が増えるからです。ただ、表のRENAMEと同様、やむ負えない場合やまだ運用前(リリース前)ならRENAMEしても良いと思います。
以下のSQLはid列をdeptidに表明変更しています。

ALTER TABLE 表名 RENAME COLUMN 列名 TO 変更の列名

ALTER TABLE employee RENAME COLUMN id TO deptid;

ALTER TABLE 表名 SET UNUSED 列名

SET UNUSEDは使用していた列を削除状態にすることができるSQLです。列削除はサーバ負荷が大きいので、とりあえず削除状態にしておいて、サーバ負荷が小さいタイミングを見計らって削除するためにSET UNUSEDは使います。

employee表のbdをUNUSEDにするSQLです。

ALTER TABLE 表名 SET UNUSED 列名

ALTER TABLE employee SET UNUSED bd;

SET UNUSEDを実行するとDMLで使えなくなるし、DESCにもその列は出てこなくなります。実質、削除と同じですね。unusedした列を削除する時は書き方には注意が必要です。さきほどUNUSEDに設定したemployee表のidを削除してみましょう。

ALTER TABLE 表名 DROP UNUSED COLUMNS

ALTER TABLE employee DROP UNUSED COLUMNS;

特に列名は記述しません。UNUSEDした列をこれで削除します。

ALTER TABLE 表名 DISABLE/ENABLE CONSTRAINT 制約名

一時的に制約を無効にするSQLもあります。データを直接いじりたい時に制約が邪魔になる時があります。そんな時に制約の無効化は便利です。
次は、employee表のemp_id_ukというUNIQUE制約を無効化するSQLです。

ALTER TABLE 表名 DISABLE CONSTRAINT 制約名

ALTER TABLE employee DISABLE CONSTRAINT emp_id_uk;

これでemp_id_ukは無効になりました。有効化したい時は以下のように書きます。

ALTER TABLE 表名 ENABLE CONSTRAINT 制約名

ALTER TABLE employee ENABLE CONSTRAINT emp_id_uk;

DISABLEをENABLEに変更するだけです。

以上でALTER TABLEの説明は終わりです。1つ1つの書き方はとてもシンプルです。ただし、ALTER TABLEは表構造を変えるSQLですし、DDLなのでcommit不要です。やり直しがきかないので、慎重に慎重に行いましょう。レビューを忘れずに。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です