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

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
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Could you please explicitly mention which row you are referring to, which according to you should not have been accepted.
schwertnerCommented:
Possibly you have run the script above twice and run into duplicating of rows.

SELECT * FROM  dummy_objects ;

will help to check the table.
johnsoneSenior Oracle DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.