Introduction to PostgreSQL UNIQUE Constraint
PostgreSQL unique constraint is straight that all the records in 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 constraint in PostgreSQL. A unique constraint in PostgreSQL provides a unique constraint that 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 was created after the defining of 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));
The above syntax shows the unique constraint was created of a group of multiple columns simultaneously.
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: Table name on which column we are creating a unique constraint.
- 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 we are creating a unique constraint column or any group of the column, PostgreSQL will create an index automatically 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 cust_id column, Btree index was automatically created while using a unique constraint on 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 constraint 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 column. At the same time, we are creating a unique constraint on multiple columns at the time of table creation.
Example #4
Insert 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', 'abc@gmail.com', '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', 'abc@gmail.com', '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 table to add a column to the existing table and add a unique constraint on 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 value like employee ID or employee mail in the same column, we need to define unique constraint.
Recommended Articles
This is a guide to PostgreSQL UNIQUE Constraint. Here we discuss the introduction, how UNIQUE constraint works in PostgreSQL with respective examples. You may also have a look at the following articles to learn more –
