Oracle ORA-02293 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02293: Cannot Validate – Check Constraint Violated

ORA-02293 occurs when you attempt to add a new CHECK constraint to a table or re-enable an existing one using ENABLE VALIDATE, but Oracle finds existing rows in the table that violate the constraint condition. Oracle validates all existing data at the moment the constraint is activated, and a single offending row is enough to abort the entire operation. This error is especially common after data migrations or when applying new business rules to mature production tables.

Top 3 Causes & SQL Examples

Cause 1: Existing Data Violates the New CHECK Constraint

When you try to add a new constraint to a populated table, any row that doesn't satisfy the condition will trigger ORA-02293.

-- This fails if any employee has salary <= 0
ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);
-- ORA-02293: cannot validate (HR.CHK_SALARY_POSITIVE) - check constraint violated

-- Find the offending rows first
SELECT employee_id, salary
FROM   employees
WHERE  salary <= 0 OR salary IS NULL;

-- Fix the data, then retry
UPDATE employees SET salary = 1 WHERE salary <= 0;
COMMIT;

ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

Cause 2: Re-enabling a Disabled Constraint After Bad Data Was Inserted

When a constraint is in DISABLE state, Oracle does not validate incoming data. Re-enabling it with the default VALIDATE option forces a full table scan.

-- Constraint was disabled, bad data slipped in
ALTER TABLE employees DISABLE CONSTRAINT chk_salary_positive;
INSERT INTO employees (employee_id, salary) VALUES (9999, -500);
COMMIT;

-- Trying to re-enable now fails
ALTER TABLE employees ENABLE CONSTRAINT chk_salary_positive;
-- ORA-02293 fires here

-- Option A: Fix bad data, then enable
UPDATE employees SET salary = 1 WHERE salary <= 0;
COMMIT;
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary_positive;

-- Option B: Enable without validating historical data (new rows still enforced)
ALTER TABLE employees ENABLE NOVALIDATE CONSTRAINT chk_salary_positive;

Cause 3: Post-Migration Constraint Activation

After bulk data loads or migrations, constraints are often re-enabled without checking data quality first. Use the EXCEPTIONS table to identify all violating rows at once.

-- Create the EXCEPTIONS table (run once per database)
@$ORACLE_HOME/rdbms/admin/utlexcpt.sql

-- Attempt to enable and capture all violating rows
ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT chk_salary_positive
EXCEPTIONS INTO exceptions;

-- Review every offending row
SELECT e.row_id,
       emp.employee_id,
       emp.salary
FROM   exceptions e
JOIN   employees emp ON emp.rowid = e.row_id
WHERE  e.table_name = 'EMPLOYEES';

-- Bulk-fix and retry
UPDATE employees
SET    salary = 1
WHERE  rowid IN (SELECT row_id FROM exceptions
                 WHERE  table_name = 'EMPLOYEES');
COMMIT;

ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT chk_salary_positive;

Quick Fix Summary

Situation	Recommended Action
Small number of bad rows	Fix data → re-run DDL
Large dataset, can't fix all rows immediately	Use `ENABLE NOVALIDATE`
Need to find all violations at once	Use `EXCEPTIONS INTO` clause
Migration scenario	Validate data quality before enabling

Prevention Tips

1. Always pre-check before applying constraints.
Make it a mandatory step in your deployment checklist to run a validation query — using the inverse of the constraint condition — before executing the ALTER TABLE statement in production.

-- Must return 0 rows before applying the constraint
SELECT COUNT(*) AS violations
FROM   employees
WHERE  NOT (salary > 0) OR salary IS NULL;

2. Use a three-phase migration strategy.
During any data migration, follow this disciplined sequence: DISABLE NOVALIDATE → load data → run data quality scripts → fix violations → ENABLE VALIDATE. Never skip the quality check phase; ORA-02293 at the final activation step is one of the most avoidable migration failures in Oracle environments.

Related Oracle Errors

ORA-02290 – CHECK constraint violated at DML time (runtime equivalent of ORA-02293)
ORA-02291 – Integrity constraint violated: parent key not found (FOREIGN KEY)
ORA-02292 – Integrity constraint violated: child record found
ORA-02296 – Cannot enable NOT NULL constraint; null values found

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

DEV Community