表の定義を変更する『ALTER TABLE』の説明、および『MODIFY』句を使った列の変更について記載します。
目次
1.ALTER TABLE MODIFYとは
ALTER TABLEは表の定義を変更するSQLで、列の追加(ADD)、変更(MODIFY)、削除(DROP)ができます。ALTER TABLE MODIFY文では、既存の列のデータ型、サイズやデフォルト値の定義、列名の変更、NOT NULL制約の追加・削除をすることができます。
<MODIFY句でできること>
- データ型の変更
- サイズの拡大縮小
- デフォルト値の定義
- NOT NUL制約の追加/削除
「ADD」句を使った列の追加については、以下の記事で紹介しています。
2.ALTER TABLE MODIFYの構文
ALTER TABLE MODIFYの構文は以下の通りです。
ALTER TABLE [スキーマ.] テーブル名
MODIFY
(
列名1 [データ型(サイズ)] [DEFAULT デフォルト値] [NULL | NOT NULL] [,]
・・・・・・・・・
);
例：NOT NULL制約の削除
ALTER TABLE 表名
MODIFY (列名 NULL);
例：NOT NULL制約の追加
ALTER TABLE 表名
MODIFY (列名 NOT NULL);
留意点
- 数値型、文字型のサイズの拡大は常に可能
- 数値型のサイズ縮小は、列にNULLのみが格納時、または表が空の場合は可能
- 文字型のサイズ縮小は現在格納している値未満でなければ可能
- 列にNULLだけが格納されている場合は、データ型の変更が可能
- デフォルト値の変更は、変更後のINSERT時に影響
3.ALTER TABLE MODIFYの練習
実際に作成したテーブルの列を変更してみましょう。
<test3テーブルの作成>
CREATE TABLE test3(
id NUMBER(6) CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR2(15) NOT NULL,
salary NUMBER(10,2),
UPDATEDATE DATE,
INSERTDATE DATE DEFAULT SYSDATE
);
<test3テーブルへのデータの挿入>
INSERT INTO test3
(id, name,salary,updatedate)
VALUES
(1,'sato',500,'2020/1/1');
INSERT INTO test3
(id, name,salary,updatedate)
VALUES
(2,'suzuki',10000,'2020/1/1');
INSERT INTO test3
(id, name,salary,updatedate)
VALUES
(3,'ouchi',800,'2020/1/1');
<test3テーブルのUPDATEDATEの「DATE」型を「TIMESTAMP型」に変更>
ALTER TABLE test3
MODIFY (UPDATEDATE TIMESTAMP(6));
データ型がCREATE TABLEで指定した「DATE」から「TIMESTAMP」に変更されているのが分かります。
<NAME列のNOT NULL制約の削除>
ALTER TABLE test3
MODIFY (NAME NULL);
NOT NULL制約を削除したため、NAMEの必須列の「YES」が消えています。
<NAME列のサイズ変更 拡大>
ALTER TABLE test3
MODIFY (NAME VARCHAR2(25));
サイズがVARCHAR2(15)からVARCHAR2(25)に変更されました。
<NAME列のサイズ変更 縮小>
文字列のサイズ縮小は現在格納している値より小さくはできないので、現在の文字数を確認後、サイズを縮小したいと思います。
・文字数の確認
select NAME,LENGTH(NAME) from test3;
※現在、NAME列に入っているデータの最大文字数は6であることをSELECT分で確認します。
・VARCHAR(6)に変更
ALTER TABLE test3
MODIFY(NAME VARCHAR(6));
サイズが縮小されました。
