Understanding Primary Key(PK) Constraint in Oracle

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>';
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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.
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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.
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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.