【Oracle】ALTER TABLE完全ガイド｜列追加・変更・削除・制約・MOVE・SET UNUSED・本番での注意点まで解説

ALTER TABLE emp
    ADD phone VARCHAR2(20);

ALTER TABLE emp
    ADD (
        phone      VARCHAR2(20),
        address    VARCHAR2(200),
        updated_at DATE DEFAULT SYSDATE NOT NULL
    );

-- VARCHAR2(50) → VARCHAR2(200) に拡張
ALTER TABLE emp
    MODIFY emp_name VARCHAR2(200);

ALTER TABLE emp
    MODIFY salary NUMBER(12, 2);

ALTER TABLE emp
    MODIFY status DEFAULT 'INACTIVE';

-- NOT NULL を付ける（既存行に NULL がないことが前提）
ALTER TABLE emp
    MODIFY phone NOT NULL;

-- NOT NULL を外す（NULL 許容に戻す）
ALTER TABLE emp
    MODIFY phone NULL;

ALTER TABLE emp
    MODIFY (
        emp_name VARCHAR2(200) NOT NULL,
        salary   NUMBER(12, 2)
    );

ALTER TABLE emp
    RENAME COLUMN old_column TO new_column;

ALTER TABLE emp
    RENAME COLUMN salary TO monthly_salary;

ALTER TABLE emp
    DROP COLUMN phone;

ALTER TABLE emp
    DROP (phone, address, memo);

-- 【ステップ1】業務時間中：列を即座に論理削除（参照不可にする）
ALTER TABLE emp SET UNUSED COLUMN phone;

-- 複数列を一括で SET UNUSED にする
ALTER TABLE emp SET UNUSED (phone, address);

-- 【ステップ2】メンテナンス時間帯：実際に削除（この操作は時間がかかる）
ALTER TABLE emp DROP UNUSED COLUMNS;

SELECT table_name, count(*) AS unused_col_count
FROM USER_UNUSED_COL_TABS
ORDER BY table_name;

-- 列を非表示に設定
ALTER TABLE emp MODIFY internal_code INVISIBLE;

-- 非表示を解除する
ALTER TABLE emp MODIFY internal_code VISIBLE;

SELECT column_name, hidden_column
FROM USER_TAB_COLS
WHERE table_name = 'EMP'
ORDER BY internal_column_id;

ALTER TABLE emp
    ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);

ALTER TABLE emp
    ADD CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id) REFERENCES dept (dept_id);

ALTER TABLE emp
    ADD CONSTRAINT uq_emp_email UNIQUE (email);

ALTER TABLE emp
    ADD CONSTRAINT ck_emp_salary CHECK (salary >= 0);

-- NOT NULL は ADD CONSTRAINT ではなく MODIFY で追加する
ALTER TABLE emp
    MODIFY emp_name NOT NULL;

ALTER TABLE emp
    DROP CONSTRAINT fk_emp_dept;

-- PRIMARY KEY を削除（依存する FK ごと削除）
ALTER TABLE dept
    DROP CONSTRAINT pk_dept CASCADE;

-- データ修正や一括 INSERT 前に無効化
ALTER TABLE emp DISABLE CONSTRAINT fk_emp_dept;

-- 作業後に有効化
ALTER TABLE emp ENABLE CONSTRAINT fk_emp_dept;

-- 既存データを検証せず有効化（新規 DML のみ制約が効く）
ALTER TABLE emp ENABLE NOVALIDATE CONSTRAINT fk_emp_dept;

SELECT constraint_name, constraint_type, status, validated
FROM USER_CONSTRAINTS
WHERE table_name = 'EMP'
ORDER BY constraint_type, constraint_name;

-- 方法1：ALTER TABLE … RENAME TO
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 方法2：RENAME 文（Oracle 独自の短縮形）
RENAME old_table_name TO new_table_name;

ALTER TABLE emp
    RENAME COLUMN salary TO monthly_salary;

-- テーブルを data2 表領域に移動
ALTER TABLE emp MOVE TABLESPACE data2;

-- UNUSABLE 状態のインデックスを確認
SELECT index_name, status
FROM USER_INDEXES
WHERE table_name = 'EMP'
AND status = 'UNUSABLE';

-- インデックスを再構築
ALTER INDEX pk_emp REBUILD;
ALTER INDEX idx_emp_dept REBUILD;

SELECT privilege FROM SESSION_PRIVS WHERE PRIVILEGE LIKE '%TABLE%';

ALTER TABLE emp
    ADD (
        created_by  VARCHAR2(30)  DEFAULT USER      NOT NULL,
        created_at  DATE          DEFAULT SYSDATE   NOT NULL,
        updated_at  DATE          DEFAULT SYSDATE   NOT NULL
    );

-- 移行データに不整合があっても新規DMLには制約を効かせたい場合
ALTER TABLE emp
    ADD CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
        ENABLE NOVALIDATE;

-- NUMBER → VARCHAR2 のように互換性のない型変更は直接 MODIFY できない
-- 作業列追加 → データコピー → 元列を論理削除 → 列名変更 の手順で対応する

-- [手順1] 新しい型で作業列を追加
ALTER TABLE emp ADD emp_code_new VARCHAR2(20);

-- [手順2] 既存データを新列にコピー
UPDATE emp SET emp_code_new = TO_CHAR(emp_code);
COMMIT;

-- [手順3] 元の列を論理削除（SET UNUSED：即時完了）
ALTER TABLE emp SET UNUSED COLUMN emp_code;

-- [手順4] 新列を元の名前に変更
ALTER TABLE emp RENAME COLUMN emp_code_new TO emp_code;

-- [手順5] NOT NULL が必要なら付ける
ALTER TABLE emp MODIFY emp_code NOT NULL;

-- [手順6] メンテ時間帯にまとめて実際の削除を行う
ALTER TABLE emp DROP UNUSED COLUMNS;