ORA-01400 Cannot Insert NULL into Column

ORA-01400: Cannot Insert NULL into Column

Error Overview

Error Text: ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE"."COLUMN")

The ORA-01400 error occurs when an INSERT or UPDATE statement attempts to place a NULL value into a column that has a NOT NULL constraint. The error message helpfully identifies the specific schema, table, and column involved.

Common Causes

1. Missing Required Values

• INSERT statement omitting required columns
• NULL passed from application code
• Empty strings converted to NULL

2. Data Loading Issues

• CSV/flat file with missing data
• ETL process not handling NULLs
• Source data quality problems

3. Trigger/Default Issues

• Trigger not setting required value
• Default value not defined
• Sequence not properly assigned

4. UPDATE to NULL

• Setting column to NULL inadvertently
• Subquery returning NULL
• Conditional logic error

Diagnostic Queries

Identify NOT NULL Columns

-- Find all NOT NULL columns for a table
SELECT
  column_name,
  data_type,
  nullable,
  data_default
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
  AND nullable = 'N'
ORDER BY column_id;

-- Check specific column
SELECT column_name, nullable, data_default
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
  AND column_name = UPPER('&column_name');

-- Find NOT NULL columns without defaults
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
  AND nullable = 'N'
  AND data_default IS NULL
ORDER BY column_id;

Check Constraint Details

-- Find NOT NULL constraints
SELECT
  constraint_name,
  search_condition
FROM user_constraints
WHERE table_name = UPPER('&table_name')
  AND constraint_type = 'C'  -- Check constraints
  AND search_condition LIKE '%NOT NULL%';

-- All constraints on column
SELECT constraint_name, constraint_type
FROM user_cons_columns cc
JOIN user_constraints c USING (constraint_name)
WHERE cc.table_name = UPPER('&table_name')
  AND cc.column_name = UPPER('&column_name');

Find NULL Source in Data

-- Check source data for NULLs
SELECT COUNT(*) as null_count
FROM source_table
WHERE target_column IS NULL;

-- Find rows with NULL that would fail
SELECT *
FROM source_table
WHERE target_column IS NULL;

-- Identify NULL patterns in staging
SELECT
  column_name,
  COUNT(CASE WHEN value IS NULL THEN 1 END) as null_count,
  COUNT(*) as total_rows
FROM staging_table
UNPIVOT (value FOR column_name IN (col1, col2, col3))
GROUP BY column_name;

Resolution Steps

1. Provide Required Values

-- Wrong: Missing required column
INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
-- Fails if employee_id is NOT NULL

-- Right: Include all required columns
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

2. Add Default Value to Column

-- Add default for existing column
ALTER TABLE employees
MODIFY email VARCHAR2(100) DEFAULT '[email protected]';

-- Add default with NOT NULL
ALTER TABLE employees
MODIFY status VARCHAR2(20) DEFAULT 'ACTIVE' NOT NULL;

-- Check the change
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
  AND column_name = 'EMAIL';

3. Use NVL/COALESCE for NULL Handling

-- In INSERT statement
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (
  emp_seq.NEXTVAL,
  'John',
  'Doe',
  NVL(v_email, '[email protected]')
);

-- In INSERT...SELECT
INSERT INTO employees (employee_id, first_name, last_name, email)
SELECT
  emp_seq.NEXTVAL,
  first_name,
  last_name,
  COALESCE(email, alt_email, '[email protected]')
FROM staging_employees;

-- With CASE expression
INSERT INTO employees (employee_id, first_name, last_name, status)
SELECT
  emp_seq.NEXTVAL,
  first_name,
  last_name,
  CASE
    WHEN status IS NULL THEN 'PENDING'
    ELSE status
  END
FROM staging_employees;

4. Create Trigger for Auto-Population

-- Trigger to set default values
CREATE OR REPLACE TRIGGER trg_emp_defaults
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  -- Auto-generate ID if null
  IF :NEW.employee_id IS NULL THEN
    :NEW.employee_id := emp_seq.NEXTVAL;
  END IF;

  -- Set default email if null
  IF :NEW.email IS NULL THEN
    :NEW.email := LOWER(:NEW.first_name || '.' || :NEW.last_name || '@company.com');
  END IF;

  -- Set creation timestamp
  IF :NEW.created_date IS NULL THEN
    :NEW.created_date := SYSDATE;
  END IF;
END;
/

5. Make Column Nullable (If Appropriate)

-- Only if business rules allow NULL
ALTER TABLE employees MODIFY middle_name NULL;

-- Verify change
SELECT column_name, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES'
  AND column_name = 'MIDDLE_NAME';

6. Fix UPDATE Statements

