ORA-01400 Cannot Insert NULL — Default or Column Fix

ORA-01400: cannot insert NULL into ("SHOP"."ORDERS"."STATUS"). It lands right after a schema change, an ETL tweak, or a framework upgrade — inserts that ran for years suddenly rejected, with the message naming the exact column. The application didn't change; the contract did. Somebody added a NOT NULL column without a default, disabled a populating trigger, or deployed code whose INSERT list predates the new column.

Oracle enforces NOT NULL at write time with zero forgiveness: no implicit defaults, no silent coercion. That strictness is a feature — it keeps junk out — but it turns every uncoordinated schema change into a write outage for exactly the paths nobody updated.

This guide follows the message inward: reading the three-part name, auditing the column's nullability and default, finding the stale INSERT, checking sequences and triggers, defaulting with NVL/COALESCE, and adding future NOT NULL columns without breaking live writes. The same discipline covers every database with mandatory-column errors.

Read the Three-Part Name in the Message

cannot insert NULL into ("SHOP"."ORDERS"."STATUS") hands you owner, table, and column in one breath — no log archaeology needed. Go straight to all_tab_columns for that coordinate and read two fields: NULLABLE (Y/N) and DATA_DEFAULT. N with no default means omission is fatal and explicit NULL is fatal; the write must supply the value or the schema must change. N with a default means someone passed explicit NULL — the default only fills omitted columns, never overrides.

While there, read the neighbors: how many other NOT NULL-without-default columns share the table, and which of them your INSERT lists cover. A table that grew three mandatory columns across three migrations usually breaks three different write paths — fixing only the named one buys days, not safety. The dictionary view of the whole table turns a single-column incident into a complete audit in one query.

Record the coordinate plus the NULLABLE/DEFAULT pair in the ticket. 'SHOP.ORDERS.STATUS is N with no default' tells the next engineer everything; 'insert failing' tells them nothing. Precision at this step compresses every later one. Paste the coordinate into the ticket title so the next engineer starts at the dictionary, not at zero.

-- The named column: nullability + default in one row
SELECT nullable, data_default, data_type
FROM all_tab_columns
WHERE owner = 'SHOP' AND table_name = 'ORDERS' AND column_name = 'STATUS';

-- Whole-table audit: every mandatory column without a default
SELECT column_name, data_type
FROM all_tab_columns
WHERE owner = 'SHOP' AND table_name = 'ORDERS'
  AND nullable = 'N' AND data_default IS NULL;

The Missing Column in the INSERT List

INSERT INTO orders VALUES (...) without a column list binds by position and breaks on every schema change; INSERT with a list breaks only when a new mandatory column goes unlisted. Both fail identically with 01400, but the list form tells you exactly what's missing by diffing. Always write lists — positional inserts are a standing invitation to the next outage.

The subtle variant is the explicitly-NULL bind: INSERT INTO orders (..., status) VALUES (..., NULL) fails even when status has a DEFAULT, because explicit NULL overrides defaults. ORMs love this shape — they bind every mapped attribute, NULLs included. The fix is omitting null-valued columns from the statement (dynamic SQL), wrapping with NVL, or declaring DEFAULT ON NULL so the column treats explicit NULLs as omitted.

Diff mechanically, not by eye: extract the statement's column list and compare against the mandatory-no-default set from the dictionary. Eyes skip the 14th column in a 20-column list; a set difference doesn't. Attach that diff to the deploy checklist for every migration touching the table. Better yet, generate the diff in CI from the migration files themselves — the checklist then verifies what the pipeline already proved.

-- Positional (breaks on every schema change): avoid
-- INSERT INTO orders VALUES (1, 881, 5000);

-- Explicit list naming the new column (survives schema growth)
INSERT INTO orders (order_id, customer_id, total, status)
VALUES (1, 881, 5000, 'new');

-- Omit-when-null pattern: DEFAULT fills what you skip
INSERT INTO orders (order_id, customer_id, total)
VALUES (2, 882, 7500);
-- (works only if status has a DEFAULT)

Sequences, Identity, and Trigger Defaults

Primary keys and audit columns usually arrive via autopopulation — and every mechanism has an off switch that produces 01400. Legacy BEFORE INSERT triggers calling seq.NEXTVAL stop firing when disabled, dropped, or invalidated by a dependency change; direct-path loads and some bulk tools bypass row triggers entirely, exposing the raw NULL beneath. Sequence-fed inserts fail differently: forgetting NEXTVAL in the VALUES clause inserts NULL into the PK with the same code.

Diagnose the chain in order: all_triggers for STATUS (DISABLED is the smoking gun), then the trigger body for the assignment (SELECT trigger_body FROM all_triggers), then whether the failing path fires triggers at all. For identity columns (GENERATED ALWAYS AS IDENTITY), check for explicit NULL/0 overrides — ALWAYS rejects caller-supplied values, while BY DEFAULT ON NULL accepts NULLs gracefully.

Repair the mechanism, not the rows. Re-enable and recompile the trigger (ALTER TRIGGER ... ENABLE, check USER_ERRORS for invalidation causes), restore NEXTVAL to the insert, or migrate legacy trigger-population to identity columns that can't be disabled. Hand-filling one incident's NULLs without fixing the populator guarantees the sequel.

-- Trigger chain: present? enabled? valid?
SELECT trigger_name, status FROM all_triggers
WHERE table_owner = 'SHOP' AND table_name = 'ORDERS';
SELECT trigger_body FROM all_triggers
WHERE trigger_name = 'ORDERS_BI_TRG';

