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>';
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.
Get our Articles via Email. Enter your email address.

You may also like...

17 Comments

  1. Bonnie says:

    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.

  2. andre liauw says:

    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

  3. Anuj Parashar says:

    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.

  4. Darl says:

    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.

  5. Sakthi says:

    Nice Article. Thanks .

  6. Tal says:

    Nice one. Thank you.

  7. Anand says:

    One of the best article I have read!

  8. Vipul says:

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

  9. Ygivon says:

    Simple clear and very convincing . Thank you Sir .

  10. mahesh says:

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

  11. Korlahalli Sridhar says:

    Informative. Very good article. Thank you.

  12. Soumya P says:

    Was worth knowing it.. thank u

  13. Guru says:

    Very nice article. Thanks for the info.

  14. nayana says:

    Thanks ,Very informative!!

  15. Suman Panigrahi says:

    good source of knowledge basics

  16. Kumar Gauraw says:

    Wow! The post seems to be written a while ago but the content is very powerful. Thank you for providing such a crisp explanation with examples as to when a unique and when a non-unique index can play alongside Primary Key. Very succinct!

  17. Mark says:

    Nice. I am new to Oracle. I disabled a pk constraint and found the index vanished. I wanted to understand how this works as this seems a fundemental of Oracle referential integrity. Many thanks.

Leave a Reply

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