What is the difference between a unique index and a unique constraint ?

The Oracle (tm) Users' Co-Operative FAQ

What is the difference between a unique index and a unique constraint?

Back to index of questions

A constraint is defined by Oracle in the 8.1 Concepts manual, chapter 1, section on Integrity Constraints as being “a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true.” Personally, I have always considered constraints to be referential integrity rules that govern the allowable contents of a column and in the case of a primary key (PK) and unique key (UK) constraint, in conjunction with foreign keys (FK), define the formal relationship between columns and rows in one table to another.

The difference between a unique index and a UK or PK constraint starts with the fact that the constraint is a rule while the index is a database object that is used to provide improved performance in the retrieval of rows from a table. It is a physical object that takes space and is created with the DDL command: create index or as part of a create table with PK or UK constraints or an alter table command that adds these constraints (see SQL Manual).

Briefly the constraints are:

Not Null       Column value must be present
复制

Unique Key     Column(s) value(s) must be unique in table or null (see note below)
复制

Primary Key    UK + Not Null which equates to every column in the key must have a value
复制

               and this value is unique so the PK uniquely identifies each and every row
复制

               in the table
复制

Foreign Key    Restricts values in a table column to being a value found in the PK or UK
复制

               Constraint on the referenced table (parent/child relationship)
复制

Check          Tests the column value against an expression (rule)
复制

Technically it would be possible for a relational database management system, RDBMS, vendor to support PK and UK constraints without using an index at all.  In the case of a UK or PK constraint the RDBMS could perform a full table scan to check for the presence of a key value before performing the insert but the performance cost of doing this for anything other than a very small table would be excessive probably rendering the RDBMS useless.  So to the best of my knowledge every commercial RDBMS vendor that supports PK and UK constraints does so using indexes.

Prior to Oracle 8 if you defined a PK or a UK constraint the Oracle RDBMS would create a unique index to support enforcement of the constraint.  If an index already existed on the constrained columns Oracle would use it rather than define another index on the same columns.  Starting with Oracle version 8 Oracle has the ability to enforce PK and UK constraints using non-unique indexes.  The use of non-unique indexes supports deferring enforcement of the constraint until transaction commit time if the constraint is defined at creation time as deferrable.  Also starting with version 8 Oracle has the ability to place constraints on tables where the existing data does not meet the requirements imposed by the constraint through use of a novalidate option (see SQL Manual).

The practical difference between using a unique index to support data integrity and a UK or PK on the same columns since Oracle will build an index to support the constraint if you do not is that you can define FK constraints when the PK or UK constraint exist.  Also in the case of a PK constraint Oracle will convert the columns in the constraint to be not null constrained when it is added to meet the PK requirement to uniquely identify each and every row in the table.  There is no such restriction on a unique index.  The PK and UK constraints along with FK constraints that reference them also provide a form of documentation on the relationships between objects.  Some query tools make use of these relationships to define joins between the tables, example, Oracle Discoverer.  In the absence of an entity relationship diagram, ERD, having PK, UK, and FK defined in the database can be beneficial when trying to determine how to find and how to query data.

The Oracle RDBMS Data Dictionary views All/ DBA/ USER_CONSTRAINTS and ALL/ DBA/ USER_CONS_COLUMNS may be used to locate constraints on a table and the columns being constrained.

If you drop or disable a PK or UK constraint that is supported by a unique index the index is dropped with the constraint.  If a non-unique index is used to support the constraint the index is not dropped with the constraint.  This second condition is effective only with version 8 and higher.

Note – UK constraints allow the constrained column to be NULL.  Nulls values are considered to be valid and do not violate the constraint.

Further Reading: Oracle provides information on constraints in the Concepts manual, the Database Administrators Manual, the Application Developers Guide - Fundamentals, and the SQL manual.

There are also other FAQ articles related to Constraints.

I am unable to add a PK constraint to a table - what is the best way of finding the problem rows ?

How can I check if I have the right indexes for the foreign key constraints on a child table ?

How can I identify which index represents which primary or unique key constraint ?

Back to top

Back to index of questions