DUP_VAL_ON_INDEX Exception in PL/SQL

DUP_VAL_ON_INDEX is a pre-defined exception in PL/SQL which is raised when a program attempts to store duplicate values in a database column that is constrained by a unique index. The name itself explains what this exception is all about.

Duplicate Value on Index column

The main reasons for the DUP_VAL_ON_INDEX exception are:

1. Unique Index Constraint: When a unique index constraint is defined on a column, the database ensures that all values in that column are unique. Attempting to insert a duplicate value in this column results in the exception.
2. Primary Key Constraint: A primary key constraint is a unique identifier for a table’s row. It requires all values in the primary key column to be unique and not null. Inserting a row with a duplicate primary key value raises the exception.

Let’s reproduce DUP_VAL_ON_INDEX

Create a table named students with columns id(primary key), and name.

CREATE TABLE students
(
   id     NUMBER PRIMARY KEY
 , name   VARCHAR(200)
);

Insert the below records in the table

INSERT INTO students(id, name) VALUES (1, 'John');
INSERT INTO students(id, name) VALUES (2, 'Emily');
INSERT INTO students(id, name) VALUES (3, 'Sarah');

Now, there are 3, let’s try to insert a duplicate in the table

SQL> BEGIN
  2     INSERT INTO students(id
  3                        , name)
  4          VALUES (1
  5                , 'Taylor');
  6  EXCEPTION
  7     WHEN OTHERS THEN
  8        dbms_output.put_line( 'Error Message ' || SQLERRM);
  9  END;    
 10  /

Error Message ORA-00001: unique constraint (XXC.SYS_C00249908) violated
PL/SQL procedure successfully completed.

Database refuses to insert a record in the table because you are trying to insert a duplicate value for the id field.

To resolve the DUP_VAL_ON_INDEX exception, follow these steps:

1. Identify the problematic index or primary key constraint: Check the error message or trace file for the name of the index or constraint that caused the exception.
2. Analyze the data: Investigate the data that caused the exception to identify the duplicate value. Use SQL queries to search for existing values in the indexed column.
3. Prevent duplicates: Modify the application logic or data input process to ensure that duplicate values are not inserted into the unique index or primary key column. This may involve validating data before insertion or implementing a sequence generator for primary key values.
4. Handle the exception: Incorporate exception handling in your PL/SQL code to catch the DUP_VAL_ON_INDEX exception and take appropriate action. This can include displaying a user-friendly error message, logging the error, or implementing a retry mechanism.