Understanding Primary Key(PK) Constraint in Oracle

primary-key
The Primary Key(PK) constraint is the most basic concept of any RDBMS (I am particularly interested in Oracle). Yet, I have noticed people getting confused when it comes to the practical usage and asking questions like:

– I have disabled PK and now oracle is doing full table scan.
– How PK constraints and indexes are related/different?
– How Oracle is using a non-unique index to enforce PK constraints?

Although these questions seem simple to the experienced users yet these can act as food for thought for the new developers. I have tried to consolidate few aspects about PK constraint which I found particularly confusing / worth knowing.

1. Primary key(PK) constraint and unique index are different.
PK constraint is a rule that prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
Index is a database object which is used for fast retrieval of data. It is created using DDL commands: “CREATE INDEX” or as part of a “CREATE TABLE” with PK/UK constraint or an “ALTER TABLE” command to add these constraints.

2. An enabled PK constraint is always associated with an index.
The associated index can be unique or non-unique (discussed later). The corresponding index can be find by querying:

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = '<TABLE_NAME>';

Also, if we have an enabled PK constraint, the corresponding column(s) will be “NOT NULL“. Now if you drop/disable the PK constriant, the column(s) will be changed to the state in which they were before adding the PK constraint.

-- Creating a table with two columns. One as NULL and other as NOT NULL
CREATE TABLE tbl_test ( col_1 NUMBER, 
                                  col_2 NUMBER NOT NULL);

-- Querying to check the the column nullable status 
SELECT table_name, column_name, nullable
  FROM user_tab_cols
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | COLUMN_NAME | NULLABLE
-- TBL_TEST   | COL_1       | Y
-- TBL_TEST   | COL_2       | N

-- Adding the the PK constraint
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

-- Querying to check the user constraints. 
--Two entries, one for NOT NULL constraint and one for PK constraint
SELECT a.table_name, b.column_name, a.constraint_name, 
           a.constraint_type, a.index_name
  FROM user_constraints a, user_cons_columns b
 WHERE a.table_name      = 'TBL_TEST' 
   AND a.constraint_name = b.constraint_name;
 
-- TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST   | COL_2       | SYS_C001231845  | C               |
-- TBL_TEST   | COL_1       | TBL_TEST_PK     | P               | TBL_TEST_PK

-- Rechecking the column nullable status. Both the columns are now NOT NULL
SELECT table_name, column_name, nullable
  FROM user_tab_cols
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | COLUMN_NAME | NULLABLE
-- TBL_TEST   | COL_1       | N
-- TBL_TEST   | COL_2       | N

-- Disabling the PK constraint
ALTER TABLE tbl_test DISABLE PRIMARY KEY;
-- OR
-- ALTER TABLE tbl_test DISABLE CONSTRAINT tbl_test_pk;

-- The column status is changed back as it was before adding the PK.
SELECT table_name, column_name, nullable
  FROM user_tab_cols
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | COLUMN_NAME | NULLABLE
-- TBL_TEST   | COL_1       | Y
-- TBL_TEST   | COL_2       | N

3. If the PK constraint is disabled, there will be no index associated with it.
The “index_name” in the above query would be blank. But the constraint name would still be there. So, PK constraint exists (with status as disabled) but there is no associated index.

DROP TABLE tbl_test;

CREATE TABLE tbl_test (col_1 NUMBER);

CREATE INDEX idx_col_1 ON tbl_test (col_1);

ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';

-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | IDX_COL_1

ALTER TABLE tbl_test DISABLE PRIMARY KEY;
-- OR
-- ALTER TABLE tbl_test DISABLE CONSTRAINT tbl_test_pk;

-- Once the PK is disabled, the association with the index is gone
SELECT constraint_name, constraint_type, index_name, status
  FROM user_constraints  
 WHERE table_name = 'TBL_TEST';

CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME | STATUS
TBL_TEST_PK     | P               |            | DISABLED

4. Once PK constraint is disabled, the index left on that column can be dropped.
If the index was created by oracle with the creation of PK constraint, it will be dropped automatically. If some existing index was associated with the PK constraint, it will not be dropped by oracle(refer point 6 for details). But its now possible to drop that index manually.

