A constraint is defined by Oracle as 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.
The difference between a unique index and a unique key/primary key 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 primary key or unique key 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 - Unique key + Not Null which equates to every column in the key must have a value and this value is unique so the primary key uniquely identifies each and every row in the table.
Foreign Key - Restricts values in a table column to being a value found in the primary key or unique key Constraint on the referenced table (parent/child relationship).
Check - Tests the column value against an expression (rule).
Technically it would be possible for Oracle, to support primary key and unique key constraints without using an index at all. In the case of a unique key or primary key constraint Oracle 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 Oracle useless.
Prior to Oracle 8 if you defined a primary key or a unique key 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 primary key and unique key 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 unique key or primary key on the same columns since Oracle will build an index to support the constraint if you do not is that you can define foreign key constraints when the primary key or unique key constraint exist. Also in the case of a primary key constraint Oracle will convert the columns in the constraint to be not null constrained when it is added to meet the primary key requirement to uniquely identify each and every row in the table.
There is no such restriction on a unique index. The primary key and unique key constraints along with foreign key constraints that reference them also provide a form of documentation on the relationships between objects.
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 primary key or unique key 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 – Unique key constraints allow the constrained column to be NULL. Nulls values are considered to be valid and do not violate the constraint.
