How oracle allowing duplicate values for unique indexes which already defined for a table object ?

MIHIR KAR
MIHIR KAR used Ask the Experts™
on
I have tables with unique indexes(composite of  columns), though it is unique indexes attached to the table object, still
 having duplicate values for the same unquie index key values.

SQL> create table dummy_objects (object_id  number, object_owner varchar2(30), object_name varchar2(30));

Table created.

SQL> insert into dummy_objects values (0,'MIHIR','TABLE_A');

1 row created.

SQL> insert into dummy_objects values (1,'HAPPY','TABLE_B');

1 row created.

SQL> insert into dummy_objects values (NULL,'BMK','NULL_1');

1 row created.

SQL> insert into dummy_objects values (NULL,'JOHN','NULL_2');

1 row created.

SQL> create unique index dummy_objects_u1 on dummy_objects(object_id);

Index created.

SQL> drop index dummy_objects_u1;

Index dropped.

SQL> create unique index dummy_objects_u2 on dummy_objects(object_id, object_owner);
create unique index dummy_objects_u2 on dummy_objects(object_id, object_owner)
                                        *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Nitin SontakkeDeveloper
Commented:
Could you please explicitly mention which row you are referring to, which according to you should not have been accepted.
schwertner
Top Expert 2008
Commented:
Possibly you have run the script above twice and run into duplicating of rows.

SELECT * FROM  dummy_objects ;

will help to check the table.
Senior Oracle DBA
Commented:
Using your sample data does not reproduce the issue.  It worked just fine for me.

One way to find issues like this is to use an exceptions table.  You can create one by running this in SQL*Plus

@?/rdbms/admin/utlexcpt.sql

Then create the index with this clause:

create index dummy_objects_i1 on dummy_objects(object_id, object_owner);
alter table dummy_objects add constraint dummy_objects_u2 unique (object_id, object_owner) exceptions into exceptions;

Once the command completes and you get an error, you should run this:

select object_id, object_owner from dummy_objects
where rowid in (select row_id from exceptions)
order by object_id, object_owner;

That should show you the rows that failed the duplicate check.

FYI - Oracle has recommended for a while that you don't create unique indexes.  You should create a non-unique index and then a unique constraint.  Easier for maintenance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial