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!
As a proof of this we can create a deferrable primary key and take a look at the index that Oracle creates
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
alter table mytab drop primary key; alter table mytab add primary key (id) deferrable; 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_C0033766 NORMAL MYTAB NONUNIQUE 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_C0033767 P ENABLED DEFERRABLE SYS_C0033766 |
As we can notice, the index is not unique and this makes perfect sense otherwise we would not be able to temporarily insert duplicate values into this table (don’t forget that the constraint is deferrable).
We can also create our own index on the same PK column before creating the constraint. If the constraint is not deferrable the index can be either unique or non-unique otherwise it has to be non-unique. Oracle will use the existing index if those requirements are met
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
alter table mytab drop primary key; create unique index mytab_idx on mytab(id); 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 ------------------------------ --------------------------- ------------------------------ ---------- MYTAB_IDX 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_C0033772 P ENABLED NOT DEFERRABLE MYTAB_IDX |
As shown in the INDEX_NAME of the USER_CONSTRAINTS view, Oracle took our index to speed up the constraint enforcement.
So what about using a custom unique index that is defined on a set of columns that is bigger than the one on which the PK is defined? Let’s try that and see what happens
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
drop table mytab; create table mytab(id number(10), run_date date, run_region number(2)); create unique 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_C0033773 NORMAL MYTAB UNIQUE MYTAB_IDX 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_C0033773 P ENABLED NOT DEFERRABLE SYS_C0033773 |
In this case Oracle refused to use our index and created its own SYS_C0033773 for supporting the constraint. Also forcing our index does not work
|
1 2 3 4 5 6 |
SQL> alter table mytab drop primary key; Table altered SQL> alter table mytab add primary key (id) using index mytab_idx; alter table mytab add primary key (id) using index mytab_idx ORA-14196: Specified index cannot be used to enforce the constraint. |
We can still do something else: let’s try to use a non-unique index this time
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> drop index mytab_idx; Index dropped SQL> create index mytab_idx on mytab(id, run_date); Index created SQL> alter table mytab add primary key (id); Table altered 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 ------------------------------ --------------------------- ------------------------------ ---------- MYTAB_IDX NORMAL MYTAB NONUNIQUE 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_C0033777 P ENABLED NOT DEFERRABLE MYTAB_IDX SQL> select index_name, column_name 2 from user_ind_columns 3 where table_name = 'MYTAB' 4 order by column_position; INDEX_NAME COLUMN_NAME ------------------------------ -------------------------------------------------------------------------------- MYTAB_IDX ID MYTAB_IDX RUN_DATE |
Now it works and we don’t even need to specify the index name, Oracle picks our MYTAB_IDX because it satisfies the requirements! What about an index that is contains the same columns of our PK but not in the leading position? Let’s have a try
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> alter table mytab drop primary key; Table altered SQL> drop index mytab_idx; Index dropped SQL> create index mytab_idx on mytab(run_date, id); Index created SQL> alter table mytab add primary key (id); Table altered 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_C0033778 NORMAL MYTAB UNIQUE MYTAB_IDX NORMAL MYTAB NONUNIQUE 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_C0033778 P ENABLED NOT DEFERRABLE SYS_C0033778 |
Nope, it doesn’t work (but I was kind of expecting that!) and neither forcing it works.
I want to show one last interesting thing. When we create a PK constraint Oracle automatically creates an index. If we drop that constraint the index is dropped as well. This does not happen when we create the index ourselves. So, what’s the difference between those indexes? There is the column GENERATED in the USER_INDEXES view that is set to Y for all indexes that have been created by Oracle to support a constraint and to N for all other user-created indexes. This also makes perfect sense because the index we create might be used to optimize our queries and we don’t want it to be dropped with the constraint!
I’ll end this post with the answer to one question that might be bouncing in your head: why should we ever use custom indexes for PK constraints if Oracle already takes care of them?
The answer is that you might have a very big table and creating such index could be a real problem. In that case you can create the index separately using parallel and nologging options in order to dramatically speed up the process.
Summing up:
- custom UNIQUE indexes can be used only if columns match the constraint ones and this is not DEFERRABLE;
- custom NON-UNIQUE indexes can be always used to enforce primary keys if the constraint columns are the leading ones of the index;
- DEFERRABLE primary key constraints always need a NON-UNIQUE index (rule of leading columns applies here too).
