EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL UNIQUE Constraint
 

PostgreSQL UNIQUE Constraint

Priya Pedamkar
Article byPriya Pedamkar

Updated May 9, 2023

PostgreSQL UNIQUE Constraint

 

 

Introduction to PostgreSQL UNIQUE Constraint

PostgreSQL unique constraint is straight that all the records in the table column are unique, duplicates are not allowed in PostgreSQL unique constraint. They state that a column or several groups of column rows values are unique in all rows in the tables. If we need to store unique value like mail ID or employee ID in the same column, we need to define unique constraints in PostgreSQL. A unique constraint in PostgreSQL provides a unique constraint that the data’s uniqueness was maintained correctly.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

CREATE TABLE table_name (column_name1 data type UNIQUE, column_name2 data_type,column_nameN data_type);

The above syntax shows the unique constraint was created at the time when we have defined a column name and data type.

CREATE TABLE table_name (column_name1 data type, column_name2 data_type,
column_nameNdata_type, UNIQUE (Column_name));

The above syntax shows the unique constraint created after defining the column name and data type.

CREATE TABLE table_name (column_name1 data type, column_name2 data_type,
column_nameNdata_type, UNIQUE (Column_name1, Column_name2));

According to the syntax above, a collection of several columns were simultaneously used to construct a unique constraint.

ALTER table table_name add column_name data_type UNIQUE;

Below is a parameter description of the above syntax:

  • Create: Create a table by using a unique constraint in PostgreSQL. We can create a constraint on the table column.
  • Column 1 to column N: Column name used while creating a column in PostgreSQL. In this column, we are creating a unique constraint.
  • Data type: Data type defines the type of data we have stored in the table. The data type is most important while creating a table.
  • Table name: We are creating a unique constraint on which column.
  • Unique constraint: PostgreSQL unique constraint is straight that all the records in the table column are unique, duplicates are not allowed in it.

How UNIQUE Constraint works in PostgreSQL?

While creating a unique constraint column or any group of the column, PostgreSQL will automatically create an index on that column.

The below example shows that the index will automatically create on the cust_id column:

Code:

CREATE TABLE discount (cust_ID INT Unique, product_name VARCHAR (100) NOT NULL, product_price varchar (10) NOT NULL, product_discount NUMERIC);

Output:

postgreSQL UNIQUE Constraint 1

  • In the above example, we have created a unique constraint on the cust_id column in the discount table.
  • On the cust_id column, the Btree index was automatically created while using a unique constraint on the cust_id column.
  • While creating a unique constraint on the column every time of data insertion, it will check duplicate records from all rows of a table.
  • If we are updating the value on the column row and which was already present in the table, it will issue an error.
  • If we are inserting a value that was present in a table, it will issue an error like “duplicate key violates unique constraint”.
  • The below figure shows the error message after inserting the same record, which was present in a table.
  • Insert into discount values (1, ‘ABC’, 100, 50);

Output:

postgreSQL UNIQUE Constraint 2

  • They state that a column or several groups of column rows values are unique in all the tables.
  • We can also create a unique constraint on the column after creating a table by using alter command.

Examples of PostgreSQL UNIQUE Constraint

Given below are the examples:

Example #1

Unique constraint creates at the time of defining the data type of the column.

Code:

CREATE TABLE Emp_UNI (emp_id INT UNIQUE, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);< >

Output:

Example #1

In the above example, we are creating a unique constraint on the emp_id column; after defining a unique constraint, the index will automatically create on the emp_id column. By default, it will create a Btree index on the emp_id column.

Example #2

Unique constraint creates after defining the data type of the column.

Code:

CREATE TABLE dis_uni (cust_ID INT, product_name VARCHAR (100) NOT NULL, product_price varchar (10) NOT NULL, product_discount NUMERIC, UNIQUE (CUST_ID));

Output:

postgreSQL UNIQUE Constraint 4

In the above example, we have created a unique constraint on the cust_id column. We are creating this unique constraint after defining a column data type.

Example #3

Create unique constraints on multiple columns.

Code:

CREATE TABLE Emp (emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_mail character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, UNIQUE (emp_id, emp_mail));

Output:

on multiple columns

In the above example, we have created an index on the emp_id and emp_mail columns. At the same time, we are creating a unique constraint on multiple columns at the time of table creation.

Example #4

Insert the record in the table after creating a unique constraint on the column.

The below example shows that we have to insert a record in the EMP table.

Code:

INSERT INTO Emp (emp_id, emp_name, emp_mail, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', '[email protected]', '1234567890', 20000, '01-01-2020');

Output:

insert record in table

Code:

INSERT INTO Emp (emp_id, emp_name, emp_mail, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', '[email protected]', '1234567890', 20000, '01-01-2020');
select * from EMP;

Output:

postgreSQL UNIQUE Constraint 8

In the first example, we have inserted a record it will insert without error. But at the second time, it will issue an error as duplicate key value violation unique constraints.

Example #5

Alter the table to add a column to the existing table and add a unique constraint on the same.

Code:

ALTER table EMP ADD column emp_code INT UNIQUE;

Output:

postgreSQL UNIQUE Constraint 9JPG

Conclusion

PostgreSQL unique constraints state that a column or several groups of column rows values are unique in all the tables. If we need to store unique values like employee ID or employee mail in the same column, we need to define a unique constraint.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL UNIQUE Constraint” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Foreign Key in PostgreSQL
  2. PostgreSQL Table
  3. Unique Key in SQL
  4. PostgreSQL Versions

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW