Managing Constraints   «Prev  Next»
Lesson 4 UNIQUE constraints
ObjectiveCreate UNIQUE constraints on database tables.

UNIQUE Key Constraints on Tables

In the previous lesson, you learned to create a primary key for a table. One of the attributes of a PRIMARY KEY is that the value for a primary key must be unique. You also can force a column to be unique by using the UNIQUE constraint.
The UNIQUE constraint requires the column to contain a unique value for every row in the table. This constraint only applies to rows that actually have values. If a row contains a NULL value for a column, it satisfies a UNIQUE constraint, even if there are other rows in the table with NULL values. The reason for this is simple, namely a NULL value is not equal to anything, so the condition NULL = NULL always evaluates to FALSE.
Therefore, a NULL value in a unique column is still not equivalent to any other value in any other row in the table, and one or more NULL values does not violate a UNIQUE constraint.
Oracle uses indexes to enforce UNIQUE constraints, as well as PRIMARY KEY constraints, which also require unique values. If a unique index on a column that is the subject of a UNIQUE constraint already exists, the index is used to enforce the constraint. If there is no appropriate index, Oracle creates one.

Example

There are no UNIQUE constraints required in the COIN database, but if you want only a single client row for each client email address, you can define a unique client_email column by using the following syntax. View the code below to see it.
CREATE TABLE CLIENT (client_id NUMBER, client_name VARCHAR2(20), 
client_phone VARCHAR2(14), client_email VARCHAR2(40), 
client_password VARCHAR2(8), 
CONSTRAINT unique_client_email UNIQUE (client_email);

If you want to specify that Oracle use an existing index (idx_email) to enforce a constraint, you can define the constraint by using the ALTER TABLE syntax.
View the code below.
ALTER TABLE ADD CONSTRAINT unique_email 
UNIQUE (client_email) USING idx_email;


Oracle Constraints

A constraint enforces certain aspects of data integrity within a database. When you add a constraint to a particular column, Oracle automatically ensures that data violating that constraint is never accepted. If a user attempts to write data that violates a constraint, Oracle returns an error for the offending SQL statement
Constraints may be associated with columns when you create or add the table containing the column (via a number of keywords) or after the table has been created with the SQL command ALTER TABLE. Since Oracle8, the following constraint types are supported:

Unique

When you designate a column or set of columns as unique, users cannot add values that already exist in another row in the table for those columns, or modify existing values to match other values in the column. The unique constraint is implemented by the creation of an index, which requires a unique value. If you include more than one column as part of a unique key, you will create a single index that will include all the columns in the unique key. If an index already exists for this purpose, Oracle will automatically use that index.
The next lesson explains the FOREIGN KEY constraint.