How to create Primary key index and how it works

Suppose you want to enforce that the primary key columns are unique within a table. Furthermore many of the columns in the primary key are frequently used within the WHERE clause of several queries. Also, you want to ensure that indexes are created on primary key columns.

When you define a primary key constraint for a table, Oracle will automatically create an associated index for you. There are several methods available for creating a primary key constraint. Preferred approach is to use the ALTER TABLE...ADD CONSTRAINT statement. This will create the index and the constraint at the same time. This example creates a primary key constraint named CUST_PK and also instructs Oracle to create the corresponding index (also named CUST_PK) in the USERS tablespace:

alter table cust add constraint cust_pk primary key (cust_id)
using index tablespace users;

The following queries and output provide details about the constraint and index that Oracle created. The first query displays the constraint information:

select
constraint_name
,constraint_type
from user_constraints
where table_name = 'CUST';

How It Works

We prefer to create primary key constraints and the corresponding index. In most situations, this approach is acceptable. However, you should be aware that there are several other methods for creating the primary key constraint and index. These methods are listed here:

•  Create an index first, and then use ALTER TABLE...ADD CONSTRAINT.

•  Specify the constraint inline (with the column) in the CREATE TABLE statement.

•  Specify the constraint out of line (from the column) within the CREATE TABLE statement.

Create Index and Constraint Separately

You have the option of first creating an index and then altering the table to apply the primary key constraint. Here’s an example:

SQL> create index cust_pk on cust(cust_id);

SQL> 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. If you work with large data volumes, you may require this sort of flexibility. This approach allows you to disable/re-enable a constraint without having to later rebuild the index.

Create Constraint Inline

You can directly create an index inline (with the column) in the CREATE TABLE statement. This approach is simple but doesn’t allow for multiple column primary keys and doesn’t name the constraint:

SQL> create table cust(cust_id number primary key);

If you don’t explicitly name the constraint (as in the prior statement), Oracle automatically
generates a name like SYS_C123456. If you want to explicitly provide a name, you can do so as follows:

create table cust(cust_id number constraint cust_pk primary key
using index tablespace users);

The advantage of this approach is that it’s very simple. If you’re experimenting in a development or test environment, this approach is quick and effective.