Databases Reference
In-Depth Information
Create a B-tree Index and Primary Key Constraint Separately
You have the option of first creating an index and then altering the table to apply the primary key
constraint. For completeness of this example, the
CREATE TABLE
statement is shown.
create table cust(
cust_id number
,first_name varchar2(30)
,last_name varchar2(30));
create unique index cust_pk
on cust(cust_id);
alter table cust
add constraint cust_pk
primary key (cust_id);
The advantage to this approach is that you can drop or disable the primary key constraint
independently of the index. Sometimes in large database environments, you may want to drop or disable
constraints while loading data for performance reasons. You may need the flexibility of being able to
drop the constraint but not the index. In large database environments, recreating an index can take a
long time and consume considerable system resources.
Another slight twist to this scenario is that it is possible to create an index with columns defined
differently than the primary key constraint. For example,
create index cust_pk
on cust(cust_id, first_name, last_name);
alter table cust
add constraint cust_pk
primary key (cust_id);
We're not recommending that you create primary key indexes with different columns than the
constraint; rather we're pointing out that it's possible. You should be aware of these scenarios so that
you're not confused when troubleshooting issues.
Viewing Primary Key Constraint and Index Details
You can confirm the details of the index as follows:
select index_name, index_type, uniqueness
from user_indexes
where table_name = 'CUST';
