Enforcing Unique Key Values- Tables and Constraints

In addition to creating a primary key constraint, you should create unique constraints on any combinations of columns that should always be unique within a table.

For example, for the primary key for a table, it is common to use a numeric key (sometimes called a surrogate key) that is populated via a sequence. Besides the surrogate primary key, sometimes users have a column (or columns) that the business uses to uniquely identify a record (also called a logical key).

Using both a surrogate key and a logical key does the following:
• Lets you efficiently join parent and child tables on a single numeric column
• Allows updates to logical key columns without changing the surrogate key

A unique key guarantees uniqueness on the defined column(s) within a table. There are some subtle differences between primary key and unique key constraints.

For example, you can define only one primary key per table, but there can be several unique keys. Also, a primary key does not allow a NULL value in any of its columns, whereas a unique key allows NULL values.
As with the primary key constraint, you can use several methods to create a unique column constraint. This method uses the UNIQUE keyword inline with the column:

SQL> create table dept( dept_id number
,dept_desc varchar2(30) unique);

If you want to explicitly name the constraint, use the CONSTRAINT keyword:

SQL> create table dept( dept_id number
,dept_desc varchar2(30) constraint dept_desc_uk1 unique);

As with primary keys, Oracle automatically creates an index associated with the unique key constraint. You can specify inline the tablespace information to be used for the associated unique index:

SQL> create table dept( dept_id number
,dept_desc varchar2(30) constraint dept_desc_uk1 unique using index tablespace users);

You can also alter a table to include a unique constraint:

SQL> alter table dept
add constraint dept_desc_uk1 unique (dept_desc) using index tablespace users;

And you can create an index on the columns of interest before you define a unique key constraint:

SQL> create index dept_desc_uk1 on dept(dept_desc) tablespace users; SQL> alter table dept add constraint dept_desc_uk1 unique(dept_desc);

This can be helpful when you are working with large data sets, and you want to be able to disable or drop the unique constraint without dropping the associated index.

Tip You can also enforce a unique key constraint with a unique index. See Chapter 8 for details on using unique indexes to enforce unique constraints.