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

改修や障害対応でテーブルにカラムを追加するといったことは結構頻繁にあり、MySQLでは指定した位置にカラムを追加することができますが、Oracleを含めたその他のDB（SQL ServerやPostgres）ではそれができません。この場合、テーブルの新規作成やリネーム、データ移行といったことをやらないといけないので、そのメモになります。

テーブルの一番後ろに追加するだけなら、以下のSQLで追加可能です。

ALTER TABLE PRODUCTS ADD ( NOTE VARCHAR2(20 CHAR) );

上記は、PRODUCTSというテーブルの一番後ろに「NOTE」というカラムを追加するSQLになります。

通常はこれでいいのですが、追加するカラムによっては最後ではなく、途中に追加したいといった場合があります。

例えば「ID」「NAME」「PRICE」というカラムを持つ「PRODUCTS」テーブルがあったとして、「NAME」の後ろに「NAME2」というカラムを追加したい場合、以下のような手順で行います。

-- ① 一時テーブル作成（主キーやインデックスはここでは作成しない）
CREATE TABLE PRODUCTS_BK 
(
  ID NUMBER(*,0) NOT NULL ENABLE, 
  NAME VARCHAR2(20 CHAR), 
  NAME2 VARCHAR2(20 CHAR), 
  PRICE NUMBER(*,0)
);

-- ② データコピー
INSERT INTO PRODUCTS_BK  
SELECT ID, NAME, '', PRICE
FROM PRODUCTS;

-- ③ 元テーブルと関連する制約を削除（完全に削除する場合はCONSTRAINTSの後に「PURGE」を追加）
DROP TABLE PRODUCTS CASCADE CONSTRAINTS;

-- ④ テーブル名変更
ALTER TABLE PRODUCTS_BK RENAME TO PRODUCTS;

-- ⑤ 主キー追加
ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (ID);

まず、最終的なテーブルを別名で作成しておき（①）、元テーブルからデータのコピー（②）、元テーブルを制約含めて削除したら（③）、別名で作成しておいたテーブルをリネームして（④）、最後に主キーやインデックスを追加します（⑤）。

注意する点としては、テーブルを削除する場合は「CASCADE CONSTRAINTS」で制約ごと削除する点で、主キーやインデックスはDB単位で名称を一意にしないといけないので、一時テーブル作成時に一緒に作成したり、テーブル削除で制約を削除していなかった場合、エラーが発生します。

テーブル削除を「PURGE」なしで行った場合、間違って削除してしまった時でも、後でOracle上のごみ箱から削除前の状態をテーブル単位で復活させることができるので、万が一のことを考えて「PURGE」なしで実行した方がいいと思います。