Oracle: ORA-02293 Error Message

ORA-02293 Error Message

Learn the cause and how to resolve the ORA-02293 error message in Oracle.

Description

When you encounter an ORA-02293 error, the following error message will appear:

• ORA-02293: cannot enable <constraint name> - check constraint violated

Cause

You tried to add or enable a check constraint to an existing table using a ALTER TABLE command, but the command failed because the existing data in the table did not comply with the check constraint.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite the SQL statement so that the check constraint is not violated, or remove the data from the table that does not comply with your check constraint.

For example, if you created the following table:

CREATE TABLE suppliers
( supplier_id numeric(4),
  supplier_name varchar2(50)
);

And then tried to execute the following INSERT statement:

INSERT INTO suppliers
 ( supplier_id, supplier_name )
 VALUES
 ( 1, 'Gateway' );

Then tried to add the following check constraint:

ALTER TABLE suppliers
ADD CONSTRAINT check_supplier_name
  CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

You would receive the following error message:

You could correct this error by either modifying the check constraint to allow the supplier_id column to contain other values (such as "Gateway"), or you could remove the values from the supplier table that do not comply with the check constraint.