📋

Description

The ORA-02290 error in Oracle Database indicates a check constraint violation. This means the data being inserted or updated does not meet the criteria defined in the database's check constraint, preventing the operation from completing.

💬

Error Message

ORA-02290: check constraint ( string . string ) violated

🔍

Known Causes

3 known causes

⚠️

Invalid Data Input

The entered data violates a specified range or pattern defined by the check constraint. ⚠

⚠️

Incorrect Data Type

The data type of the input does not match the data type required by the constraint. ⚠

⚠️

Logic Error in Application

The application code is generating data that does not comply with the database constraints. 💻

🛠️

Solutions

3 solutions available

1. Temporarily Disable and Re-enable Constraint easy

Quickly bypass the error for data loading or specific operations by disabling and then re-enabling the constraint.

1

Identify the constraint name from the ORA-02290 error message. It will be in the format `SCHEMA_NAME.CONSTRAINT_NAME`.

2

Disable the check constraint using the `ALTER TABLE ... DISABLE CONSTRAINT` command.

ALTER TABLE schema_name.table_name DISABLE CONSTRAINT constraint_name;

3

Perform the data operation that was failing.

4

Re-enable the check constraint. Oracle will automatically validate existing data. If existing data violates the constraint, this step will fail, indicating a data integrity issue.

ALTER TABLE schema_name.table_name ENABLE CONSTRAINT constraint_name;

2. Correct Data Violating the Constraint medium

Address the root cause by modifying the data that violates the check constraint.

1

Identify the constraint name and the table involved from the ORA-02290 error message.

2

Query the table to find rows that violate the constraint. You'll need to infer the specific condition from the constraint definition.

SELECT * FROM schema_name.table_name WHERE NOT (your_constraint_condition);

3

Analyze the data identified in the previous step. Understand why it violates the constraint.

4

Update the violating rows to conform to the constraint's rules.

UPDATE schema_name.table_name SET column_name = new_value WHERE row_id_or_condition_for_violation;

5

Attempt the original operation again. If the data is now corrected, the operation should succeed.

3. Modify or Drop the Constraint medium

Adjust the constraint's logic or remove it if it's no longer required or is incorrect.

1

Identify the constraint name and the table involved from the ORA-02290 error message.

2

Review the current definition of the check constraint. You can do this by querying the data dictionary views `ALL_CONSTRAINTS` and `ALL_CONS_COLUMNS`.

SELECT constraint_name, search_condition FROM all_constraints WHERE owner = 'SCHEMA_NAME' AND constraint_name = 'CONSTRAINT_NAME';

3

Based on your analysis, decide whether to modify the constraint's `search_condition` or drop it entirely. If modifying, ensure the new condition correctly reflects the business rules.

4

To modify the constraint, first drop it and then recreate it with the new condition.

ALTER TABLE schema_name.table_name DROP CONSTRAINT constraint_name;
ALTER TABLE schema_name.table_name ADD CONSTRAINT constraint_name CHECK (new_constraint_condition);

5

Alternatively, to drop the constraint permanently:

ALTER TABLE schema_name.table_name DROP CONSTRAINT constraint_name;

6

After modifying or dropping, attempt the original operation again.

Oracle ORA-02290: Constraint Violation