Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-unique-constraint/ below:

PostgreSQL - UNIQUE Constraint - GeeksforGeeks

Last Updated : 15 Jul, 2025

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.

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.

UNIQUE(column);

or,

variable_name Data Type UNIQUE;

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

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.

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.

• 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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4