Oracle 列(カラム)の追加・変更・削除する方法 ALTER TABLE

列(カラム)追加には ALTER TABLE 表名 ADD ( 列名 型(桁),... ) を使います。

ALTER TABLE EMPLOYEES ADD (
  EMAIL   NVARCHAR2(200),
  SALARY  NUMBER(12,2)
);

列(カラム)変更には ALTER TABLE 表名 MODIFY ( 列名 型(桁),... ) を使います。

ALTER TABLE EMPLOYEES MODIFY (
  EMAIL   NVARCHAR2(255)
);

列(カラム)削除には ALTER TABLE 表名 DROP ( 列名,... ) を使います。

ALTER TABLE EMPLOYEES DROP (
  EMAIL
);

ALTER TABLE コマンドだけでは途中に列を差し込むことはできません。一時的にデータ退避するテーブル(テンポラリテーブル)を作り、そこへデータを移行、元テーブルをドロップ、テンポラリテーブル名を元テーブル名にリネーム、プライマリキーを作成、・・・という手順となります。あらかじめバックアップを取っておくのと、レコード件数を確認しておくことがポイントです。

例として「EMPLOYEES」というテーブルに「EMAIL」と「CREATED_AT」の間に「SALARY」という列を追加してみます。下記のSQLを実行します。

CREATE TABLE EMPLOYEES
(
  ID            NVARCHAR2(255) NOT NULL,
  NAME          NVARCHAR2(255) NOT NULL,
  EMAIL         NVARCHAR2(255),
  CREATED_AT    DATE NOT NULL,
  UPDATED_AT    DATE,
  CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID) USING INDEX
);

次にテンポラリテーブルを作成するため下記のSQLを実行します。

CREATE TABLE TEMP_EMPLOYEES
(
  ID            NVARCHAR2(255) NOT NULL,
  NAME          NVARCHAR2(255) NOT NULL,
  EMAIL         NVARCHAR2(255),
  SALARY        NUMBER(12,2) NOT NULL,
  CREATED_AT    DATE NOT NULL,
  UPDATED_AT    DATE
);

次にテンポラリテーブルへデータ移行してコミットします。

INSERT INTO TEMP_EMPLOYEES (
  SELECT
   ID
  ,NAME
  ,EMAIL
  ,0 AS SALARY
  ,CREATED_AT
  ,UPDATED_AT
  FROM EMPLOYEES
);
COMMIT;

いったん、元表は削除します。

DROP TABLE EMPLOYEES PURGE;

PURGEとは「ごみ箱」に残さない方法です。詳しくは「Oracle RECYCLEBIN(ごみ箱)を確認して削除や復旧する方法」を参考にしてください。

次に表名(テーブル名)を変更します。

RENAME TEMP_EMPLOYEES TO EMPLOYEES;

新テーブルへプライマリーキーを追加しましょう。

ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY(ID);

最後にレコード件数を確認し、前テーブルと一致していることを確認します。

SELECT COUNT(1) FROM EMPLOYEES;

以上で作業は終了です。

プロジェクトによっては、プライマリーキーは先頭列に、タイムスタンプの管理列は最後列に、というような制約があります。そのため、どうしても途中に差し込まなければ・・・って場合に使える方法ですが、途中で失敗するとかなり危険です。必ずバックアップを取ってから作業をおこないましょう。本番環境へ適用する場合は、開発環境などで試してから実施すると安心です。

列(カラム)変更する場合、桁拡張なら問題ないですが、逆の桁縮小や型変更などをおこなう場合には、どうしても該当列のデータを削除しなければならず、消せない場合もあるし、作業自体も不安になりますよね。

こんな時に私がよくやる方法がこれです。別表にデータバックアップして、カラム変更してデータ戻すって方法ですね。

まずは該当テーブルのバックアップを別名で保存してコミットします。

CREATE TABLE BACKUP_EMPLOYEES AS SELECT * FROM EMPLOYEES;
COMMIT;

次に該当テーブルの全データを削除します。

TRUNCATE TABLE EMPLOYEES;

さあ、目的の該当カラムの桁を縮小しますよ。

ALTER TABLE EMPLOYEES MODIFY (
  EMAIL   NVARCHAR2(100)
);

元のテーブルへデータを戻しましょう。

INSERT INTO EMPLOYEES SELECT * FROM BACKUP_EMPLOYEES;

最後にバックアップテーブルを削除したらおしまいです。

DROP TABLE BACKUP_EMPLOYEES PURGE;

ただし、型変更の場合には元の型から新しい型へ変換できることが前提です。「元のテーブルへデータを戻す」の際に、何がしらの変換ロジックを使えば挿入することはできますが、型変更する場合には十分注意して実施してください。

Oracleで列(カラム)の追加・変更・削除する方法を紹介しました。

既存のテーブルに対して、何かの処理を実施するというのは、何度やっても緊張するものです。

何度もいいますが、うっかりデータを消したりして事故になる前に、必ずバックアップを取ってから実施しましょう。バックアップさえあれば戻すことが可能で、事故をなかったことにできますが、バックアップが取られていないと、もうどうしようもありません。

列の追加・変更・削除に関わらず、バックアップは常に意識して作業したいものですね。