SQL UNIQUE Constraint

The UNIQUE constraint in SQL is used to enforce the uniqueness of values within one or more columns of a table. This prevents duplicate entries in the specified column(s).



Adding a UNIQUE Constraint

Using UNIQUE with CREATE TABLE

The simplest way to add a UNIQUE constraint is during table creation. This ensures uniqueness from the very beginning.

Single Column Example:

Syntax

-- SQL Server, Oracle, MS Access
CREATE TABLE Persons (
  ID INT NOT NULL UNIQUE,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT
);

-- MySQL
CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  UNIQUE (ID)
);
      
Output

Creates a table named 'Persons'. The 'ID' column will not allow duplicate values. In MySQL, the `UNIQUE` keyword is used differently than in other databases.

Named Constraints and Multiple Columns

You can name your constraint for better readability and organization. You can also apply uniqueness across multiple columns.

Syntax

-- MySQL, SQL Server, Oracle, MS Access
CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  CONSTRAINT UC_Person UNIQUE (ID, LastName)
);
      
Output

Creates a table named 'Persons'. The combination of 'ID' and 'LastName' must be unique; no two rows can have the same 'ID' and 'LastName' values.

Using UNIQUE with ALTER TABLE

If your table already exists, you can add a UNIQUE constraint using ALTER TABLE:

Syntax

-- MySQL, SQL Server, Oracle, MS Access (Single Column)
ALTER TABLE Persons
ADD UNIQUE (ID);

-- MySQL, SQL Server, Oracle, MS Access (Named Constraint, Multiple Columns)
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);
      
Output

Adds a unique constraint to the existing 'Persons' table. This will prevent duplicate values in the specified columns.

Dropping a UNIQUE Constraint

To remove a UNIQUE constraint:

Syntax

-- MySQL
ALTER TABLE Persons
DROP INDEX UC_Person;

-- SQL Server, Oracle, MS Access
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
      
Output

Removes the unique constraint named 'UC_Person' from the 'Persons' table. Duplicate values will now be allowed.