【Oracle】ALTER TABLE 完全ガイド｜列の追加・変更・削除・制約操作・列名変更・DBMS_REDEFINITION まで解説

-- 列を追加する（既存行は NULL / DEFAULT 値になる）
ALTER TABLE orders ADD status VARCHAR2(20) DEFAULT 'PENDING' NOT NULL;
-- DEFAULT 値を指定すると既存行にも DEFAULT が設定される（データを更新せずに追加できる）
-- NOT NULL + DEFAULT の組み合わせ: 12c 以降では既存行に DEFAULT 値を設定しながら NOT NULL を追加できる

-- 複数列を一度に追加する
ALTER TABLE orders
ADD (
    created_by  VARCHAR2(50),
    updated_at  DATE DEFAULT SYSDATE,
    version     NUMBER(10) DEFAULT 0 NOT NULL
);

-- INVISIBLE 列として追加する（SELECT * には表示されない）
ALTER TABLE orders ADD internal_flag NUMBER DEFAULT 0 INVISIBLE;
-- INVISIBLE 列は明示的に列名を指定しないと参照されない
-- 既存アプリに影響を与えずに列を追加するときに使う（12c 以降）

-- VARCHAR2 のサイズを拡大する（縮小は既存データが収まる場合のみ可能）
ALTER TABLE orders MODIFY status VARCHAR2(50);

-- NOT NULL 制約を追加する（NULL 値がある場合は ORA-02296 エラー）
-- ① まず NULL 値を UPDATE してから
UPDATE orders SET status = 'PENDING' WHERE status IS NULL;
COMMIT;
-- ② NOT NULL を追加する
ALTER TABLE orders MODIFY status NOT NULL;

-- NOT NULL 制約を削除する（NULL を許可にする）
ALTER TABLE orders MODIFY status NULL;

-- DEFAULT 値を変更する
ALTER TABLE orders MODIFY status DEFAULT 'CREATED';

-- DEFAULT 値を削除する（DEFAULT なしに戻す）
ALTER TABLE orders MODIFY status DEFAULT NULL;

-- 列の位置を変更する（12c 以降: INVISIBLE → VISIBLE で末尾に追加されるが順序変更は困難）
-- 列の順序変更はテーブル再作成が必要なケースがほとんど

-- NUMBER の精度を上げる（精度を下げるのは既存データの範囲内のみ）
ALTER TABLE products MODIFY price NUMBER(12, 2);  -- NUMBER(10,2) → NUMBER(12,2) に拡大

-- DATE → TIMESTAMP への変更（暗黙的なキャストが必要な場合は DBMS_REDEFINITION を使う）
-- ※ データ型の変更は制限が多い。基本的に互換性のある変更のみ可能

-- SET UNUSED: 列をすぐに使用不能にする（実データは削除されない・高速）
-- ※ SET UNUSED にした列は SELECT・INSERT・UPDATE では参照できなくなる
ALTER TABLE orders SET UNUSED (internal_flag);
ALTER TABLE orders SET UNUSED COLUMN old_status;   -- COLUMN キーワードはオプション

-- 後で余裕があるときに実際に削除する
ALTER TABLE orders DROP UNUSED COLUMNS;   -- 未使用列をすべて削除する

-- DROP COLUMN: 即時削除（大きなテーブルでは時間がかかる）
ALTER TABLE orders DROP COLUMN old_status;

-- 複数列を一度に削除する
ALTER TABLE orders DROP (old_status, temp_column);

-- 未使用列の確認
SELECT table_name, column_name
FROM DBA_UNUSED_COL_TABS
ORDER BY table_name;

-- 列名を変更する（Oracle 9i 以降）
ALTER TABLE orders RENAME COLUMN status TO order_status;

-- テーブル名を変更する
ALTER TABLE orders RENAME TO sales_orders;
-- または（シノニム）
RENAME orders TO sales_orders;   -- 現在のスキーマ内でのリネーム

-- 注意: テーブル名を変更してもシノニム・外部キー・ビューは自動更新されない
-- → 依存オブジェクトを確認してから変更する
SELECT object_type, object_name
FROM USER_DEPENDENCIES
WHERE referenced_name = 'ORDERS'
  AND referenced_type = 'TABLE';

