Oracle

【Oracle】既存テーブルに指定した位置にカラムを追加する方法

Oracleはカラムを追加する際、基本は一番後ろにしかカラムを追加できません。

既存テーブルに対してカラムの途中に新しいカラムを追加するには、少し力業が必要になってきます。

出来ることならやらないほうがいいですが、SQL*PulsでLOAD DATAをする場合、テーブルのカラム位置が重要になってくるケースがあります

止む無く指定した位置にカラムを追加する必要が出てくるので、その方法をここにメモしておきます。

 

カラムを追加する

下記テーブルを例に説明していきます。

テーブル名：M_USER

ID NAME TEL MAIL 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で行けます。

ALTER TABLE M_USER ADD ( NAME_KANA VARCHAR2(10) );

実行後のテーブルはこうなります。

ID NAME TEL MAIL 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の後ろにカラムが追加されました。これで運用できる場合はいいですが、指定した位置にカラムを追加する場合は別の方法が必要です。

 

指定した位置にカラムを追加する

方法としては以下の流れで行います。

  1. M_USERのテーブル名を変更する（バックアップ）
  2. 新しスキーマーのM_USERを新規作成（NAME_KANA）
  3. シーケンスの再作成
  4. トリガーの再作成
  5. ①のテーブルのデータを②のテーブルに移行する

要は追加したいカラムを含む新テーブルを作成して、既存テーブルのデータを移行する、という流れです。

 

1．M_USERのテーブル名を変更する（バックアップ）

ALTER TABLE M_USER RENAME TO M_USER_BK;

②カラムを追加したテーブルを再作成

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

③シーケンスの再作成

最後にデータ移行する関係で、シーケンスを再作成しておきます。

シーケンス削除

DROP SEQUENCE  M_USER_SEQ;

シーケンス再作成

CREATE SEQUENCE  M_USER_SEQ  MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER  NOCYCLE;

④トリガーの再作成

既存テーブルを別名に変更したタイミングで、トリガーの中に記載されているテーブル名の自動でへこうされてしまうので、トリガーも再作成する必要があります。

トリガーの削除

DROP TRIGGER M_USER_TRIGGER;

トリガーの再作成

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

⑤データを移行する

既存テーブルのデータを新規テーブルに移行します。

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）をひとつずつデータを更新していって、最終的な完成形は以下の通りになります。

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 MAIL 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

以上です。

-Oracle