1.ALTER TABLE文とは
表作成後、何らかの理由で表の定義を変更する場合があります。そのときに使用するDDL文がALTER TABLE文です。
2.ALTER TABLE文による表の変更
ALTER TABLE文では、次のことができます。
- 新しい列の追加
- 既存の列の定義(データ型・サイズ・デフォルト値等)の変更
- 既存の列の削除
- 既存の列名の変更
- 表のモード変更(読み取り/書き込みモード、読み取り専用モード)
3.列の追加(ALTER TABLE ADD)
ADD句により列を追加できます。
ALTER TABLE 表名 ADD
( 列名 データ型(サイズ) [DEFAULT 値] [[CONSTRAINT 制約名] 制約タイプ,]
[列名 データ型(サイズ)] ...
);
列を追加する表に、すでに行が格納されている場合は、以下の制約が適用される。
- 追加する列にデフォルト値を指定しない場合、すでに格納される行の追加する列にはNULLが格納される。
- 追加する列にNOT NULL制約を設定する場合、デフォルト値を定義する必要がある。
4.列の定義変更(ALTER TABLE MODIFY)
MODIFY句により、列の定義(データ型・サイズ・デフォルト値・NOT NULL制約の有無)を変更できます。
ALTER TABLE 表名 MODIFY
(列名 [データ型] [DEFAULT 値] [NULL| NOT NULL],
[データ型] [DEFAULT 値] [NULL| NOT NULL]...
);
EMP1表のbirthdate列をDATE型からTIMESTAMP型へ変更しています。
ALTER TABLE emp1 MODIFY (birthdate TIMESTAMP);
列の変更に関する主な注意点
- 表に行データがある状態で、列のデータ型を変更する場合、原則的に、変更対象の列が全行についてNULLである必要があります。例外は以下のデータ型の変更です。
- DATE型をTIMESTAMP型またはTIMESTAMP WITH LOCAL TIME ZONE型に変更
- TIMESTAMP型またはTIMESTAMP WITH LOCAL TIME ZONE型をDATE型に変更
- 表に行データがある状態で、列のデータ型のデータサイズを変更する場合、データサイズを大きくする変更のみが実行できます。
5.列の削除(ALTER TABLE DROP)
DROP句により既存の列を削除できます。
ALTER TABLE 表名 DROP ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
列の削除に関しては、以下の注意事項があります。
・削除した列は戻せない
・表には最低1つの列を残す必要がある
・他から参照されるFOREIGN KEY制約の親表の列はCASCADE CONSTRAINTSオプションを指定する必要がある
・パーティション化キー列（表をパーティション化するためのキー列）は削除できない
・列に多くのデータが含まれている場合は、削除に時間がかかる
6.列の未使用化(ALTER TABLE SET UNSED)
SET UNSED句を使い、既存の列を「UNSED(未使用)」にできます。
ALTER TABLE文で既存の表の列を削除できますが、列の削除中は表にロックがかかり、列に多くのデータが含まれている場合は削除に時間がかかります。
この機能は、多くのユーザーがデータベースを利用する時間帯に負荷の高い削除処理を行いたくない場合の対処策として使用します。
ALTER TABLE 表名 SET UNUSED ( 列名 [, 列名...]) [CASCADE CONSTRAINTS];
SET UNUSED句に関しては、以下の注意事項があります。
- UNUSEDに設定した列は戻せず、DESCRIBEコマンドなどで列名やデータ型を確認できなくなる
- UNUSEDにした列に作成された索引や制約は削除される
- UNUSEDにした列と同じ名前の列を表に追加できる
- UNUSEDにした列の表名と列数は「USER_UNUSED_COL_TABS」ディクショナリで確認できる
- 表に対して作成したシノニムの再作成は必要ない
- UNUSEDにした列を含むビューは無効になる