umzzil nng

Posted on Aug 15 • Originally published at oraerror.com

Oracle ORA-02262 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02262: ORA-type Error in Type-Checking of Check Constraint

ORA-02262 is raised by Oracle when it encounters a data type mismatch or an invalid expression while parsing a CHECK constraint during a CREATE TABLE or ALTER TABLE statement. This error almost always appears alongside a secondary ORA error (such as ORA-00932 or ORA-01722) that reveals the true root cause. Understanding both errors together is essential for a fast resolution.

Top 3 Causes and Fixes

1. Data Type Mismatch in CHECK Constraint Expression

Comparing a column to a literal of the wrong type is the most common trigger. Oracle's type checker strictly validates expressions at the DDL level and does not silently coerce incompatible types.

Broken example:

-- salary is NUMBER, but compared to a string literal
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 'ZERO');
-- ORA-02262 / ORA-00932: inconsistent datatypes

Fixed example:

-- Use a numeric literal for a NUMBER column
ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Use DATE literals for DATE columns
ALTER TABLE contracts
ADD CONSTRAINT chk_start_date CHECK (start_date >= DATE '2000-01-01');

2. Using Non-Deterministic or Disallowed Functions

Oracle prohibits non-deterministic functions like SYSDATE, SYSTIMESTAMP, USER, sequence .NEXTVAL, and subqueries inside CHECK constraints. Using them causes ORA-02262 during the type-checking phase.

Broken example:

-- SYSDATE is not allowed in a CHECK constraint
ALTER TABLE orders
ADD CONSTRAINT chk_order_date CHECK (order_date <= SYSDATE);
-- ORA-02262 raised

Workaround — use a BEFORE trigger instead:

CREATE OR REPLACE TRIGGER trg_order_date_check
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  IF :NEW.order_date > SYSDATE THEN
    RAISE_APPLICATION_ERROR(
      -20001,
      'order_date cannot be in the future.'
    );
  END IF;
END;
/

Deterministic functions that ARE allowed:

-- LENGTH, TRIM, UPPER, REGEXP_LIKE, TO_NUMBER, etc.
ALTER TABLE employees
ADD CONSTRAINT chk_emp_name CHECK (LENGTH(TRIM(emp_name)) > 0);

ALTER TABLE products
ADD CONSTRAINT chk_code_format
  CHECK (REGEXP_LIKE(product_code, '^[A-Z]{3}[0-9]{4}$'));

3. Existing CHECK Constraint Conflicts After Column Type Change

When you modify a column's data type with ALTER TABLE ... MODIFY, existing CHECK constraints tied to that column are re-validated. If the constraint expression is no longer compatible with the new type, ORA-02262 is thrown.

-- Step 1: Identify existing CHECK constraints on the column
SELECT constraint_name, search_condition
FROM   user_constraints
WHERE  table_name      = 'EMPLOYEES'
  AND  constraint_type = 'C';

-- Step 2: Drop the conflicting constraint
ALTER TABLE employees
DROP CONSTRAINT chk_old_salary;

-- Step 3: Change the column type
ALTER TABLE employees
MODIFY salary NUMBER(12, 2);

-- Step 4: Re-add a compatible CHECK constraint
ALTER TABLE employees
ADD CONSTRAINT chk_salary
  CHECK (salary >= 0 AND salary <= 99999999.99);

Quick Diagnosis Tips

Always read the full error stack — the secondary error beneath ORA-02262 points directly to the problem:

-- In SQL*Plus, display the full error stack
SHOW ERRORS;

-- Test your CHECK expression as a WHERE clause first
-- If this SELECT returns no rows, the constraint is safe to add
SELECT COUNT(*)
FROM employees
WHERE NOT (salary >= 0 AND salary <= 99999999.99);

Prevention Tips

1. Pre-validate expressions with a SELECT before adding the constraint.
Run the CHECK expression as a WHERE clause against your table. If it executes without error and returns the expected rows, it is safe to use in a constraint.

2. Audit constraints before any column type change.
Always query USER_CONSTRAINTS and USER_CONS_COLUMNS before issuing an ALTER TABLE ... MODIFY. Drop incompatible constraints first, perform the type change, then recreate them with corrected expressions. This eliminates surprise ORA-02262 errors in production deployments.

Related Errors

Error Code	Description
ORA-00932	Inconsistent datatypes — the most common companion error
ORA-01722	Invalid number — string-to-number conversion failure
ORA-02251	Subquery not allowed in CHECK constraint
ORA-02290	CHECK constraint violated during DML
ORA-00904	Invalid identifier inside constraint expression

📖 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