SQL & PL/SQL

Hello people,

can somebody explain to me, what the hell am I missing?

What enforces the unique value when you create primary or a unique key? I thought it's a unique index, but that turned out to be wrong....

create table test1 (no number primary key);

select index_name,uniqueness
from user_indexes
where table_name ='TEST1';

INDEX_NAME UNIQUENESS
------------------------------ ----------
SYS_C0017897 UNIQUE
/

OK, this works. Exactly as one would expect. But, If I create a table without primary key, create a nonunique index on it and then add the primary key, the index doesn't change.

create table test2 (no number);

create index test2_idx on test2(no);

alter table test2
add constraint test2_pk primary key (no);

select index_name,uniqueness
from user_indexes
where table_name ='TEST2';

INDEX_NAME UNIQUENESS
------------------------------ ----------
TEST2_IDX NONUNIQUE
/


insert into test2 values(1); this goes ok
insert into test2 values(1); and here I get an error

So, it doesn't allow duplicate values...well, that's good

When I queried the index above it shows that it's nonunique.
So, I came to conclusion that I don't need it anymore and decided to drop it.

drop index TEST2_IDX ;

and I got

ORA-02429: cannot drop index used for enforcement of unique/primary key

So, I' have a nonunique index that is enforcing uniqueness on primary key. And I have a headache, because I don't understand what's going on.

Same thing would happen if I created a column, then created a nonunique index on it and finally added a nonunique constraint.

The index would still be NONUNIQUE.

I was coming to conclusion that it's not the index that is preforming the uniqueness check, so I tested the following - create a column with unique index without unique key.

create table test3(no number);
create unique index test3_idx on test3(no);
insert into test3 values(1);
insert into test3 values(1); - error

ORA-00001: unique constraint (TEST.TEST3_IDX) violated
00001. 00000 - "unique constraint (%s.%s) violated"

So index is enforcing it.


Can somebody please tell me, what am I missing? I came to conclusion that Oracle sometimes doesn't care if index is unique or not.