Oracle – Facts About Primary Key vs. Unique Key You May Not Have Known

In this post we talk about Oracle specifically although some concepts apply to other databases.

At a high level – PK’s vs UK’s

Ask someone who has been doing database work about Primary Keys (PK) and Unique Keys (UK) and the differences between the two, they will tell you everything they know. Some even go into principles of normalization by E.F. Codd. The problem is that you know most of these already too.

At a high level –

Primary Key	Unique Key
·         Uniquely identifies every row in a database ·         There can be only one PK per table ·         Can consist of one or more columns ·         Columns comprising a PK are non-nullable	·         Uniquely identifies every row in the database ·         There can be more than one UK per table ·         Can consist of one or more columns ·         Columns comprising UK’s may be nullable

Before we go into some basic details, it helps to understand the differences between constraints and indexes – constraints are a mechanism to enforce data integrity and indexes are a mechanism to aid in the quick lookup of data – like bookmarking. They both serve two distinct purposes although they work together in certain scenarios thereby causing confusion to even seasoned professionals when different terms are mingled –  PK constraint, UK constraint, Unique index etc.

Here are some unsaid facts about PK’s and UK’s (in Oracle)

1. PK’s and UK’s create indexes:

A PK/UK must have a supporting index. If you create a table with just a PK/UK but with no indexes, index(es) will be automatically created for you. The index(es) will have the same columns as the PK/UK. Oracle uses the index to enforce the data integrity for the PK/UK constraint.

Illustration:

Create a new table named TEST_TABLE1

CREATE TABLE TEST_TABLE1
(
   COL1  INTEGER,
   COL2  VARCHAR2(100)
);

At this time, it has no constraints

SELECT *
FROM user_constraints
WHERE table_name = 'TEST_TABLE1';

No rows selected

…and no indexes

SELECT *
FROM user_indexes
WHERE table_name = 'TEST_TABLE1';

No rows selected

Let us create a PK constraint on COL1. Note that we are NOT creating an index to go with it

ALTER TABLE TEST_TABLE1
ADD CONSTRAINT TEST_TABLE1_PK
   PRIMARY KEY
   (COL1)
   ENABLE VALIDATE;

Can we see the new constraint?

SELECT *
FROM user_constraints
WHERE table_name = 'TEST_TABLE1';

OWNER	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME
MYUSERID	TEST_TABLE1_PK	P	TEST_TABLE1	…more columns

…we did not create any indexes, but Oracle implicitly created an underlying index!

SELECT *
FROM user_indexes
WHERE table_name = 'TEST_TABLE1';

INDEX_NAME	INDEX_TYPE	TABLE_OWNER	TABLE_NAME	TABLE_TYPE	UNIQUENESS
TEST_TABLE1_PK	NORMAL	MYUSERID	TEST_TABLE1	TABLE	UNIQUE	…more columns

2. Dropping PK’s and UK’s sometimes drops indexes:

If Oracle was responsible for automatically creating an index when a PK/UK constraint was created, it will also drop the index when the corresponding PK/UK constraint is removed.

Let us drop the new PK constraint and see what happens to the index

ALTER TABLE MYUSERID.TEST_TABLE1

DROP CONSTRAINT TEST_TABLE1_PK;

It has no constraints as expected

SELECT *
FROM user_constraints
WHERE table_name = 'TEST_TABLE1';

No rows selected

…and no indexes too! Oracle also dropped the index that it created

SELECT *
FROM user_indexes
WHERE table_name = 'TEST_TABLE1'; 

No rows selected

Let us drop the sample table for our next illustration

DROP TABLE TEST_TABLE1;

The results would be the same for an UK

3. Oracle only drops index(es) “It” automatically created but never the ones “You” created

We saw how Oracle implicitly creates indexes if none existed to support a new PK or UK. Here, we will see that if Oracle found an existing index to support the new PK or UK constraint, it will use that index. If the constraint is later dropped, it will NOT drop the underlying indexes if they existed before the PK’s were created. This even true if the index is a non-unique index with the same list of columns as a new PK/UK constraint. Remember that the uniqueness is enforced by the constraint using the index as a lookup mechanism and not by the index itself.

Create a new table named TEST_TABLE1 without any constraints

