How to resolve ORA-02293 Error Message in Oracle database

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 – check constraint violated

Cause

You tried to add or enable a test constraint to an present table using a ALTER TABLE command, however the command failed due to the fact the present facts in the desk did not comply with the take a look at constraint.

Resolution

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

Option #1

Rewrite the SQL assertion so that the take a look at constraint is not violated, or remove the information from the table that does now not comply with your test 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 ought to right this error via either modifying the check constraint to allow the supplier_id column to comprise different values (such as “Gateway”), or you should do away with the values from the dealer table that do no longer comply with the take a look at constraint.