I admit it, I had a completely wrong belief when thinking about primary key constraints and their related index. When creating a PK on a table, Oracle automatically associates a unique index to it
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
create table mytab(id number(10), run_date date, run_region number(2)); create index mytab_idx on mytab(id, run_date); alter table mytab add primary key (id); SQL> select index_name, index_type, table_name, uniqueness 2 from user_indexes 3 where table_name = 'MYTAB'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS ------------------------------ --------------------------- ------------------------------ ---------- SYS_C0033765 NORMAL MYTAB UNIQUE SQL> select constraint_name, constraint_type, status, deferrable, index_name 2 from user_constraints 3 where table_name = 'MYTAB'; CONSTRAINT_NAME CONSTRAINT_TYPE STATUS DEFERRABLE INDEX_NAME ------------------------------ --------------- -------- -------------- ------------------------------ SYS_C0033765 P ENABLED NOT DEFERRABLE SYS_C0033765 |
The index name is internally generated and associated to the constraint. Fact is that, as I discovered some time ago, it’s not the index that enforces the uniqueness but the constraint itself. The constraint uses the underlying index to go faster but the index itself does not enforce anything. But you can’t really blame me for thinking differently in the beginning, not after seeing that Oracle creates a unique index for the pk!