Primary Key and Index

The default expectation is that defining a Primary Key constraint creates a Unique Index on the exactly the same columns (i.e. in the same order as well) as specified in the constraint.

However, this is not necessarily so.
1. You can have a Primary Key constraint with a non-Unique Index.
2. The index can be created ahead of the constraint. (Thus, the constraint "uses" the index as it finds the index already present)
3. The columns in the index need not be in the same order as the PK definition.

Here are two example tables showing :
1. Non-Unique Index being used
2. The Index Columns not in the same order as the Primary Key constraint definition.

SQL> -- table_a example with a non-unique index
SQL> create table table_a
  2  as select object_id, owner, object_name, object_type, created
  3  from dba_objects
  4  where object_id is not null;

Table created.

SQL> create index table_a_ndx on table_a(owner,object_id);

Index created.

SQL> alter table table_a add constraint table_a_pk
  2  primary key (owner,object_id);

Table altered.

SQL> 
SQL> -- table_b example with the key columns differently ordered
SQL> create table table_b
  2  as select object_id, owner, object_name, object_type, created
  3  from dba_objects
  4  where object_id is not null;

Table created.

SQL> create unique index table_b_unq_ndx on table_b(object_id,owner);

Index created.

SQL> alter table table_b add constraint table_b_pk
  2  primary key (owner,object_id);

Table altered.

SQL> 
SQL> select table_name, constraint_name, constraint_type, index_name
  2  from user_constraints
  3  where table_name in ('TABLE_A','TABLE_B')
  4  and constraint_type = 'P'
  5  order by table_name
  6  /

TABLE_NAME           CONSTRAINT_NAME      C INDEX_NAME
-------------------- -------------------- - --------------------
TABLE_A              TABLE_A_PK           P TABLE_A_NDX

TABLE_B              TABLE_B_PK           P TABLE_B_UNQ_NDX


SQL> select index_name, column_name, column_position
  2  from user_ind_columns
  3  where index_name in ('TABLE_A_NDX','TABLE_B_UNQ_NDX')
  4  order by index_name, column_position
  5  /

INDEX_NAME           COLUMN_NAME          COLUMN_POSITION
-------------------- -------------------- ---------------
TABLE_A_NDX          OWNER                              1
                     OBJECT_ID                          2

TABLE_B_UNQ_NDX      OBJECT_ID                          1
                     OWNER                              2


SQL>

Table_A has a non-unique Index used to enforce a Primary Key constraint.
Table_B has an Index where the columns are ordered differently.





Here are quiz questions for readers :
1. What happens to the Index definitions if I DROP the constraints ?
2. What happens to the Index definitions if I DISABLE the constraints ?
3. We know that a Primary Key constraint requires that the columns be NOT NULL. Yet when I created the tables, the columns were created as NULLable. So, what gives ? OR, what works ?

.
.
.