Updated May 9, 2023
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.
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:
- 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:
- 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:
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:
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:
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:
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:
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:
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.
