Daily Archives: 12.03.2014

Today I discovered that primary key indexes do not need to be unique. This makes a mess for checking supplemental logging for GoldenGate.

Here is the example:

SQL> create table t(c1 number, c2 number);
Table created.


SQL> create index i on t(c1,c2);
Index created.


SQL> alter table t add primary key (c1,c2) using index i;
Table altered.


SQL> select uniqueness from dba_indexes where table_name=’T';
UNIQUENES
———
NONUNIQUE


SQL>  select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_name=’T';
CONSTRAINT_NAME                C
—————————— -
SYS_C001632047                 P


SQL> insert into t values (1,2);
1 row created.


SQL> commit;
Commit complete.


SQL> insert into t values (1,2);
insert into t values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001632047) violated


SQL> alter table t drop constraint SYS_C001632047;
Table altered.


SQL> insert into t values (1,2);
1 row created.


SQL> commit;
Commit complete.


SQL> alter table t add primary key (c1,c2) using index i;
alter table t add primary key (c1,c2) using index i
*
ERROR at line 1:
ORA-02437: cannot validate (SYS.SYS_C001632048) – primary key violated


SQL> alter table t add primary key (c1,c2) using index i novalidate;
Table altered.


SQL>  select uniqueness from dba_indexes where table_name=’T';
UNIQUENES
———
NONUNIQUE


SQL> select * from t;
        C1         C2
———- ———-
         1          2
         1          2


SQL> select VALIDATED from dba_constraints where table_name=’T';
VALIDATED
————-
NOT VALIDATED


SQL> insert into t values (1,2);
insert into t values (1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C001632049) violated


SQL> drop table t;
Table dropped.