DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01407 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01407: Cannot Update to NULL – Causes, Fixes & Prevention

ORA-01407 is thrown by Oracle Database when you attempt to UPDATE a column that has a NOT NULL constraint with a NULL value. This error enforces data integrity at the database level and will immediately roll back the offending statement. It is one of the most common constraint-related errors seen in production environments, often caused by application logic changes or data migration issues.

Top 3 Causes

1. Explicitly Setting a NOT NULL Column to NULL

The most straightforward cause: a developer writes an UPDATE that directly assigns NULL to a constrained column, often without checking the table schema first.

-- This will raise ORA-01407
UPDATE employees
SET salary = NULL
WHERE employee_id = 101;

-- Check column constraints before updating
SELECT column_name, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';
Enter fullscreen mode Exit fullscreen mode

2. Subquery in SET Clause Returns No Rows (NULL)

When a subquery is used in the SET clause and finds no matching row, Oracle returns NULL for that expression. If the target column is NOT NULL, ORA-01407 is raised.

-- Dangerous: subquery may return NULL if no match found
UPDATE employees e
SET e.dept_name = (
    SELECT d.department_name
    FROM departments d
    WHERE d.department_id = e.department_id
);

-- Safe fix: wrap with NVL to handle NULL results
UPDATE employees e
SET e.dept_name = NVL(
    (SELECT d.department_name
     FROM departments d
     WHERE d.department_id = e.department_id),
    'Unassigned'
);

-- Alternative: filter rows where subquery has no match
UPDATE employees e
SET e.dept_name = (
    SELECT d.department_name
    FROM departments d
    WHERE d.department_id = e.department_id
)
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.department_id = e.department_id
);
Enter fullscreen mode Exit fullscreen mode

3. Batch or Migration Data Contains NULL Values

During bulk UPDATE or ETL operations, source data may contain NULL values, or a transformation step may produce NULLs unexpectedly. This can cause the entire batch to fail and roll back.

-- Pre-check: find NULL values in source before running batch
SELECT COUNT(*) AS problematic_rows
FROM source_data
WHERE salary IS NULL OR department_id IS NULL;

-- Safe batch update using MERGE with NULL handling
MERGE INTO employees t
USING (
    SELECT employee_id,
           NVL(salary, 0)         AS salary,
           NVL(department_id, 99) AS department_id
    FROM source_data
) s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
    UPDATE SET
        t.salary = s.salary,
        t.department_id = s.department_id;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Use NVL() or COALESCE() to replace NULL with a default value in any SET clause.
  • Add a WHERE filter using EXISTS or IS NOT NULL to skip rows where the subquery would return NULL.
  • Alter the column to allow NULLs if the business rule has changed (use with caution in production). 
-- Allow NULL if business logic requires it
ALTER TABLE employees MODIFY (salary NUMBER(10,2) NULL);

-- Or set a column-level default
ALTER TABLE employees MODIFY (salary NUMBER(10,2) DEFAULT 0 NOT NULL);
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Validate before updating. Always run a pre-check query to confirm no NULL values exist in the data you are about to write. Make this a mandatory step in any ETL or migration runbook.

-- Always run this before bulk updates
SELECT column_name, COUNT(*) AS null_count
FROM (
    SELECT NVL(TO_CHAR(salary), 'NULL_FLAG')       AS column_name FROM source_data WHERE salary IS NULL
    UNION ALL
    SELECT NVL(TO_CHAR(department_id), 'NULL_FLAG') FROM source_data WHERE department_id IS NULL
) GROUP BY column_name;
Enter fullscreen mode Exit fullscreen mode

Enforce constraints at the application layer too. Do not rely solely on the database to catch NULLs. Add validation in your application code (e.g., @NotNull annotations in JPA entities) so that NULL values are rejected long before the SQL statement is executed. This two-layer defense reduces both errors and unnecessary database round-trips.

Related Oracle Errors

Error Code Description
ORA-01400 Cannot insert NULL — same constraint, triggered on INSERT
ORA-02290 CHECK constraint violated — broader data integrity category
ORA-01722 Invalid number — wrong data type reaching a numeric column

📖 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)