-- インデックス名を変更する（ALTER INDEX）
ALTER INDEX orders_pk RENAME TO sales_orders_pk;

-- 制約名を変更する（ALTER TABLE ... RENAME CONSTRAINT）
ALTER TABLE orders RENAME CONSTRAINT orders_pk TO sales_orders_pk;

-- PRIMARY KEY の追加
ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (order_id)
USING INDEX TABLESPACE app_index;   -- インデックスを別表領域に作成できる

-- UNIQUE 制約の追加
ALTER TABLE customers ADD CONSTRAINT customers_email_uk UNIQUE (email);

-- FOREIGN KEY の追加
ALTER TABLE order_items ADD CONSTRAINT oi_order_fk
FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE;

-- CHECK 制約の追加
ALTER TABLE orders ADD CONSTRAINT orders_status_chk
CHECK (status IN ('PENDING', 'PROCESSING', 'SHIPPED', 'CANCELLED'));

-- NOT NULL 制約に名前を付けて追加する
ALTER TABLE orders ADD CONSTRAINT orders_customer_nn
CHECK (customer_id IS NOT NULL);   -- NOT NULL はインラインで書くのが一般的

-- 制約を削除する
ALTER TABLE orders DROP CONSTRAINT orders_status_chk;
ALTER TABLE orders DROP PRIMARY KEY;         -- 主キー制約を削除
ALTER TABLE orders DROP PRIMARY KEY CASCADE; -- 参照している外部キーも一緒に削除

-- 制約を無効化する（バルクロード時や一時的なデータ修正時に使う）
ALTER TABLE order_items DISABLE CONSTRAINT oi_order_fk;
ALTER TABLE orders DISABLE PRIMARY KEY CASCADE;  -- 依存する外部キーも無効化

-- 制約を再有効化する
ALTER TABLE order_items ENABLE CONSTRAINT oi_order_fk;
-- ENABLE VALIDATE（デフォルト）: 既存データ全件チェックを行ってから有効化する
-- ENABLE NOVALIDATE: 既存データをチェックせずに有効化する（将来の行のみ検証）
ALTER TABLE orders ENABLE NOVALIDATE PRIMARY KEY;  -- 大量データで既存データが正しいと確認済みの場合

-- 制約の状態を確認する
SELECT constraint_name, constraint_type, status, validated
FROM USER_CONSTRAINTS
WHERE table_name = 'ORDERS'
ORDER BY constraint_type;
-- status: ENABLED / DISABLED
-- validated: VALIDATED（既存データ検証済み）/ NOT VALIDATED（未検証）

-- 前提: 再定義対象のテーブルに主キーが必要
-- 手順の概要:
-- ① 新しい定義を持つ中間テーブルを作成する
-- ② DBMS_REDEFINITION.START_REDEF_TABLE でコピーを開始する（ロックなし）
-- ③ 処理中の差分は自動的にキャッチアップされる
-- ④ DBMS_REDEFINITION.FINISH_REDEF_TABLE で最終同期して切り替える（一時的にロック）

-- 再定義できるかチェックする
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname       => 'HR',
    tname       => 'ORDERS',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
);

-- ① 中間テーブルを作成する（新しい定義でテーブルを CREATE する）
CREATE TABLE hr.orders_new (
    order_id    NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date  DATE DEFAULT SYSDATE,
    status      VARCHAR2(50) DEFAULT 'PENDING' NOT NULL,  -- VARCHAR2(20) → VARCHAR2(50) に変更
    created_at  DATE DEFAULT SYSDATE                       -- 新しい列を追加
);

-- ② オンライン再定義を開始する（バックグラウンドでコピーが始まる）
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
    uname       => 'HR',
    orig_table  => 'ORDERS',
    int_table   => 'ORDERS_NEW'
);

-- ③（省略可）途中経過の同期（大きなテーブルの場合に定期的に実行する）
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname       => 'HR',
    orig_table  => 'ORDERS',
    int_table   => 'ORDERS_NEW'
);

-- ④ 再定義を完了する（最終同期と名前の切り替えを行う）
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname       => 'HR',
    orig_table  => 'ORDERS',
    int_table   => 'ORDERS_NEW'
);
-- 切り替え完了後、ORDERS_NEW（旧の ORDERS）を削除する
DROP TABLE hr.orders_new PURGE;