-- Repair: re-enable (then check USER_ERRORS if invalid)
ALTER TRIGGER shop.orders_bi_trg ENABLE;

-- Sequence-fed PK done right
INSERT INTO orders (order_id, customer_id, total, status)
VALUES (shop.order_seq.NEXTVAL, 881, 5000, 'new');

NVL/COALESCE: Defaulting at Write Time

When inputs are legitimately absent — an optional status from an older client, a nullable API field — default at write time instead of failing. NVL(:status, 'new') substitutes the fallback per row; COALESCE takes the first non-NULL across several candidates (request value, customer default, global default). Both keep the INSERT list stable while inputs vary, which is exactly the shape that survives schema growth.

Prefer schema DEFAULTs for universal fallbacks and NVL/COALESCE for contextual ones. A column default ('new' for every omitted status) belongs in DDL where every writer inherits it; a contextual pick (VIP customers default to 'priority') belongs in the statement where the logic stays visible to reviewers. Mixing them up — contextual logic in DDL via triggers, universal defaults repeated in every INSERT — scatters the contract across layers.

On 12c+, DEFAULT ON NULL closes the explicit-NULL hole declaratively: omitted and explicit-NULL both resolve to the default, so ORM-bound NULLs stop throwing for good. Adopt it for every defaulted column that ORMs touch in any write path — it's the one-line end to an entire class of binding bugs that otherwise recur quarterly. Document which columns use it beside the schema contract so every writer inherits the behavior.

-- Contextual fallback at write time
INSERT INTO orders (order_id, customer_id, total, status)
VALUES (3, 883, 9000, NVL(:status, 'new'));

-- First non-NULL across candidates
INSERT INTO orders (order_id, customer_id, total, status)
VALUES (4, 884, 9100, COALESCE(:status, :cust_default, 'new'));

-- Declarative end to explicit-NULL bindings (12c+)
ALTER TABLE shop.orders MODIFY status DEFAULT ON NULL 'new';

Backfill Then Constrain: Adding NOT NULL Safely

Adding NOT NULL to a populated table is a three-step dance, and skipping a step is how outages happen. First UPDATE the existing NULLs to the intended value (or a marked sentinel you reconcile later) — on big tables, batch the update to avoid a megatransaction that stalls replication and holds TM locks. Then declare the DEFAULT so future omissions land safely. Only then ADD the constraint — with VALIDATE on small tables, and ENABLE NOVALIDATE plus a later VALIDATE on huge ones to avoid a full-table lock during peak.

The migration that caused the incident did step three first (well, simultaneously): NOT NULL with no default and no service-list check. The safe template inverts the risk — backfill, default, constrain — with each step independently deployable and rollback-safe. Defaults are the shock absorbers: they let old write paths keep working while new code adopts the column deliberately.

Verify after constraining: re-run the mandatory-no-default audit (the new column should now show a default or full list coverage), insert one row omitting the column to prove the default fires, and watch the alert log for 01400 in the hour after deploy. The migration isn't done when it runs clean — it's done when the first real writes prove the contract.

-- 1) Backfill existing NULLs (batch on big tables)
UPDATE shop.orders SET status = 'new'
WHERE status IS NULL AND ROWNUM <= 100000;
-- repeat until 0 rows updated

-- 2) Declare the default (absorbs future omissions)
ALTER TABLE shop.orders MODIFY status DEFAULT 'new';

-- 3) Constrain last (NOVALIDATE + later VALIDATE on huge tables)
ALTER TABLE shop.orders MODIFY status NOT NULL;

-- Prove the contract: omitted column must default, not fail
INSERT INTO shop.orders (order_id, customer_id, total)
VALUES (5, 885, 9200);

Prevention: Schema Contracts in CI

Make uncoordinated columns structurally impossible. The migration rule is one sentence: every new NOT NULL column declares a DEFAULT unless the owning service's updated INSERT list is attached to the same pull request. Reviewers enforce the sentence; the pipeline enforces the proof — a job that diffs each service's INSERT lists against the mandatory-no-default set and fails the build on any gap.

Monitor the error as a deploy signal, not just an incident. Alert on the first ORA-01400 in the alert log after any DDL deploy: one occurrence pages, because the first is never the last when a write path is broken. Trend 01400 counts per table across releases — a step-change names the migration that introduced the mismatch even when nobody connects the two.

Version the write contract alongside the schema itself. The service's INSERT lists are as much an interface as its REST endpoints; changing mandatory columns without updating consumers breaks the interface. Treat DDL review with the same consumer-impact rigor as API review, and the 'surprise mandatory column' stops shipping. Publish the contract (mandatory columns per table) where service owners can read it without asking the DBA team.

#!/bin/bash
# Deploy gate: no 01400 may follow a DDL deploy; mandatory columns covered
set -euo pipefail
CONNECT="svc/pass@//db-prod:1521/shop"
# 1) Any 01400 since the deploy marker?
CNT=$(sqlplus -S "$CONNECT" << 'EOF'
SET HEADING OFF FEEDBACK OFF
SELECT COUNT(*) FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01400%' AND originating_timestamp > SYSTIMESTAMP - INTERVAL '1' HOUR;
EOF
)
echo "ORA-01400 in last hour: $CNT" | tr -d ' '
# 2) Mandatory-no-default columns must all have service coverage (reviewed list)
sqlplus -S "$CONNECT" << 'EOF'
SELECT column_name FROM all_tab_columns
WHERE owner='SHOP' AND table_name='ORDERS'
  AND nullable='N' AND data_default IS NULL;
EOF
echo GATE-CHECK-DONE