【Oracle】制約（CONSTRAINT）完全ガイド｜PRIMARY KEY・UNIQUE・CHECK・FOREIGN KEY・ENABLE/DISABLE・DEFERRABLE の使い方

-- 列制約: 列の定義の直後に記述（単一列）
-- 表制約: 列定義の後に別途記述（複合列や名前付き制約）

CREATE TABLE orders (
    -- PRIMARY KEY: 1テーブルに1つだけ。自動的に NOT NULL + UNIQUE になる
    order_id     NUMBER          CONSTRAINT pk_orders PRIMARY KEY,

    -- NOT NULL: NULL 値の禁止（列制約として定義）
    customer_id  NUMBER          NOT NULL,

    -- UNIQUE: NULL は複数可（email が NULL の行は複数登録できる）
    email        VARCHAR2(200)   CONSTRAINT uq_orders_email UNIQUE,

    -- CHECK: 値の範囲チェック（status は指定した値のみ）
    status       VARCHAR2(20)    DEFAULT 'PENDING'
                                 CONSTRAINT chk_orders_status
                                     CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED')),

    order_date   DATE            DEFAULT SYSDATE NOT NULL,
    total_amount NUMBER(12, 2)   CONSTRAINT chk_amount_positive CHECK (total_amount >= 0),

    -- FOREIGN KEY: 参照先テーブルの列と一致する値のみ格納可
    -- ON DELETE CASCADE: 親レコード削除時に子レコードも連鎖削除
    -- ON DELETE SET NULL: 親レコード削除時に FK 列を NULL に設定
    --（デフォルト: 親レコードがある限り削除不可 ORA-02292）
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE SET NULL,

    -- 複合 PRIMARY KEY（複数列を組み合わせた PK）は表制約として定義
    -- CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_seq)
);

-- 複合 UNIQUE 制約の例
CREATE TABLE product_prices (
    product_id   NUMBER NOT NULL,
    currency     CHAR(3) NOT NULL,
    price        NUMBER(10, 2),
    CONSTRAINT uq_product_currency UNIQUE (product_id, currency)  -- 組み合わせで一意
);

-- 制約を後から追加
ALTER TABLE employees
    ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE;

-- NOT NULL は MODIFY を使って追加
ALTER TABLE employees
    MODIFY (department_id NUMBER NOT NULL);

-- 制約の削除
ALTER TABLE employees
    DROP CONSTRAINT chk_salary_positive;

-- PRIMARY KEY の削除（関連する UNIQUE インデックスも削除される）
-- CASCADE: この PK を参照している FOREIGN KEY も一緒に削除
ALTER TABLE orders DROP PRIMARY KEY CASCADE;

-- 制約の名前変更
ALTER TABLE orders
    RENAME CONSTRAINT uq_orders_email TO uq_email;

-- 制約を無効化（既存データは変更しない）
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;
-- → DISABLE 後は FOREIGN KEY のチェックが行われない
-- → 既存の違反データも残ったまま

-- 大量データを INSERT
INSERT INTO employees SELECT * FROM employees_staging;
COMMIT;

-- 制約を有効化（ENABLE）
-- VALIDATE: 既存データも含めて制約チェックを行う（デフォルト）
ALTER TABLE employees ENABLE CONSTRAINT fk_emp_dept;
-- → 既存データに違反がある場合は ORA-02298 エラーになる

-- NOVALIDATE: 新規データのみ制約チェックし、既存データはチェックしない
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT fk_emp_dept;
-- → 既存の不整合データを許容しつつ、新しい違反データの追加を防ぐ

-- 4つの組み合わせ
-- ENABLE VALIDATE（デフォルト）: 全データ対象・新規データも対象
-- ENABLE NOVALIDATE: 既存スキップ・新規データのみ対象
-- DISABLE VALIDATE: 既存違反不可・新規データは挿入後に制約チェックなし（稀）
-- DISABLE NOVALIDATE: 制約無効（最も緩い状態）

