|Lesson 4|| UNIQUE constraints |
|Objective||Create 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 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
The reason for this is simple, namely a
NULL value is not equal to anything, so the condition
NULL = NULL always evaluates to
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
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.
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),
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
View the code below.
ALTER TABLE syntax.
ALTER TABLE ADD CONSTRAINT unique_email
UNIQUE (client_email) USING idx_email;
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:
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.