auroralights’s diary

SESの客先常駐。35歳。仕事でへとへとです。このブログではデータベース、Excel、Excel VBA、ネットワークなどIT関連のことを主に書いていきます。

SQL基礎 ALTER TABLE MODIFYを使って列の変更  oracleデータベース

表の定義を変更する『ALTER TABLE』の説明、および『MODIFY』句を使った列の変更について、記載します。

 

 

1.ALTER TABLE MODIFYとは

ALTER TABLEは表の定義を変更するSQLで、列の追加(ADD)、変更(MODIFY)、削除(DROP)ができます。ALTER TABLE MODIFY文では、既存の列のデータ型、サイズやデフォルト値の定義、列名の変更、NOT NULL制約の追加・削除をすることができます。

 

<MODIFY句でできること>

・データ型の変更

・サイズの拡大縮小

・デフォルト値の定義

・NOT NUL制約の追加/削除

 

なお、「ADD」句を使った列の追加は、以前紹介しました。

auroralights.hatenablog.com

 

 

 

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));

 

f:id:auroralights:20200325235455p:plain

データ型がCREATE TABLEで指定した「DATE」から「TIMESTAMP」に変更されているのが分かります。

<NAME列のNOT NULL制約の削除>

ALTER TABLE test3
MODIFY (NAME NULL);

 

f:id:auroralights:20200326000219p:plain

 NOT NULL制約を削除したため、NAMEの必須列の「YES」が消えています。

 

<NAME列のサイズ変更 拡大>

ALTER TABLE test3
MODIFY (NAME VARCHAR2(25));

 

f:id:auroralights:20200326000634p:plain

サイズがVARCHAR2(15)からVARCHAR2(25)に変更されました。

 

<NAME列のサイズ変更 縮小>

文字列のサイズ縮小は現在格納している値より小さくはできないので、現在の文字数を確認後、サイズを縮小したいと思います。

 

・文字数の確認

select NAME,LENGTH(NAME) from test3;

※最大文字数は6でした

 

・VARCHAR(6)に変更

ALTER TABLE test3
MODIFY(NAME VARCHAR(6));

 

f:id:auroralights:20200326001110p:plain

サイズが縮小されました。