ORACLE 列(カラム)の追加・変更・削除する方法 ALTER TABLE ホームページ制作 | 墨田区

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

LINEで送る
Pocket

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

正直このコマンド、いつもいつも実施するわけではないので、忘れがちです。そんな訳で、忘れないようにまとめておきます^^;

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




【PR】マジか?!「アレ」してるLINEスタンプっていったい・・・


列(カラム)追加

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

下表に EMAIL と CREATED_AT の間に SALARY という列を追加します。
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
);

1. テンポラリテーブル作成
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
);

2. データ移行してコミット
INSERT INTO TEMP_EMPLOYEES (
  SELECT
   ID
  ,NAME
  ,EMAIL
  ,0 AS SALARY
  ,CREATED_AT
  ,UPDATED_AT
  FROM EMPLOYEES
);
COMMIT;

3. 元表削除
DROP TABLE EMPLOYEES PURGE;

4. 表名変更
RENAME TEMP_EMPLOYEES TO EMPLOYEES;

5. プライマリーキー追加
ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY(ID);

6. レコード確認
SELECT COUNT(1) FROM EMPLOYEES;

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

列(カラム)変更する場合のおすすめ手順

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

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

1. 該当テーブルのバックアップを別名で保存してコミット
CREATE TABLE BACKUP_EMPLOYEES AS SELECT * FROM EMPLOYEES;
COMMIT;

2. 該当テーブルの全データを削除
TRUNCATE TABLE EMPLOYEES;

3. 該当カラムの桁縮小
ALTER TABLE EMPLOYEES MODIFY (
  EMAIL   NVARCHAR2(100)
);

4. 元のテーブルへデータを戻す
INSERT INTO EMPLOYEES SELECT * FROM BACKUP_EMPLOYEES;

5. バックアップテーブルを削除
DROP TABLE BACKUP_EMPLOYEES;

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

まとめ

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

何度もいいますが、うっかりデータを消したりして事故になる前に、必ずバックアップを取ってから実施しましょう。

バックアップさえあれば戻すことが可能で、事故をなかったことにできますが、バックアップが取られていないと、もうどうしようもありません。

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

おつかれさまでした。

LINEで送る
Pocket

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

コメントを残す

コメント(必須)

お名前 (必須)
メールアドレス
(アドレスは公開されません)

Trackback URL