【Oracle】ORA-00001: 一意制約に反しています の原因と解決方法完全ガイド｜重複データの特定・MERGE・UPSERT・LOG ERRORSまで解説

CREATE TABLE employees (
    employee_id  NUMBER PRIMARY KEY,
    email        VARCHAR2(100) UNIQUE,
    name         VARCHAR2(100)
);

INSERT INTO employees VALUES (1, 'sato@example.com', '佐藤太郎');
-- → 成功

INSERT INTO employees VALUES (2, 'sato@example.com', '佐藤次郎');
-- → ORA-00001: unique constraint (HR.SYS_C007856) violated
-- → email 列の UNIQUE 制約に違反（同じメールアドレスが既に存在する）

INSERT INTO employees VALUES (1, 'suzuki@example.com', '鈴木花子');
-- → ORA-00001: unique constraint (HR.SYS_C007855) violated
-- → employee_id 列の PRIMARY KEY 制約に違反（ID=1 が既に存在する）

-- 制約名で検索してテーブル名と列名を確認する
SELECT
    c.constraint_name,
    c.table_name,
    c.constraint_type,   -- P=PRIMARY KEY, U=UNIQUE
    cc.column_name,
    cc.position
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE c.constraint_name = 'SYS_C007856'
ORDER BY cc.position;

-- 対象列（email）で重複を検出
SELECT email, COUNT(*) AS cnt
FROM employees
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

-- INSERT しようとしているデータと既存データの重複を確認する
SELECT s.employee_id, s.email, s.name
FROM staging_employees s
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.email = s.email
);

MERGE INTO employees e
USING (
    SELECT 1 AS employee_id, 'sato_new@example.com' AS email, '佐藤太郎' AS name
    FROM dual
) src ON (e.employee_id = src.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.email = src.email, e.name = src.name
WHEN NOT MATCHED THEN
    INSERT (employee_id, email, name)
    VALUES (src.employee_id, src.email, src.name);

-- ステージングテーブル staging_employees から本テーブルへ一括反映
MERGE INTO employees e
USING staging_employees s ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.email = s.email, e.name = s.name
WHEN NOT MATCHED THEN
    INSERT (employee_id, email, name)
    VALUES (s.employee_id, s.email, s.name);

COMMIT;

-- PRIMARY KEY（employee_id）が重複する行はスキップされる
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(employees, pk_employees) */
INTO employees (employee_id, email, name)
SELECT employee_id, email, name
FROM staging_employees;

-- インデックス名ではなく列名で指定する方法
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(employees(employee_id)) */
INTO employees (employee_id, email, name)
VALUES (1, 'test@example.com', 'テスト');
-- employee_id=1 が既存なら行がスキップされ、エラーにならない

-- ステップ 1: エラーログテーブルを作成する
BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG(
        dml_table_name  => 'EMPLOYEES',
        err_log_table_name => 'ERR_EMPLOYEES'  -- 任意のログテーブル名
    );
END;
/

-- ステップ 2: LOG ERRORS INTO 付きで INSERT を実行
INSERT INTO employees (employee_id, email, name)
SELECT employee_id, email, name
FROM staging_employees
LOG ERRORS INTO err_employees ('BATCH_20260331')
REJECT LIMIT UNLIMITED;
-- ORA-00001 が発生する行は err_employees に退避され、残りの行はINSERTされる

-- ステップ 3: エラー行を確認する
SELECT ora_err_mesg$, ora_err_tag$, employee_id, email, name
FROM err_employees
WHERE ora_err_tag$ = 'BATCH_20260331';

BEGIN
    INSERT INTO employees (employee_id, email, name)
    VALUES (1, 'sato@example.com', '佐藤太郎');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        -- 一意制約違反の場合は UPDATE にフォールバック
        UPDATE employees
        SET email = 'sato@example.com', name = '佐藤太郎'
        WHERE employee_id = 1;
END;
/

-- シーケンスの現在値を確認
SELECT last_number FROM user_sequences WHERE sequence_name = 'EMP_SEQ';

-- テーブルの最大IDを確認
SELECT MAX(employee_id) FROM employees;

-- シーケンスがテーブルの最大IDより小さい場合、衝突が発生する

-- テーブルの最大IDより大きい値にシーケンスを進める
DECLARE
    v_max_id NUMBER;
    v_seq_val NUMBER;
BEGIN
    SELECT MAX(employee_id) INTO v_max_id FROM employees;
    LOOP
        SELECT emp_seq.NEXTVAL INTO v_seq_val FROM dual;
        EXIT WHEN v_seq_val > v_max_id;
    END LOOP;
END;
/
-- Oracle 12c以降なら ALTER SEQUENCE emp_seq RESTART START WITH 値; も使える