{"id":287,"date":"2024-11-23T10:27:31","date_gmt":"2024-11-23T03:27:31","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=287"},"modified":"2025-01-02T08:41:54","modified_gmt":"2025-01-02T01:41:54","slug":"postgresql-unique-constraint","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-unique-constraint\/","title":{"rendered":"PostgreSQL UNIQUE Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PostgreSQL UNIQUE<\/code> constraint to ensure that all values in a specific column or a combination of columns are unique across the table.<\/p>\n\n\n\n

Introduction to PostgreSQL UNIQUE constraint #<\/a><\/h2>\n\n\n\n

In PostgreSQL, you can use a UNIQUE<\/code> constraint to ensure that all values in a column or combination of columns are unique<\/em> across the table.<\/p>\n\n\n\n

Here’s the syntax for defining a UNIQUE<\/code> constraint:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span> (\n  column1 data_type CONSTRAINT<\/span> constraint_name<\/span> UNIQUE<\/span>,\n  ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
Note that the CONSTRAINT<\/code> constraint_name<\/code><\/code> is optional. If you omit it, PostgreSQL will automatically generate a constraint name in the following format:<\/p>\n\n\n
{table_name<\/span>}_{column_name<\/span>}_key<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use the UNIQUE<\/code><\/code><\/code> keyword to instruct PostgreSQL that you are about to create a UNIQUE<\/code><\/code><\/code> constraint for the column.<\/p>\n\n\n\n
This syntax creates a column constraint because we define it as a part of a column definition.<\/p>\n\n\n\n
You can define a UNIQUE<\/code> constraint as a table constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span> (\n  column1 data_type,\n  ...,\n  CONSTRAINT<\/span> constraint_name<\/span> UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, we define the UNIQUE<\/code><\/code> constraint after the column list.<\/p>\n\n\n\n
In practice, you often define a UNIQUE<\/code><\/code> table constraint when it includes two or more columns:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span> (\n  column1 data_type,\n  column2 data_type,\n  CONSTRAINT<\/span> constraint_name<\/span> UNIQUE<\/span> (column1, column2)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Defining a UNIQUE constraint that includes one column #<\/a><\/h2>\n\n\n\n
First, create a table called brands<\/code> to store brands:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> brands (\n  brand_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The name<\/code> column in this brands<\/code> table has a UNIQUE<\/code><\/code> constraint. This means that there will be no duplicate values in the name<\/code> column.<\/p>\n\n\n\n
If you insert a brand that already exists in the brands<\/code> table, PostgreSQL will handle it by issuing an error, ensuring data integrity.<\/p>\n\n\n\n
Second, insert a new row into the brands<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  brands (name<\/span>)\nVALUES<\/span>\n  ('Apple'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 brand_id | name<\/span>\n----------+-------<\/span>\n        1<\/span> | Apple<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, attempt to insert the Apple<\/code> brand into the brands<\/code> table again:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  brands (name<\/span>)\nVALUES<\/span>\n  ('Apple'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL issued the following error:<\/p>\n\n\n
ERROR: duplicate key value violates unique constraint \"brands_name_key\"<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The error means you are trying to insert a value already in the brands<\/code> table under the name<\/code> column, violating the unique constraint brands_name_key<\/code><\/code>.<\/p>\n\n\n\n
Defining a UNIQUE constraint that includes multiple columns #<\/a><\/h2>\n\n\n\n
First, create a table<\/a> product_loans<\/code><\/code> to store product loans:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> product_loans (\n  loan_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  product_id INT<\/span> NOT<\/span> NULL<\/span>,\n  user_id INT<\/span> NOT<\/span> NULL<\/span>,\n  loan_date DATE<\/span> NOT<\/span> NULL<\/span>,\n  return_date DATE<\/span>,\n  UNIQUE<\/span> (product_id, user_id)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
This example defines a UNIQUE<\/code><\/code> constraint that includes the product_id<\/code><\/code> and user_id<\/code><\/code> columns.<\/p>\n\n\n\n
Second, insert rows into the product_loans<\/code><\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  product_loans (product_id, user_id, loan_date)\nVALUES<\/span>\n  (1<\/span>, 1<\/span>, '2024-11-23'<\/span>),\n  (1<\/span>, 2<\/span>, '2024-11-23'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 loan_id | product_id | user_id | loan_date  | return_date\n---------+------------+---------+------------+-------------\n       1 |          1 |       1 | 2024-11-23 | NULL\n       2 |          1 |       2 | 2024-11-23 | NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, attempt to insert a new row with duplicate values in both product_id<\/code><\/code> and user_id<\/code><\/code> columns:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  product_loans (product_id, user_id, loan_date)\nVALUES<\/span>\n  (1<\/span>, 1<\/span>, '2024-11-24'<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Error:<\/p>\n\n\n
duplicate key value<\/span> violates unique<\/span> constraint<\/span> \"product_loans_product_id_user_id_key\"<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Handling NULL with UNIQUE constraint #<\/a><\/h2>\n\n\n\n
In PostgreSQL, NULL<\/a><\/code><\/code> is different from another NULL<\/code>. Therefore, you can insert multiple NULL<\/code><\/code>s into a UNIQUE<\/code> constraint column. For example:<\/p>\n\n\n\n
First, create a table profiles<\/code> to store the user profiles:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> profiles (\n  user_id INT<\/span> PRIMARY KEY<\/span>,\n  first_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n  last_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n  phone VARCHAR<\/span>(25<\/span>) UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The phone<\/code> column is nullable and has a UNIQUE<\/code> constraint.<\/p>\n\n\n\n
Second, insert a new row into the profiles<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  profiles (user_id, first_name, last_name, phone)\nVALUES<\/span>\n  (1<\/span>, 'John'<\/span>, 'Doe'<\/span>, NULL<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 user_id | first_name | last_name | phone\n---------+------------+-----------+-------\n       1 | John       | Doe       | NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, attempt to insert a new row to the profiles<\/code> table with the value in the phone<\/code> column as NULL<\/code>:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  profiles (user_id, first_name, last_name, phone)\nVALUES<\/span>\n  (2<\/span>, 'Jane'<\/span>, 'Doe'<\/span>, NULL<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 user_id | first_name | last_name | phone\n---------+------------+-----------+-------\n       2 | Jane       | Doe       | NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> profiles;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 user_id | first_name | last_name | phone\n---------+------------+-----------+-------\n       1 | John       | Doe       | NULL\n       2 | Jane       | Doe       | NULL<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Finally, retrieve data <\/a>from the profiles<\/code> table:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
First, specify the constraint name for the UNIQUE<\/code><\/code> constraint. When you insert<\/a> or update<\/a> a value to a new value that already exists in the table, PostgreSQL will issue an error that includes the constraint name. This constraint name helps you detect where the problem is faster.<\/p>\n\n\n\n