【Oracle】ORA-01400 完全ガイド｜cannot insert NULL の原因・NOT NULL 制約・デフォルト値・INVISIBLE 列への INSERT まで解説

-- パターン①: NOT NULL 列を INSERT で省略している
CREATE TABLE orders (
    order_id    NUMBER    PRIMARY KEY,       -- NOT NULL（主キー）
    customer_id NUMBER    NOT NULL,          -- NOT NULL 制約
    order_date  DATE      DEFAULT SYSDATE,   -- DEFAULT 値あり
    status      VARCHAR2(20) NOT NULL
);

-- NG: customer_id が省略されている → 暗黙的に NULL が挿入される → ORA-01400
INSERT INTO orders (order_id, order_date, status) VALUES (1, SYSDATE, 'PENDING');
-- ORA-01400: ("MYSCHEMA"."ORDERS"."CUSTOMER_ID") に NULL は挿入できません

-- OK: すべての NOT NULL 列に値を指定する
INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (1, 100, SYSDATE, 'PENDING');

-- パターン②: 明示的に NULL を挿入しようとしている
INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (2, NULL, SYSDATE, 'PENDING');   -- customer_id に NULL を明示 → ORA-01400

-- パターン③: SELECT INSERT で SELECT 結果が NULL になる
INSERT INTO orders (order_id, customer_id, order_date, status)
SELECT o.order_id, c.customer_id, SYSDATE, 'COPY'
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;  -- 対応するcustomerがなければNULL

-- パターン④: UPDATE で NOT NULL 列を NULL に更新しようとする
UPDATE orders SET customer_id = NULL WHERE order_id = 1;
-- ORA-01407: ("MYSCHEMA"."ORDERS"."CUSTOMER_ID") を NULL に更新できません
-- ※ UPDATE の場合は ORA-01400 ではなく ORA-01407 になる（混同に注意）

-- 通常の DEFAULT: 列を省略した場合にのみ適用される
CREATE TABLE logs (
    log_id     NUMBER PRIMARY KEY,
    message    VARCHAR2(500),
    log_time   DATE DEFAULT SYSDATE   -- 列省略時のみ SYSDATE
);

INSERT INTO logs (log_id, message) VALUES (1, 'test');         -- OK: log_time = SYSDATE
INSERT INTO logs (log_id, message, log_time) VALUES (2, 'x', NULL); -- ORA-01400（もし NOT NULL 制約あれば）

-- DEFAULT ON NULL（Oracle 12c 以降）: NULL を明示しても DEFAULT 値を使う
CREATE TABLE orders_v2 (
    order_id    NUMBER    PRIMARY KEY,
    status      VARCHAR2(20) DEFAULT ON NULL 'PENDING' NOT NULL
    -- NULL を明示しても 'PENDING' が使われる → NOT NULL 制約と組み合わせやすい
);

INSERT INTO orders_v2 (order_id) VALUES (1);              -- status = 'PENDING'（省略）
INSERT INTO orders_v2 (order_id, status) VALUES (2, NULL); -- status = 'PENDING'（NULL → DEFAULT 適用）
INSERT INTO orders_v2 (order_id, status) VALUES (3, 'SHIPPED'); -- status = 'SHIPPED'

-- NOT NULL 制約の確認: USER_TAB_COLUMNS で確認する
SELECT column_name, nullable, data_default
FROM USER_TAB_COLUMNS
WHERE table_name = 'ORDERS_V2'
ORDER BY column_id;
-- nullable = N: NOT NULL 制約あり
-- data_default: デフォルト値（DEFAULT ON NULL は DATA_DEFAULT_ON_NULL=YES に追加列で確認）

-- 既存テーブル（一部の行に NULL がある可能性がある）
ALTER TABLE employees ADD salary_grade NUMBER;   -- NULL 許可で追加

-- NG: NULL 値がある状態で NOT NULL を追加しようとすると ORA-02296
ALTER TABLE employees MODIFY salary_grade NOT NULL;
-- ORA-02296: (HR.SYS_C00xxxx) を使用可能にできません - NULL 値があります

-- 手順①: まず全行に DEFAULT 値を設定する
UPDATE employees SET salary_grade = 0 WHERE salary_grade IS NULL;
COMMIT;

-- 手順②: NOT NULL 制約を追加する
ALTER TABLE employees MODIFY salary_grade NOT NULL;

-- または DEFAULT ON NULL を使って1ステップで追加する（12c 以降）
ALTER TABLE employees ADD monthly_bonus NUMBER DEFAULT ON NULL 0 NOT NULL;
-- 既存行には DEFAULT 値 0 が設定される → NULL 行が発生しないため制約追加が成功する

-- NULL 値の件数を確認してから作業する
SELECT COUNT(*) FROM employees WHERE salary_grade IS NULL;

-- NULL 値がある列を特定する
SELECT column_name, COUNT(*) AS null_count
FROM (
    SELECT column_name, table_name
    FROM USER_TAB_COLUMNS
    WHERE table_name = 'EMPLOYEES' AND nullable = 'Y'
) cols
CROSS APPLY (
    SELECT COUNT(*) AS null_rows
    FROM employees
    WHERE CASE columns.column_name WHEN 'SALARY_GRADE' THEN salary_grade END IS NULL
)
GROUP BY column_name
HAVING null_count > 0;
-- ※ 上記は例示。実際はテーブルの列を確認してから個別に COUNT する