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

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
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.

Solution,

This is not error as such. You can handle it depending on you business logic. Simply print the message and ignore it or instead of inserting record update it using student_id as driving column like this,