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

 

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

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

 ① M_USERのテーブル名を変更する(バックアップ)

 ② 新しスキーマーのM_USERを新規作成(NAME_KANA)

 ③ シーケンスの再作成

 ④ トリガーの再作成

 ⑤ ①のテーブルのデータを②のテーブルに移行する

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

 

① 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

 

以上です。



投稿日:2021-01-27    更新日:2021-02-10

[スポンサーリンク]

[スポンサーリンク]

  
関連記事
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
サイト内検索
プロフィール

プロフィール

[Name : POCO(@PocoIt2019)]
都内で社内SEをしているおじさん。
仕事で得られる知識だけでは限界を感じ、 WEBの勉強がてらITブログを開始。
サーバからWEBサイトまでフルスクラッチで開発しました。
現在は勉強のモチベーションを保つために活用中。
興味があることを雑記的に書いていきます。

[スポンサーリンク]

カテゴリ


タグ

[スポンサーリンク]

最近の記事