-- Wrong: Updating to NULL
UPDATE employees SET email = NULL WHERE employee_id = 100;

-- Right: Use NVL to prevent NULL
UPDATE employees
SET email = NVL(v_new_email, email)  -- Keep existing if new is NULL
WHERE employee_id = 100;

-- Right: Use NULLIF carefully
UPDATE employees
SET department_id = NULLIF(v_dept_id, 0)  -- Could return NULL!
WHERE employee_id = 100;
-- Better:
UPDATE employees
SET department_id = CASE WHEN v_dept_id = 0 THEN department_id ELSE v_dept_id END
WHERE employee_id = 100;

Prevention Strategies

1. Input Validation in Application

-- PL/SQL validation before insert
CREATE OR REPLACE PROCEDURE insert_employee(
  p_first_name VARCHAR2,
  p_last_name VARCHAR2,
  p_email VARCHAR2
) AS
BEGIN
  -- Validate required fields
  IF p_first_name IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'First name is required');
  END IF;

  IF p_last_name IS NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'Last name is required');
  END IF;

  INSERT INTO employees (employee_id, first_name, last_name, email)
  VALUES (emp_seq.NEXTVAL, p_first_name, p_last_name, NVL(p_email, '[email protected]'));

  COMMIT;
END;
/

2. Data Loading with NULL Handling

-- SQL*Loader control file with NULL handling
-- LOAD DATA
-- INFILE 'employees.csv'
-- INTO TABLE employees
-- FIELDS TERMINATED BY ','
-- (
--   employee_id SEQUENCE(MAX,1),
--   first_name,
--   last_name,
--   email "NVL(:email, '[email protected]')"
-- )

-- PL/SQL bulk load with NULL handling
DECLARE
  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
  v_employees t_emp_tab;
BEGIN
  -- Load from staging
  SELECT
    staging_seq.NEXTVAL,
    first_name,
    last_name,
    NVL(email, '[email protected]'),
    NVL(hire_date, SYSDATE)
  BULK COLLECT INTO v_employees
  FROM staging_employees;

  FORALL i IN 1..v_employees.COUNT
    INSERT INTO employees VALUES v_employees(i);

  COMMIT;
END;
/

3. Define Sensible Defaults at Table Creation

CREATE TABLE employees (
  employee_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100) DEFAULT '[email protected]' NOT NULL,
  status VARCHAR2(20) DEFAULT 'ACTIVE' NOT NULL,
  created_date DATE DEFAULT SYSDATE NOT NULL,
  created_by VARCHAR2(30) DEFAULT USER NOT NULL
);

4. Use Check Constraints for Additional Validation

-- Prevent empty strings that might be confused with NULL
ALTER TABLE employees ADD CONSTRAINT chk_fname_not_empty
CHECK (first_name IS NOT NULL AND LENGTH(TRIM(first_name)) > 0);

ALTER TABLE employees ADD CONSTRAINT chk_lname_not_empty
CHECK (last_name IS NOT NULL AND LENGTH(TRIM(last_name)) > 0);

Common Scenarios

Empty String vs NULL

-- In Oracle, empty string '' equals NULL!
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (100, 'John', '');  -- This becomes NULL and fails if last_name is NOT NULL

-- Solution: Check for empty strings
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (100, 'John', NVL(NULLIF(TRIM(v_last_name), ''), 'Unknown'));

Conditional Insert

-- Ensure value exists in all branches
INSERT INTO employees (employee_id, first_name, last_name, department_id)
VALUES (
  100,
  'John',
  'Doe',
  CASE
    WHEN v_is_manager THEN 10
    WHEN v_is_contractor THEN 20
    ELSE 30  -- Don't forget the ELSE!
  END
);

Related Errors

• ORA-01407 - Cannot update to NULL
• ORA-02290 - Check constraint violated
• ORA-00947 - Not enough values
• ORA-01722 - Invalid number

Emergency Response

Quick Identification

-- Parse error message for column info
-- Error: ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")
-- Schema: HR, Table: EMPLOYEES, Column: EMAIL

-- Check column requirements
SELECT nullable, data_default
FROM dba_tab_columns
WHERE owner = 'HR'
  AND table_name = 'EMPLOYEES'
  AND column_name = 'EMAIL';

Temporary Fix

-- Add temporary default (fix properly later)
ALTER TABLE employees MODIFY email DEFAULT '[email protected]';

-- Or create trigger for complex defaults
CREATE OR REPLACE TRIGGER trg_emp_email
BEFORE INSERT ON employees
FOR EACH ROW
WHEN (NEW.email IS NULL)
BEGIN
  :NEW.email := 'auto_' || :NEW.employee_id || '@company.com';
END;
/