ALTER TABLE （列の追加、変更、削除）
カラムの追加 ／ ALTER TABLE ADD column(s)
表へカラムの追加
表定義の最後にのみ追加可能。表の途中への挿入や列の順番の変更や入れ替えはできない。表の再定義(※1)とデータの再登録を行なう必要がある。
- my_table に TIMESTAMP カラム time_col を追加する。
ALTER TABLE my_table ADD ( time_col TIMESTAMP(3) ) ;
- my_table に仮想列 vir_col を追加する。
ALTER TABLE my_table ADD ( vir_col as (col1 + col2) ) ;
仮想列のデータ型を指定するには
ALTER TABLE my_table ADD ( vir_col as (cast(col1 + col2 as NUMBER(3))) ) ;
- my_table に 200バイト長の VARCHAR2 カラム char_col と有効桁数 10 の NUMBER カラム number_col をテーブルの末尾に追加する。char_col にはデフォルト値と NOT NULL 制約を付ける。
ALTER TABLE my_table ADD (
char_col VARCHAR2(200 byte) DEFAULT 'xxx' NOT NULL ,
number_col NUMBER(10)
);
デフォルト値を指定している場合には既存行の該当カラムはデフォルト値で埋められる。
(※1) 表のオンライン再定義機能を使うことでシステムを稼動しながらカラム位置などのテーブル定義の変更ができる。
カラムの定義の変更 ／ ALTER TABLE MODIFY column(s)
カラムの長さ、桁数、スケール、精度の変更
カラムのサイズは増加、または、縮小が可能（※2)
もしデータがすべて NULL の場合には データ型 の変更も可能 (※3)
ALTER TABLE my_table MODIFY ( char_col VARCHAR2(80 char) ) ;
カンマ区切りで同時に複数カラムの変更も可能。
ALTER TABLE my_table MODIFY (
char_col VARCHAR2(50),
number_col NUMBER(14)
);
(※2) 注意
- カラムの データタイプ が文字、バイナリ属性であれば列長の縮小が可能。だだし、テーブルに存在するデータの最大長未満にはできない。
- 数値属性の精度およびスケール変更に関しては上方修正（増加）しか行なうことはできない。(〜Oracle 11gR2 時点)
下方修正すると 「ORA-01440: 精度またはスケールを下げるには、修正する列を空にする必要があります」 が発生する。
精度は同じでスケールだけを増やしても下方修正になるのでエラーとなる。例) NUMBER(10,1) ⇒ NUMBER(10,2)
- 日時属性も同様にスケールダウンはできないが TIMESTAMP(9) ⇒ DATE ⇒ TIMSTAMP(6) といったデータ型の変換は可能。変換後のデータ型で保持できない部分に関しては変換の際に丸められる。
(※3) データが存在する場合でも TIMEATAMP 型 から DATE 型への変更などで NULL でない場合でもすり抜け可能なパターンはいくつかある。
SQL> INSERT INTO my_table (time_col) VALUES ( TIMESTAMP '2000-01-01 01:02:03.456' );
1行が作成されました。
SQL> ALTER TABLE my_table MODIFY ( time_col DATE ) ;
表が変更されました。
デフォルト値の変更と削除
- my_table の char_col カラムのデフォルト値を 'abc' に変更する。
ALTER TABLE my_table MODIFY ( char_col DEFAULT 'abc' ) ;
デフォルト値を削除する構文はない。
デフォルト値を NULL にすることで同じ振る舞いにすることはできる。
データ・ディクショナリ には残るので再作成できるなら DROP TABLE して CREATE TABLE し直した方が精神的には良いかも。
- my_table の char_col のデフォルト値 を NULL にすることで事実上削除する。
ALTER TABLE table_name MODIFY ( char_col DEFAULT NULL ) ;
補足：デフォルト値の変更は、既存データの NULL 値 をデフォルト値で上書きすることはない。
NULL 制約の設定と解除
- my_table の char_col を NOT NULL に変更する。
ALTER TABLE my_table MODIFY ( char_col NOT NULL ) ;
- my_table の char_col を NULLable に変更する。
ALTER TABLE table_name MODIFY ( char_col NULL )
注意：NULL(NOT NULL) の指定は、現在設定と異なる場合に指定する。現在の設定と同じ指定を行うとエラーになる。
３種盛り合わせ
ALTER TABLE my_table MODIFY (
char_col VARCHAR2(2000) default 'abc' not null,
number_col NUMBER(20) default -1 not null
);
テーブル名、カラム名の変更 ／ ALTER TABLE RENAME [column]
テーブル名の変更
ALTER TABLE old_table_name RENAME TO new_tablename ;
カラム名の変更
- my_table の time_col を date_col に変更する。
ALTER TABLE my_table RENAME COLUMN time_col TO date_col ;
ALTER TABLE UNUSED と DROP column(s)
カラムの使用禁止 (UNUSED)
１列でも複数列でも可能な形式
ALTER TABLE table_name SET UNUSED (
col1 [, col2 [, col3 … ]]
)
[CASCADE CONSTRAINTS] [INVALIDATE] ;
１列のみで使用できる形式
ALTER TABLE table_name SET UNUSED COLUMN
colname
[CASCADE CONSTRAINTS] [INVALIDATE] ;
使用禁止列の削除
ALTER TABLE table_name DROP UNUSED COLUMN ;
カラムの削除 (DROP)
１列でも複数列でも可能な形式
- my_table から char_col と number_col を削除する。
ALTER TABLE my_table DROP (
char_col , number_col
)
-- 以下はオプションなので必須ではない
CASCADE CONSTRAINT INVALIDATE;
１列のみで使用できる形式
- my_table から date_col を削除する。
ALTER TABLE my_table DROP COLUMN
date_col ;
-- 以下はオプションなので必須ではない
-- CASCADE CONSTRAINT INVALIDATE
オプション指定
- CASCADE CONSTRAINT : 列に付随する制約（主キー、ユニークキー、チェックなど）を削除する。
- INVALIDATE : 依存関係にあるオブジェクトに対し再帰的に無効化マーク(INVALID) を付ける。
- CHECKPOINT : UNDO に蓄積されるログを抑制するため、指定行数毎に チェックポイント を発生させる。
ALTER TABLE PRIMARY KEY
⇒ プライマリキーの追加、変更、削除
関連事項
