PostgreSQL – UNIQUE Constraint

In PostgreSQL, the UNIQUE constraint is a powerful tool used to ensure that values stored in a column or a group of columns are unique across rows in a table. This constraint is essential for maintaining data integrity, especially when certain data should not be duplicated. For instance, if you’re storing email addresses, you wouldn’t want the same email to be associated with multiple users. The UNIQUE constraint helps you enforce this rule at the database level.

How the UNIQUE Constraint Works

Every time you insert a new row into a table with a UNIQUE constraint, PostgreSQL checks if the value already exists in the table. If the value is found to be a duplicate, PostgreSQL denies the insertion or update and issues an error. This ensures that no duplicate data can be entered, keeping your data consistent and reliable.

Syntax:

UNIQUE(column);

or,

variable_name Data Type UNIQUE;

PostgreSQL UNIQUE Constraint Examples

Now let’s look into some examples of the UNIQUE Constraint in PostgreSQL to better understand the concept.

Example 1: Applying a UNIQUE Constraint to a Single Column

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
);
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: Applying a UNIQUE Constraint to a Different Column

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)
);
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.

Important Points About PostgreSQL UNIQUE Constraint

• In PostgreSQL, a column with a UNIQUE constraint can contain multiple NULL values. PostgreSQL treats NULL as a non-value, so it does not consider NULL values as duplicates.
• You can define a UNIQUE constraint across multiple columns (composite key), ensuring that the combination of values across these columns is unique.
• PostgreSQL allows the deferring of UNIQUE constraints using the ‘DEFERRABLE’ and ‘INITIALLY DEFERRED’ keywords. This feature is useful in complex transactions where you temporarily need to violate the ‘UNIQUE’ constraint during the transaction.
• When you define a UNIQUE constraint on a column, PostgreSQL automatically creates a unique index on that column. This index is used to enforce the constraint.

R

RajuKumar19

Follow

Improve

Article Tags :

• PostgreSQL
• postgreSQL-constraints