SQL UNIQUE Constraint

A UNIQUE constraint in SQL ensures that all values in a column (or set of columns) are unique. This is a crucial constraint for maintaining data integrity and preventing duplicate entries.

UNIQUE Constraint: Definition and Usage

The UNIQUE constraint guarantees that no two rows in a table will have the same value(s) in the specified column(s). This is similar to a primary key constraint, but a table can have multiple unique constraints, whereas it can only have one primary key. A primary key constraint automatically includes a unique constraint, but a unique constraint does not automatically include a primary key constraint. A primary key constraint enforces uniqueness and disallows null values, whereas a unique constraint enforces uniqueness but allows null values.

Adding UNIQUE Constraints

You can add UNIQUE constraints when creating a table (using CREATE TABLE) or later using ALTER TABLE.

UNIQUE Constraint on CREATE TABLE

This example creates the 'Persons' table with a unique constraint on the 'ID' column.

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

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

Named UNIQUE Constraints and Multiple Columns

You can give your constraint a name and apply it across multiple columns.

CREATE TABLE Persons (
  ID INT NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  FirstName VARCHAR(255),
  Age INT,
  CONSTRAINT UC_Person UNIQUE (ID, LastName)
);
      

Adding a UNIQUE Constraint After Table Creation

Use ALTER TABLE to add a unique constraint to an existing table.

ALTER TABLE Persons
ADD UNIQUE (ID);

--Named constraint and multiple columns:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);
      

ALTER TABLE Persons
DROP INDEX UC_Person;
      

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;