Non – Unique index- Primary Key

Post is to explain that a non-unique index can police a primary constraint
SQL> create table rakesh as select rownum id, ‘rakesh’ text from dual connect by level <= 10000;

Table created.

SQL> create index rakesh_i on rakesh(id);

Index created.

SQL> alter table rakesh add constraint rakesh_pk primary key(id);

Table altered.

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_I’;

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_I
ID
NONUNIQUE RAKESH_PK P

Even if there are additional columns within the index, the fact the leading columns match the PK constraint means Oracle can still use the index to police the constraint as the index must be in the same logical order of these leading columns.
SQL> alter table rakesh drop primary key;

Table altered.

SQL> drop index RAKESH_I;

Index dropped.

SQL> create index rakesh_i on rakesh(id,text);

Index created.

SQL> alter table rakesh add constraint rakesh_pk primary key(id);

Table altered.

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_I’
2 ;

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_I
TEXT
NONUNIQUE RAKESH_PK P

RAKESH_I
ID
NONUNIQUE RAKESH_PK P

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –

using Deferrable clause:
This means the constraint could be used to defer the policing of the constraint until the time of the COMMIT, rather than at the time of the DML statement.
Oracle has to create a non-unique index in this case.
SQL> alter table rakesh drop primary key;

Table altered.

SQL> drop index rakesh_i;

Index dropped.

SQL> alter table rakesh add constraint rakesh_pk primary key(id) deferrable;

Table altered.

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_I’;

no rows selected

SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name =’RAKESH_PK’;

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_PK
ID
NONUNIQUE RAKESH_PK P

we can create non-unique index while defining the primary constraint

SQL> alter table rakesh drop primary key;

Table altered.

SQL> drop index rakesh_pk;

Index dropped.

SQL> alter table rakesh add constraint rakesh_pk primary key (id) using index (create index rakesh_1 on rakesh(id));

Table altered.

select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘RAKESH’;SQL>

INDEX_NAME
——————————
COLUMN_NAME
——————————————————————————–
UNIQUENES CONSTRAINT_NAME C
——— —————————— –
RAKESH_1
ID
NONUNIQUE RAKESH_PK P

 

Ref: https://richardfoote.wordpress.com/2008/06/04/primary-keys-and-non-unique-indexes-whats-really-happening/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s