PostgreSQL Unique Constraints
Unique constraint is used to enforce data integrity in PostgreSQL tables. It ensures that no duplicate values are entered in specific columns that is not primary key column.
use a UNIQUE constraint instead of a PRIMARY KEY constraint if you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
The unique constraint can be applied to one or more columns in a table. PostgreSQL raises an error when you try to insert or update a value which is already exists in a column.
A NULL value in a column means missing data, and the NULL value of one column does not equal a NULL value in another column. Hence unique constraint allows storing one NULL value in the column.
Unique constraints can be defined at the table level or at the column level, as shown below.
CREATE TABLE table-name ( column1 data_type(length) UNIQUE, column2 data_type(length), ... UNIQUE(column2) );
In the above syntax, the unique constraint is defined on
column1 at column level and for
column2 at table level.
The following create a unique constraint on the
CREATE TABLE IF NOT EXISTS employee (emp_id INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50), gender CHAR(1), birthdate DATE, email VARCHAR(100) UNIQUE, salary INT);
The above CREATE TABLE statement will create the following
employee table with the unique constraint:
Create Unique Constraint on Multiple Columns
When a unique constraint is defined on a group of columns, then the combination of those column values needs to be unique across the table. A Unique constraint can be defined on multiple columns by specifying it at table level.
CREATE TABLE table_name ( column1 data_type(length), column2 data_type(length), ... UNIQUE(column_1, column_2) );
Let's add a unique constraint on combination of
CREATE TABLE IF NOT EXISTS employee (emp_id INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50), gender CHAR(1), birthdate DATE, email VARCHAR(100), salary INT, UNIQUE (first_name, last_name));
In the above example, the
first_name column also has a NOT NULL constraint also.
Hence, you can have multiple constraints defined on one column.
Adding UNIQUE Constraint on Existing Table:
Unique constraints can be added on one or more columns of the existing table, provided that column or set of columns have unique values across the table rows.
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column1,column2,..);
The following will add unique constraint on the
ALTER TABLE employee ADD CONSTRAINT unique_employee_email UNIQUE(email);
Create Unique Constraint using pgAdmin
You can create unique constraints on new or existing tables using pgAdmin. The steps would remain the same if you add unique constraints on new tables or existing tables.
Expand your databases node in the left pane and then expand Schemas -> public -> Tables nodes.
Now, right-click on the table where you want to add the unique constraints and click on 'Properties' in the menu.
In the popup, go to the 'Constraints' tab and click on the Unique tab, as shown below.
Now, to add a unique constraints, click on the
+ button to add a row in a grid. Again click on the edit icon to add unique column definition, as shown below.
As you can see above, add unique constraint name in the
Now, go to the
Definition tab and select one or more columns where you want to apply the unique constraint, as shown below.
Click on the Save button to add the unique constraint on the selected columns.
In the same way, you can add unique constraints to a new table using pgAdmin.