-- 制約の状態確認
SELECT constraint_name, status, validated
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

-- DEFERRABLE: 制約チェックを遅延可能にする
-- INITIALLY IMMEDIATE（デフォルト）: 各 DML 実行時にチェック（遅延なし）
-- INITIALLY DEFERRED: デフォルトで COMMIT 時まで遅延

-- 循環参照の例: employees の manager_id は employees を参照する
CREATE TABLE emp2 (
    emp_id     NUMBER PRIMARY KEY,
    name       VARCHAR2(100),
    manager_id NUMBER,
    CONSTRAINT fk_emp2_manager
        FOREIGN KEY (manager_id)
        REFERENCES emp2 (emp_id)
        DEFERRABLE INITIALLY DEFERRED  -- COMMIT 時まで遅延チェック
);

-- DEFERRABLE INITIALLY DEFERRED の場合:
-- 循環データの INSERT が可能（COMMIT 時に一括チェック）
INSERT INTO emp2 VALUES (1, 'CEO',   NULL);
INSERT INTO emp2 VALUES (2, 'VP',    1);
INSERT INTO emp2 VALUES (3, 'Mgr',   2);
COMMIT;  -- ここでまとめてチェック（すべて整合していれば成功）

-- SET CONSTRAINTS: セッション内で一時的に遅延モードを切り替える
SET CONSTRAINTS fk_emp2_manager DEFERRED;    -- 遅延モードに切り替え
SET CONSTRAINTS fk_emp2_manager IMMEDIATE;   -- 即時モードに戻す（即時チェックが走る）
SET CONSTRAINTS ALL DEFERRED;                -- すべての DEFERRABLE 制約を遅延に

-- デフォルト（ON DELETE 指定なし）:
-- → 子レコードが存在する場合に親を削除しようとすると ORA-02292 エラー

-- ON DELETE CASCADE: 親を削除すると子レコードも自動削除
CREATE TABLE order_items (
    order_id   NUMBER,
    item_seq   NUMBER,
    product_id NUMBER,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_seq),
    CONSTRAINT fk_oi_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE    -- orders を削除すると order_items も削除
);

-- ON DELETE SET NULL: 親を削除すると子の FK 列を NULL に設定
CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    department_id NUMBER,
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
        ON DELETE SET NULL   -- departments を削除すると employees.department_id が NULL になる
);

-- 確認: ON DELETE の設定は USER_CONSTRAINTS の DELETE_RULE で確認
SELECT constraint_name, delete_rule
FROM user_constraints
WHERE table_name = 'EMPLOYEES'
  AND constraint_type = 'R';
-- R = Referential（FOREIGN KEY）
-- DELETE_RULE: CASCADE / SET NULL / NO ACTION

-- 制約の一覧確認
SELECT
    constraint_name,
    constraint_type,   -- P=PK, U=UNIQUE, C=CHECK/NOT NULL, R=FK
    status,            -- ENABLED / DISABLED
    validated,         -- VALIDATED / NOT VALIDATED
    rely               -- RELY（NOVALIDATE でも最適化ヒントに使う）
FROM user_constraints
WHERE table_name = 'ORDERS'
ORDER BY constraint_type;

-- 制約を構成する列の確認
SELECT
    uc.constraint_name,
    uc.constraint_type,
    ucc.column_name,
    ucc.position
FROM user_constraints uc
JOIN user_cons_columns ucc
  ON uc.constraint_name = ucc.constraint_name
 AND uc.owner = ucc.owner
WHERE uc.table_name = 'ORDERS'
ORDER BY uc.constraint_type, ucc.position;

-- FOREIGN KEY の参照先確認
SELECT
    uc.constraint_name,
    uc.r_constraint_name,          -- 参照先制約名
    (SELECT table_name FROM user_constraints
     WHERE constraint_name = uc.r_constraint_name) AS ref_table,
    uc.delete_rule
FROM user_constraints uc
WHERE uc.table_name = 'ORDER_ITEMS'
  AND uc.constraint_type = 'R';