PostgreSQL - UNIQUE

The PostgreSQL UNIQUE constraint is used to ensure that all values in a column of a table are different (unique). To ensure uniqueness of values in a column either UNIQUE or PRIMARY KEY constraints can be used. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, a table can have only one PRIMARY KEY constraint, but many UNIQUE constraints.

PostgreSQL UNIQUE constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which UNIQUE constraint is applied on column EmpID.

CREATE TABLE Employee (
  EmpID INT NOT NULL UNIQUE,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

Alternatively, it can also be created as mentioned below:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  UNIQUE(EmpID)
);

To provide a name to UNIQUE constraint, and to define a UNIQUE constraint on multiple columns (say EmpID and Name), the statement is given below:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CONSTRAINT UC_Employee UNIQUE(EmpID, Name)
);

PostgreSQL UNIQUE constraint with ALTER TABLE

In the above example, a table called Employee is created. To enforce UNIQUE constraint on Salary column, the statement is given below:

ALTER TABLE Employee
ADD UNIQUE (Salary);

To provide a name to UNIQUE constraint, and to define a UNIQUE constraint on multiple columns (say Age and Salary), the statement is given below:

ALTER TABLE Employee
ADD CONSTRAINT UC_Employee UNIQUE (Age, Salary);

DROP UNIQUE constraint

To drop UC_Employee UNIQUE constraint from table called Employee, the statement is given below:

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;