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

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

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

Oracleで表(テーブル)定義の変更する場合、つまりテーブル列(カラム)の追加や桁数変更、カラム削除をおこなう場合にはALTER TABLEを使用します。

正直このALTERコマンド、いつもいつも実施するわけではないので忘れがちです。そんな訳で忘れないように記事に残しておきますね。

ここでは Oracleで表の途中に列を追加する方法 や、カラム変更時のおすすめの方法 を紹介します。


列(カラム)追加

列(カラム)追加には 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;

元表削除

表名変更

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

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で列(カラム)の追加・変更・削除する方法を紹介しました。

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

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

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

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。