DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01400 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01400: Cannot Insert NULL into Column

ORA-01400 is one of the most common Oracle errors encountered by developers and DBAs alike. It occurs when you attempt to insert a NULL value into a column defined with a NOT NULL constraint. Oracle enforces this to protect data integrity, and the fix is usually straightforward once you identify the offending column.

Top 3 Causes

1. Missing Column Value in INSERT Statement

When a column list is omitted or a required column is simply left out of the INSERT statement, Oracle raises ORA-01400.

-- This will fail if DEPARTMENT_ID is NOT NULL
INSERT INTO employees (employee_id, last_name, hire_date, job_id)
VALUES (301, 'Smith', SYSDATE, 'IT_PROG');
-- ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."DEPARTMENT_ID")

-- Correct: include all NOT NULL columns
INSERT INTO employees (employee_id, last_name, hire_date, job_id, department_id)
VALUES (301, 'Smith', SYSDATE, 'IT_PROG', 60);
Enter fullscreen mode Exit fullscreen mode

2. Application Passing NULL via Bind Variables

Applications written in Java, Python, or .NET often bind NULL values when user input is empty or validation is skipped. The database sees a NULL and rejects it immediately.

-- Protect against NULL at the SQL level using NVL
INSERT INTO orders (order_id, customer_id, status, order_date)
VALUES (
    seq_order_id.NEXTVAL,
    :p_customer_id,
    NVL(:p_status, 'PENDING'),
    NVL(:p_order_date, SYSDATE)
);
Enter fullscreen mode Exit fullscreen mode

3. Missing DEFAULT Value or Disabled Trigger

If a PRIMARY KEY column relies on a sequence-based trigger that is disabled, no value is generated, resulting in ORA-01400.

-- Check trigger status
SELECT trigger_name, status
FROM   user_triggers
WHERE  table_name = 'EMPLOYEES';

-- Re-enable a disabled trigger
ALTER TRIGGER trg_emp_pk ENABLE;

-- Better: Use IDENTITY column (Oracle 12c+)
CREATE TABLE customers (
    customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    created_at    DATE DEFAULT SYSDATE NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Identify NOT NULL columns on the table:

SELECT column_name, nullable, data_default
FROM   user_tab_columns
WHERE  table_name = 'YOUR_TABLE'
ORDER BY column_id;
Enter fullscreen mode Exit fullscreen mode

Step 2 — Add a DEFAULT value to avoid future issues:

-- Add DEFAULT to an existing NOT NULL column
ALTER TABLE employees
MODIFY (status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL);
Enter fullscreen mode Exit fullscreen mode

Step 3 — Use COALESCE for multi-fallback NULL handling:

INSERT INTO products (product_id, product_name, description)
VALUES (
    seq_product_id.NEXTVAL,
    :p_name,
    COALESCE(:p_desc, :p_alt_desc, 'N/A')
);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always define DEFAULT values alongside NOT NULL constraints at design time, especially for audit columns like CREATED_AT, STATUS, and CREATED_BY. This ensures INSERT statements that omit the column still succeed.
  • Run regression tests after every schema change. When adding a new NOT NULL column to a live table, always include a DEFAULT clause so existing INSERT statements are not broken. Incorporate automated constraint-violation checks into your CI/CD pipeline to catch issues before they reach production.

Related Errors

Error Code Description
ORA-01407 Cannot UPDATE a NOT NULL column to NULL
ORA-00001 Unique / Primary Key constraint violated
ORA-02290 CHECK constraint violated
ORA-02291 Foreign Key (referential integrity) violation

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)