UNIQUE Constraint
The UNIQUE constraint makes sure that no two rows have the same value in a column (or in a group of columns).
Unlike PRIMARY KEY, a UNIQUE column can have NULL values. Only the values that are not NULL must be different.
Syntax
CREATE TABLE table_name (
column_name datatype UNIQUE
);
For a combination of columns:
CREATE TABLE table_name (
col1 datatype,
col2 datatype,
UNIQUE (col1, col2)
);
Or for a table that already exists:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);
Example
CREATE TABLE users (
user_id INT,
email VARCHAR(100) UNIQUE
);
INSERT INTO users VALUES (1, '[email protected]');
INSERT INTO users VALUES (2, '[email protected]');
Output:
CREATE TABLE
INSERT 0 1
INSERT 0 1
Now try to add another user with Alice's email:
INSERT INTO users VALUES (3, '[email protected]');
Output:
ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=([email protected]) already exists.
The insert failed, because [email protected] is already used. PostgreSQL named the constraint users_email_key, from the table and column names.
When to Use UNIQUE?
- When a value must never appear twice.
- For things like email addresses, usernames and phone numbers.
Next, you will learn PRIMARY KEY, which identifies each row in a table.
Answer the question below to check how UNIQUE works.