Dbms_comparison Fails When Unique Constraint Is Defined On Column

(Doc ID 1101089.1)

Last updated on SEPTEMBER 07, 2015

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


When unique constraint is defined on column, DBMS_COMPARISON fails with ORA-23629 .

The index columns in a comparison must uniquely identify every row involved in a comparison. The following constraints satisfy this requirement:
* A primary key constraint
* A unique constraint on one or more non-NULL columns

In spite of having a unique constraint on "not null" column the ORA-23629 is seen.

create table test (col1 date);
alter table test modify (col1 not null);
alter table test add constraint test_unique unique (col1);
conn strmadmin/strmadmin
comparison_name => 'TEST_COMP',
schema_name => 'SCOTT',
object_name => 'TEST',
dblink_name => 'STRM11Q.US.ORACLE.COM',
index_schema_name => 'SCOTT',
index_name => 'TEST_UNIQUE');
ORA-23629: SCOTT.TEST_UNIQUE is not an eligible index on table SCOTT.TEST for comparison
ORA-06512: at "SYS.DBMS_COMPARISON", line 4304
ORA-06512: at "SYS.DBMS_COMPARISON", line 420
ORA-06512: at line 2


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms