Oracle ORA-02290 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02290: check constraint violated — Causes, Fixes & Prevention

ORA-02290 is thrown by Oracle Database when a INSERT or UPDATE statement attempts to store a value that violates a CHECK constraint defined on a table column. Oracle enforces these constraints to guarantee data integrity, and any DML that breaks the rule is automatically rolled back. Understanding which constraint was violated and why is the fastest path to resolution.

Top 3 Causes

1. Value Out of Allowed Range

The most common cause is inserting a numeric or date value that falls outside the range defined in the CHECK constraint.

-- Table definition with a CHECK constraint
CREATE TABLE employees (
    employee_id  NUMBER PRIMARY KEY,
    salary       NUMBER(10,2) CONSTRAINT chk_salary CHECK (salary > 0),
    age          NUMBER       CONSTRAINT chk_age    CHECK (age BETWEEN 18 AND 65)
);

-- This INSERT will raise ORA-02290 because salary = -500
INSERT INTO employees (employee_id, salary, age)
VALUES (101, -500, 30);

-- ORA-02290: check constraint (HR.CHK_SALARY) violated

-- Correct INSERT
INSERT INTO employees (employee_id, salary, age)
VALUES (101, 5000, 30);
COMMIT;

2. Disallowed String / Enumerated Value

CHECK constraints are frequently used to restrict a column to a fixed set of values (acting like an enum). Passing any value outside that set triggers ORA-02290.

-- CHECK constraint allowing only specific status values
ALTER TABLE orders
ADD CONSTRAINT chk_order_status
CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'CANCELLED'));

-- This UPDATE will fail — 'PROCESSING' is not in the allowed list
UPDATE orders
SET    status = 'PROCESSING'
WHERE  order_id = 42;

-- ORA-02290: check constraint (OE.CHK_ORDER_STATUS) violated

-- First, identify what values are valid
SELECT constraint_name, search_condition
FROM   user_constraints
WHERE  constraint_name = 'CHK_ORDER_STATUS';

-- Then use a valid value
UPDATE orders
SET    status = 'CONFIRMED'
WHERE  order_id = 42;
COMMIT;

3. Dirty Data During Batch / Migration Jobs

During ETL processes or data migrations, source data often contains values that do not meet the target table's CHECK constraints, causing the entire batch to fail.

-- Pre-validate staging data BEFORE loading into the target table
SELECT COUNT(*) AS violation_count
FROM   orders_staging
WHERE  status NOT IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'CANCELLED')
   OR  amount <= 0;

-- Cleanse data before inserting
INSERT INTO orders (order_id, status, amount)
SELECT order_id,
       CASE
           WHEN status IN ('PENDING','CONFIRMED','SHIPPED','CANCELLED')
               THEN status
           ELSE 'PENDING'   -- map unknown values to a safe default
       END,
       NULLIF(amount, 0)
FROM   orders_staging
WHERE  amount > 0;

COMMIT;

Quick Fix Solutions

Step 1 — Identify the violated constraint

-- Find all CHECK constraints on the target table
SELECT constraint_name,
       search_condition,
       status
FROM   user_constraints
WHERE  table_name      = 'EMPLOYEES'
  AND  constraint_type = 'C';

Step 2 — Modify the constraint if the business rule has changed

-- Drop the old constraint and recreate with updated rule
ALTER TABLE employees DROP CONSTRAINT chk_salary;

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0); -- now allows 0

-- Re-enable and validate
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary;

Step 3 — Temporarily disable during bulk load (use with caution)

-- Disable without validating existing rows
ALTER TABLE employees
DISABLE CONSTRAINT chk_salary NOVALIDATE;

-- Perform bulk insert ...

-- Re-enable and validate all rows
ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT chk_salary;

Prevention Tips

Always pre-validate data before bulk DML. Run a SELECT COUNT(*) query against your staging table using the same condition as the CHECK constraint before executing the actual load. This catches violations early and avoids rolling back large transactions.

-- Generic pre-check pattern
SELECT *
FROM   employees_staging
WHERE  NOT (salary > 0)          -- mirrors CHECK condition
    OR NOT (age BETWEEN 18 AND 65);

Document and share CHECK constraints with the application team. Use the data dictionary to export all active constraints and include them in your project's technical spec so developers can mirror the validation logic in the application layer.

-- Export all CHECK constraints for documentation
SELECT table_name,
       constraint_name,
       search_condition,
       status
FROM   user_constraints
WHERE  constraint_type = 'C'
ORDER BY table_name;

Related Oracle Errors

Error Code	Description
ORA-02291	Foreign key constraint violated — parent key not found
ORA-02292	Foreign key constraint violated — child records exist
ORA-02293	Cannot enable CHECK constraint — existing data violates it
ORA-01400	Cannot insert NULL into a NOT NULL column
ORA-00001	UNIQUE constraint violated — duplicate value

📖 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