Database Error Messages

Release
toggle
  • 23ai
  • 21c
  • 19c
Updated
Jun 24, 2024

OCI-00001

unique constraint (constraint_schema.constraint_name) violated on table table_schema.table_name columns (column_names)
  • constraint_schema: The schema name where the constraint resides.
  • constraint_name: The name of the constraint.
  • table_schema: The schema name for the table affected by this constraint.
  • table_name: The name of the table affected by this constraint.
  • column_names: The column names affected by this constraint.

Cause

An UPDATE, INSERT or MERGE statement attempted to update or create a record that duplicated values limited by a unique constraint. A unique constraint can be implemented as an explicit unique constraint, a unique index, or a primary key.

Consider the case where a table has a unique constraint on columns FIRSTNAME and LASTNAME. Because of this constraint, it is not possible to insert a row containing values of FIRSTNAME and LASTNAME that are identical to the values of these columns in an existing table row.

Action

Determine what type of unique constraint was violated (explicit unique constraint, unique index, or primary key), and which table columns are affected by this unique constraint.

Then choose whether to change the constraint to allow duplicate values; modify the SQL statement to no longer create a duplicate value; or drop the constraint, unique index, or primary key.

Additional Information

Note: Further details about the violating column values are provided with the parameter ERROR_MESSAGE_DETAILS=ON. This parameter is ON by default. If it is currently OFF, you can turn this parameter ON using the ALTER SESSION SET ERROR_MESSAGE_DETAILS=ON statement.

The following examples describe how to identify the table name and table columns of the violated unique constraint.

This query returns CONSTRAINT or INDEX to identify whether an index or constraint generated the exception. For example:

SELECT 'CONSTRAINT' object_type
FROM   all_constraints
WHERE  owner = '<schema_name>'
AND    constraint_name = '<constraint_name>'
UNION
SELECT 'INDEX' object_type
FROM   all_indexes
WHERE  owner = '<schema_name>'
AND    index_name = '<constraint_name>';

If the constraint or index query returns CONSTRAINT, this query finds the table columns affected by the violated unique constraint. For example:

SELECT column_name, table_name
FROM   all_cons_columns
WHERE  owner = '<schema_name>'
AND    constraint_name = '<constraint_name>';

If the constraint or index query returns INDEX, this query finds the table columns affected by the violated unique index (or primary key). For example:

SELECT column_name, table_owner, table_name
FROM   all_ind_columns
WHERE  index_owner = '<schema_name>'
AND    index_name = '<constraint_name>';

For example:

ORA-00001: unique constraint (SCOTT.UNIQUE_VALUES) violated

The following query determines whether the violated constraint is an explicit constraint or index. For example:

SELECT 'CONSTRAINT' object_type
FROM   all_constraints
WHERE  owner = 'SCOTT'
AND    constraint_name = 'UNIQUE_VALUES'
UNION
SELECT 'INDEX' object_type
FROM   all_indexes
WHERE  owner = 'SCOTT'
AND    index_name = 'UNIQUE_VALUES';