CREATE TABLE MYUSERID.TEST_TABLE1
(
   COL1  INTEGER,
   COL2  VARCHAR2(100)
);

Create a new UNIQUE index (this could be a non-unique index too!)

CREATE UNIQUE INDEX TEST_TABLE1_IDX01 ON TEST_TABLE1
   (COL1)
LOGGING
STORAGE    (
   BUFFER_POOL
   DEFAULT
)
NOPARALLEL;

Are there any constraints? No.

SELECT *
FROM user_constraints
WHERE table_name = 'TEST_TABLE1';

No rows selected

…yes, we do have our index

SELECT *
FROM user_indexes
WHERE table_name = 'TEST_TABLE1';

INDEX_NAME	INDEX_TYPE	TABLE_OWNER	TABLE_NAME	TABLE_TYPE	UNIQUENESS
TEST_TABLE1_IDX01	NORMAL	MYUSERID	TEST_TABLE1	TABLE	UNIQUE	…more columns

Now, let us create our PK constraint on COL1

ALTER TABLE TEST_TABLE1
ADD CONSTRAINT TEST_TABLE1_PK
PRIMARY KEY
   (COL1)
ENABLE VALIDATE;

Can we see the new constraint?

SELECT *
FROM user_constraints
WHERE table_name = 'TEST_TABLE1';

OWNER	CONSTRAINT_NAME	CONSTRAINT_TYPE	TABLE_NAME
MYUSERID	TEST_TABLE1_PK	P	TEST_TABLE1	…more columns

Let us drop the new PK constraint and see what happens to the index

ALTER TABLE TEST_TABLE1

DROP CONSTRAINT TEST_TABLE1_PK;

…and our index still exists!

SELECT *
FROM user_indexes
WHERE table_name = 'TEST_TABLE1';

INDEX_NAME	INDEX_TYPE	TABLE_OWNER	TABLE_NAME	TABLE_TYPE	UNIQUENESS
TEST_TABLE1_IDX01	NORMAL	MYUSERID	TEST_TABLE1	TABLE	UNIQUE	…more columns

Let us drop the sample table for our next illustration

DROP TABLE TEST_TABLE1;

The results would be the same for an UK

4. Indexes support quick data integrity checks for constraints:

Can you imagine how long it would take to validate a new PK or UK value without an underlying index? Yes, it would take a full table scan to validate every INSERT, UPDATE (to PK or UK) and DELETE. This is the reason why Oracle implicitly creates a supporting index.

 

5. You can have a row with NULL’s in every UK column:

We are not doing an example here but feel free to try on your own. If the UK constraint is made of multiple non-nullable columns, one is allowed to insert a row will NULL values for all the columns comprising the UK. PK’s are different. NULL’s are not allowed in any column comprising the PK.

 

6. NULL values are not indexed at all:

The underlying index to support PK’s and UK’s are B*Tree structures. These structures do not store NULL’s. This is not a problem with PK’s. However, with UK’s, even though there is a supporting index on one or more columns corresponding to an UK, the index is useless if the query has a WHERE clause littered with “IS NULL”.

 

7. FK’s without a supporting index result in full-table scans during DELETE’s on PK/UK table:

Although only slightly relevant to the topic of PK’s and UK’s let us consider this scenario – a table named TABLE_WITH_FK has a FK pointing to a parent table having the PK or UK – TABLE_WITH_PK.

When you delete a row from the PK/UK table TABLE_WITH_PK, to preserve data integrity, Oracle has to check the TABLE_WITH_FK to make sure that there are no rows corresponding to the TABLE_WITH_PK row you are about to delete. To do so, it if found an index on the TABLE_WITH_FK on the referenced column, the search will be quick. Otherwise, the search is a full-table scan especially if there are no other indexes that can help with this validation lookup. The principle is the same even when you do an ON DELETE CASCADE or ON DELETE SET NULL to deal with FK tables.

This is also true if you update the data in any of the PK/UK columns in TABLE_WITH_PK as it has to do similar checks on the FK table(s).

In conclusion

As a best practice, always create an index on the FK table on referenced columns. There are some disadvantages like not being able to TRUNCATE the PK table if there are FK references but that is the topic of another discussion.

 

Advertisement