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