A unique constraint in PostgreSQL is a way to ensure that the values in a particular column or set of columns are unique. This means that no two rows in the table can have the same values in the specified columns. This can be useful in a variety of situations, such as when you want to ensure that each customer in a table has a unique identifier or when you want to ensure that no two users have the same email address.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
Unique constraints on new tables
To create a unique constraint in PostgreSQL, you can use the
CREATE TABLE or
ALTER TABLE statement, depending on whether you want to add the constraint to an existing table or create a new table with the constraint. Here is an example of how to create a unique constraint on the
CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT UNIQUE );
In this example, the
UNIQUE keyword is used to specify that the
users table can have the same email address.
Add a unique constraint to an existing table
Alternatively, if you want to add a unique constraint to an existing table, you can use the
ALTER TABLE statement like this:
ALTER TABLE users ADD UNIQUE (email);
In this case, the
ADD UNIQUE clause is used to add the unique constraint to the
The purpose of a unique constraint
A unique constraint will prevent you from inserting or updating a row if the values in the constrained columns are not unique. For example, if you try to insert a new row into the
users table with an email address that already exists in the table, you will get an error.
Unique constraints are similar to PRIMARY KEY columns
You can also sort-of define a unique constraint as a
PRIMARY KEY. The difference between the two is that a
PRIMARY KEY also automatically adds a
NOT NULL constraint, which means that the columns in the constraint cannot contain
A table can only have one primary key, as a primary key uniquely identifies each row of the table, so be careful using it instead of a unique constraint.
Primary key example
Here is an example of how to create a
PRIMARY KEY constraint on the
id column of the
CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT );
In this example, the
PRIMARY KEY constraint is applied to the
id column, which means that this column will have a unique constraint and will not allow
Composite unique constraints
Unique constraints can contain more than one column. This is useful if you want a column to be unique, but only within the scope of something else. For example in the
users table, we might want email to be unique for users in the same organization.
CREATE TABLE users ( id INTEGER PRIMARY KEY, organization_id INTEGER NOT NULL, email TEXT, UNIQUE (organization_id, email) );
or to alter an existing table
ALTER TABLE users ADD UNIQUE (organization_id, email)
One click editing of constraints
Beekeeper Studio provides a convenient GUI for working with constraints. Simply right click any table to View Structure, to view a list of all indexes and constraints. You can view, edit, delete, and create constraints without knowing any SQL.
Postgres Unique Constraint Summary
In summary, a unique constraint in PostgreSQL is a way to ensure that the values in a particular column or set of columns are unique. This can be useful for ensuring the integrity of your data and preventing duplicate values in your tables. To create a unique constraint, you can use the
CREATE TABLE or
ALTER TABLE statement and specify the
PRIMARY KEY keyword. Better yet, edit them with one click using Beekeeper Studio