-- With the primary key disabled, the index can now be dropped
DROP INDEX idx_col_1;

SELECT table_name, index_name
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- no rows returned.

5. Enabling of the PK constraint requires association with index.
If we now try to enable the PK constraint again, it will pick up the first index it found on that column and will get associated with it. In case there is no index to get associated, oracle will create a new index with the name same as that of PK constraint.

ALTER TABLE tbl_test ENABLE PRIMARY KEY;
-- OR
-- ALTER TABLE  tbl_test ENABLE CONSTRAINT tbl_test_pk;

-- Oracle has created a new index with name "TBL_TEST_PK"
SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- A new index "TBL_TEST_PK" is created and associated with the PK constraint
-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | TBL_TEST_PK

SELECT table_name, index_name
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | INDEX_NAME
-- TBL_TEST   | TBL_TEST_PK

6. Use “USING INDEX” clause to associated a particular index with the PK.
If there are more than one indexes on the column on which you want to add PK constraint, we can selectively choose the index to be assoicated with the PK using “USING INDEX“. This clause can be used while:
a) Adding the PK constraint for the first time (using “ALTER TABLE” command).

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER, 
                        col_2 NUMBER,
                        col_3 NUMBER);

CREATE INDEX idx_col_1_2 ON tbl_test(col_1, col_2);

CREATE INDEX idx_col_1_3 ON tbl_test(col_1, col_3);

CREATE UNIQUE INDEX idx_col_1 ON tbl_test(col_1);

-- Forcing oracle to use the unique index "IDX_COL_1"
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1) 
USING INDEX idx_col_1;

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | IDX_COL_1

b) Enabling the PK constraint.

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER, col_2 NUMBER,
                        col_3 NUMBER);

CREATE INDEX idx_col_1_2 ON tbl_test(col_1, col_2);

CREATE INDEX idx_col_1_3 ON tbl_test(col_1, col_3);

CREATE UNIQUE INDEX idx_col_1 ON tbl_test(col_1);

ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | INDEX_NAME  | UNIQUENESS
-- TBL_TEST   | IDX_COL_1_2 | NONUNIQUE
-- TBL_TEST   | IDX_COL_1_3 | NONUNIQUE
-- TBL_TEST   | IDX_COL_1   | UNIQUE

-- Although an unique index exists, oracle has picked up the first index
SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | IDX_COL_1_2

ALTER TABLE tbl_test DISABLE PRIMARY KEY;

-- Forcing oracle to use the unique index
ALTER TABLE  tbl_test ENABLE CONSTRAINT TBL_TEST_PK USING INDEX IDX_COL_1;

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | IDX_COL_1

Manully associating PK constraint with already existing unique/non-unique index has the following advantages:
a) The index remains available and valid when the constraint is disabled.
b) Enabling the PK constraint doesn’t require rebuilding the unique/non-unique index associated with the constraint.
c) The redundant indexes can be eliminated. PK constraint can be associated with a composite index too if the column is included as the prefix of the composite index. So, in the example above, it iss possible to remove the unique index (if not required) and the composite index can be used for PK enforcement.

7. The index associated with the PK constraint needn’t be unique.
A non-unique index can also be be associated with the PK constraints. Now the question is how oracle allows PK constraint to be enforced using a non-unique index. Here is the explanation (as per best of my knowledge, might not be correct):

As described above, PK constraint is a rule to prohibit duplicate/null records for the PK column. Suppose, we already have 1 Million records in the table and inserting a new entry. So, to enforce the PK constraint, Oracle has to search through the already present records and this is where the index comes handy. If you have an index on that column, the search will be quite fast. The unique index will be the best but a non-unique index will also be a better option as compared to a full table scan. So, the basic purpose of associating index with PK constraints is to efficiently enforce the underlying rule. So, using index for PK constraint enforcement is a part of Oracle architecture (I assume its the same for all other RDBMS).

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER, col_2 NUMBER,
                        col_3 NUMBER);

CREATE INDEX idx_col_1_2 ON tbl_test(col_1, col_2);

-- Associating composite index with the PK constraint
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1) 
USING INDEX idx_col_1_2;

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | IDX_COL_1_2

 SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | INDEX_NAME  | UNIQUENESS
-- TBL_TEST   | IDX_COL_1_2 | NONUNIQUE

8. Merits of allowing non-unique index for enforcing PK constraints:
a) The non-unique indexes facilitates the use of “INITIALLY DEFERRED” clause with the constraint until the transaction has been committed if the PK constraint has been defined as “DEFERRABLE” at the time of creating. The “DEFERRABLE” PK constraint can’t be associated with a unique index.

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER, 
                        col_2 NUMBER);
                        
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1)  
INITIALLY DEFERRED DEFERRABLE;

-- The resulting index created by oracle is non-unique
SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';

-- TABLE_NAME | INDEX_NAME  | UNIQUENESS
-- TBL_TEST   | TBL_TEST_PK | NONUNIQUE

-- Allowing duplicate records inspite of the presence of PK consraint
INSERT INTO tbl_test VALUES (1,2);
INSERT INTO tbl_test VALUES (1,2);
INSERT INTO tbl_test VALUES (1,2); 

-- Constraint checked at the time of transaction commit
COMMIT; 
-- ORA-02091: transaction rolled back
-- ORA-00001: unique constraint (GC_ADMIN.TBL_TEST_PK) violated

b) The “NOVALIDATE” option can be used to exclude the enforcement of constraint on the already existing data.

DROP TABLE tbl_test purge;

CREATE TABLE tbl_test ( col_1 NUMBER);

INSERT INTO tbl_test VALUES (1);
INSERT INTO tbl_test VALUES (1);
INSERT INTO tbl_test VALUES (1);

ALTER TABLE tbl_test add constraint idx_col_1 PRIMARY KEY (col_1) NOVALIDATE;
-- ORA-02437: cannot validate (GC_ADMIN.IDX_COL_1) - primary key violated

ALTER TABLE tbl_test add constraint idx_col_1 PRIMARY KEY (col_1) DISABLE;

ALTER TABLE tbl_test ENABLE NOVALIDATE PRIMARY KEY;
-- ORA-02437: cannot validate (GC_ADMIN.IDX_COL_1) - primary key violated

-- This is because oracle tries to create unique index for the PK constraints. 
-- The statement fails while checking the uniqueness for creating the unique index. 
-- To fix this, create a non-unique index first. Then oracle will associate 
-- the primary key constraint with this non-unique index.
CREATE INDEX idx_col_1 ON tbl_test(col_1);

ALTER TABLE tbl_test ENABLE NOVALIDATE PRIMARY KEY;

9. Bitmap index can’t be associated with a PK constraint.

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER, 
                        col_2 NUMBER,
                        col_3 NUMBER);

CREATE BITMAP INDEX idx_col_1 ON tbl_test (col_1);

ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1) USING INDEX idx_col_1;
-- ORA-14196: Specified index cannot be used to enforce the constraint.

10. Dropping the PK may or may not drop the associated index.
If you drop a PK constraint, the associated index may or may not be dropped depending on the association of PK constraint and index. Two scenario arises:
a) The PK constraint is associated with an already present index (either by using “USING INDEX” clause or by default association if not specifically specified). In that case, the index will not be dropped with the dropping of PK constraint.

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER);

CREATE INDEX idx_col_1 ON tbl_test (col_1);

ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1) 
USING INDEX idx_col_1;

ALTER TABLE tbl_test DROP PRIMARY KEY;

-- Primary Key dropped
SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- no rows selected.
-- The index is still present
 SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | INDEX_NAME | UNIQUENESS
-- TBL_TEST   | IDX_COL_1  | NONUNIQUE

b) If the PK constraint is created while there is no index on PK column, oracle will create a new unique index with the same name as PK constraint. By default, this index will be dropped with the dropping of PK constraint. You can keep this index intact by using the “KEEP INDEX” clause.

DROP TABLE tbl_test;

CREATE TABLE tbl_test ( col_1 NUMBER);

ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';
 
-- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME
-- TBL_TEST_PK     | P               | TBL_TEST_PK
 
SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | INDEX_NAME  | UNIQUENESS
-- TBL_TEST   | TBL_TEST_PK | UNIQUE

ALTER TABLE tbl_test DROP PRIMARY KEY;

SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';

-- no rows selected.

 SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- no rows selected.

ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

ALTER TABLE tbl_test DROP PRIMARY KEY KEEP INDEX;

-- The PK constraint is gone
SELECT constraint_name, constraint_type, index_name
  FROM user_constraints
 WHERE table_name = 'TBL_TEST';

-- no rows selected.
-- Yet the index created by oracle is still there
 SELECT table_name, index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'TBL_TEST';
 
-- TABLE_NAME | INDEX_NAME  | UNIQUENESS
-- TBL_TEST   | TBL_TEST_PK | UNIQUE

Please note that the above mentioned points are also more or less applicable for Unique key(UK) constraints. I haven’t tried to touch that subject to keep the content precise.
All queries are tested and verified on Oracle 10.2.0.4 version.

Disclaimer: All data and information provided on this article is for informational purposes only. Author makes no representations as to accuracy, completeness, suitability, or validity of any information on this article. All information is provided on an as-is basis.



15 Comments

  • Bonnie 17 February, 2010, 2:40

    Hey Anuj,

    This seems very crisp yet detailed. Thanks, I have glanced through the initial sections, since I Was required to use those – and found them very clear. It would be good to see more articles on the commonly used Oracle meta data tables.

  • andre liauw 14 April, 2010, 13:43

    Hey Anuj,

    Very clear article. Thanks for that. I agree with Bonnie to see more articles on the commonly user Oracle meta data tables.
    This is my question.
    When running this command:
    ALTER TABLE XXX.XXX_TABLE ADD CONSTRAINT XXX_PK PRIMARY KEY (XXX_ID)
    for several tables in the apps schema, I got thousands of invalid objects.
    My question: what is causing this ?

    Kind regards,
    Andre

  • Anuj Parashar 15 April, 2010, 0:24

    Hi Andre,
    Good that you liked it. Regarding your question about invalid objects, I can say two things:

    1. It’s definitely not a side effect of creating primary key :)
    2. Most probably, the invalid objects are “synonyms”. The DDL operations on the tables cause the synonyms to get invalidated. Nothing to worry, they will be valid next time they are used. Or you can write a script to manually compile or recreate the synonyms.

    Please note that this behvaiour varies for different versions. In case you want to discuss it more, please send me the type of objects getting invalidated and the oracle version.

  • Darl 2 September, 2011, 1:22

    Nice article. Do you know if there is any way you can tell if an index associagted with a PK or UK index has automatically been created for you?

    I have a database, and I want to know beforehand if the index will be dropped when I disable the constraint. Or I want to know if the index will be preserved when I disable the constraint.

    If there isn’t a way to differentiate, then I suppose I should specify “keep index” if I want to explicitly keep the index when disabling a constraint.

  • Sakthi 14 October, 2011, 11:40

    Nice Article. Thanks .

  • Tal 17 April, 2012, 11:45

    Nice one. Thank you.

  • Anand 20 April, 2012, 15:51

    One of the best article I have read!

  • Vipul 11 May, 2012, 13:42

    Very good Artical. Every thing is cleared in one shot.

  • Ygivon 24 May, 2012, 16:39

    Simple clear and very convincing . Thank you Sir .

  • mahesh 24 May, 2012, 17:07

    Very nice article,it will clear use of indexes with primary key concept.

  • Korlahalli Sridhar 7 August, 2012, 10:38

    Informative. Very good article. Thank you.

  • Soumya P 20 August, 2012, 17:19

    Was worth knowing it.. thank u

  • Guru 27 June, 2013, 13:42

    Very nice article. Thanks for the info.

  • nayana 8 March, 2014, 17:39

    Thanks ,Very informative!!

  • Suman Panigrahi 18 July, 2014, 12:35

    good source of knowledge basics

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current ye@r *