Link to home
Start Free TrialLog in
Avatar of MIHIR KAR
MIHIR KARFlag for India

asked on

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
SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial