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.