snippetMinor

How to log the values that cause a unique constraint violation

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

uniquetheviolationlogconstraintthathowvaluescause

Problem

I have a lengthy INSERT statement running on Oracle that fails with:

ORA-00001: unique constraint (MYPROJECT.MYTABLE_PK) violated

The constraint is:

CONSTRAINT "MYTABLE_PK" PRIMARY KEY ("ENCOUNTER_NUM", "CONCEPT_CD", "PROVIDER_ID", "START_DATE", "MODIFIER_CD")

The problematic INSERT statement is logged, but not the values causing the constraint violation.

Is there a way to log or otherwise save and view the values that violated the constraint?

This is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Solution

You can use DML error logging

begin
  dbms_errlog.create_error_log( dml_table_name => 'MYTABLE' );
end;
/

insert into mytable
  >
  log errors into err$mytable
  reject limit unlimited;

This will log all the rows that violated a constraint to the err$mytable table.

Code Snippets

begin
  dbms_errlog.create_error_log( dml_table_name => 'MYTABLE' );
end;
/

insert into mytable
  <<your select statement>>
  log errors into err$mytable
  reject limit unlimited;

Context

StackExchange Database Administrators Q#120270, answer score: 5

Revisions (0)

No revisions yet.