Unique Constraint

The UNIQUE constraint ensures that an attributes value is unique within its column. When inserting a row it checks if the value is already in the table. It the value exists it rejects the change and issues an error.

Syntax

CREATE TABLE <table-name> (
    <column-name> <column-type>,
    <column-name> <column-type>,
    ...
);

Example

CREATE TABLE Companies (
    name TEXT PRIMARY KEY,
    type TEXT UNIQUE,
    founded INT,
    hq TEXT,
    stock_symbol VARCHAR(5) UNIQUE
);

Primary vs. Unique keys

1. There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes.
2. No attribute of a PRIMARY KEY can ever be NULL in any tuple.
   1. But attributes declared UNIQUE may have NULL’s, and there may be several tuples with NULL.