My Oracle Support Banner

Dbms_comparison Fails When Unique Constraint Is Defined On Column (Doc ID 1101089.1)

Last updated on FEBRUARY 20, 2019

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A 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




To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.