PostgreSQL – UNIQUE Constraint

PostgreSQL provides the user with a UNIQUE constrain that is used to make sure that values stored in a column or a group of columns are unique across rows in a table. Every time the user inserts a new row, PostgreSQL checks if the value already exists in the table if UNIQUE constraints are used. If it discovers that the new value is already present, it denies the change and issues an error. A similar process is carried out for updating existing data.

Syntax: UNIQUE(column); or variable_name Data Type UNIQUE;

Now let’s look into some examples.

Example 1:
In this example we will create a new table named person with a UNIQUE constraint for the email column using the below command:

CREATE TABLE person (
    id serial PRIMARY KEY,
    first_name VARCHAR (50),
    last_name VARCHAR (50),
    email VARCHAR (50) UNIQUE
);

Now we will insert some data to the table using the below command:

INSERT INTO person(first_name, last_name, email)
VALUES
    (
        'Raju',
        'Kumar',
        'rajukumar@gmail.com'
    );

Now if we try to insert the same email, PostgreSQL should raise an error. So let’s do so.

INSERT INTO person(first_name, last_name, email)
VALUES
    (
        'Nikhil',
        'Aggarwal',
        'rajukumar@gmail.com'
    );

Output:

ERROR:  duplicate key value violates unique constraint "person_email_key"
DETAIL:  Key (email)=(rajukumar@gmail.com) already exists.

Example 2:
PostgreSQL also allows users to create a UNIQUE constraint to the first_name using the below commands:

CREATE TABLE person (
    id SERIAL  PRIMARY KEY,
    first_name VARCHAR (50),
    last_name  VARCHAR (50),
    email      VARCHAR (50),
        UNIQUE( first_name)
);

Now we will insert some data to the table using the below command:

INSERT INTO person(first_name, last_name, email)
VALUES
    (
        'Raju',
        'Kumar',
        'rajukumar@gmail.com'
    );

Now if we try to insert the same email, PostgreSQL should raise an error. So let’s do so.

INSERT INTO person(first_name, last_name, email)
VALUES
    (
        'Nikhil',
        'Aggarwal',
        'nikhilagg@gmail.com'
    ),
    (
        'Raju',
        'Verma',
        'rajuverma@gmail.com'
    );

This should raise an error for the first_name of Raju Verma as Raju Kumar already exists.
Output:

ERROR:  duplicate key value violates unique constraint "person_first_name_key"
DETAIL:  Key (first_name)=(Raju) already exists.