Oracleはカラムを追加する際、基本は一番後ろにしかカラムを追加できません。
既存テーブルに対してカラムの途中に新しいカラムを追加するには、少し力業が必要になってきます。
出来ることならやらないほうがいいですが、SQL*PulsでLOAD DATAをする場合、テーブルのカラム位置が重要になってくるケースがあります
止む無く指定した位置にカラムを追加する必要が出てくるので、その方法をここにメモしておきます。
目次[表示]
カラムを追加する
下記テーブルを例に説明していきます。
テーブル名:M_USER
ID | NAME | TEL | BIRTHDAY | |
---|---|---|---|---|
1 | 太郎 | 090-0000-0001 | taro@sample.com | 1986-01-01 |
2 | 次郎 | 090-0000-0002 | jiro@sample.com | 1999-01-02 |
3 | 花子 | 090-0000-0003 | hanako@sample.com | 2019-01-03 |
このテーブルに対し「氏名カナ(NAME_KANA)」を一番後ろにつける場合は以下のSQLで行けます。
1 | ALTER TABLE M_USER ADD ( NAME_KANA VARCHAR2(10) ); |
実行後のテーブルはこうなります。
ID | NAME | TEL | BIRTHDAY | NAME_KANA | |
---|---|---|---|---|---|
1 | 太郎 | 090-0000-0001 | taro@sample.com | 1986-01-01 | |
2 | 次郎 | 090-0000-0002 | jiro@sample.com | 1999-01-02 | |
3 | 花子 | 090-0000-0003 | hanako@sample.com | 2019-01-03 |
BIRTHDAYの後ろにカラムが追加されました。これで運用できる場合はいいですが、指定した位置にカラムを追加する場合は別の方法が必要です。
指定した位置にカラムを追加する
方法としては以下の流れで行います。
- M_USERのテーブル名を変更する(バックアップ)
- 新しスキーマーのM_USERを新規作成(NAME_KANA)
- シーケンスの再作成
- トリガーの再作成
- ①のテーブルのデータを②のテーブルに移行する
要は追加したいカラムを含む新テーブルを作成して、既存テーブルのデータを移行する、という流れです。
1.M_USERのテーブル名を変更する(バックアップ)
1 | ALTER TABLE M_USER RENAME TO M_USER_BK; |
②カラムを追加したテーブルを再作成
1 2 3 4 5 6 7 8 | CREATE TABLE M_USER ( ID NUMBER(10) PRIMARY KEY , NAME VARCHAR2(10) NOT NULL ,NAME_KANA VARCHAR2(10) ,TEL NUMBER(11) ,MAIL VARCHAR (255) ,BIRTHDAY DATE DEFAULT SYSDATE NOT NULL ); |
③シーケンスの再作成
最後にデータ移行する関係で、シーケンスを再作成しておきます。
シーケンス削除
1 | DROP SEQUENCE M_USER_SEQ; |
シーケンス再作成
1 | CREATE SEQUENCE M_USER_SEQ MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER NOCYCLE; |
④トリガーの再作成
既存テーブルを別名に変更したタイミングで、トリガーの中に記載されているテーブル名の自動でへこうされてしまうので、トリガーも再作成する必要があります。
トリガーの削除
1 | DROP TRIGGER M_USER_TRIGGER; |
トリガーの再作成
1 2 3 4 5 6 7 8 | CREATE OR REPLACE TRIGGER M_USER_TRIGGER before insert on M_USER for each row begin select M_USER_SEQ.nextval into :new.id from dual; end ; / ALTER TRIGGER M_USER_TRIGGER ENABLE |
⑤データを移行する
既存テーブルのデータを新規テーブルに移行します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | INSERT INTO M_USER( ID , NAME ,TEL ,MAIL ,BIRTHDAY ) SELECT ID , NAME ,TEL ,MAIL ,BIRTHDAY FROM M_USERO_BK ORDER BY ID ; |
これでやっとNAME_KANAを指定した位置に追加することができました!!
あとは氏名カナ(NAME_KANA)をひとつずつデータを更新していって、最終的な完成形は以下の通りになります。
1 2 3 | UPDATE M_USER SET NAME_KANA= 'TARO' WHERE ID=1 AND NAME = '太郎' ; UPDATE M_USER SET NAME_KANA= 'JIRO' WHERE ID=2 AND NAME = '次郎' ; UPDATE M_USER SET NAME_KANA= 'NAHAKO' WHERE ID=3 AND NAME = '花子' ; |
ID | NAME | NAME_KANA | TEL | BIRTHDAY | |
---|---|---|---|---|---|
1 | 太郎 | TARO | 090-0000-0001 | taro@sample.com | 1986-01-01 |
2 | 次郎 | JIRO | 090-0000-0002 | jiro@sample.com | 1999-01-02 |
3 | 花子 | HANAKO | 090-0000-0003 | hanako@sample.com | 2019-01-03 |
以上です。