The UNIQUE constraint is used to uniquely identify each row in a database table. It is some similar to Primary key, but has some difference:
- Primary Key is automatically Unique Constraint
- One table should have ONE primary key, but could have mutiple Unique Constraint
To create a table with UNIQUE Constraint:
SQL Server
CREATE TABLE Users ( UserID int NOT NULL UNIQUE , FirstName varchar(100) NOT NULL, LastName varchar(100) NOT NULL )
My SQL
CREATE TABLE Users ( UserID int NOT NULL, FirstName varchar(100) NOT NULL, LastName varchar(100) NOT NULL, UNIQUE (UserID ) )
You can also set Unique Constraint by combining multiple columns:
My SQL / SQL Server / Oracle
CREATE TABLE Users ( UserID int NOT NULL, FirstName varchar(100) NOT NULL, LastName varchar(100) NOT NULL, CONSTRAINT uc_UserFullName UNIQUE (FirstName, LastName ) )
To set UNIQUE Constraint by using ALTER TABLE:
SQL Server / MySQL / Oracle / MS Access:
ALTER TABLE Users ADD UNIQUE (UserID)
A UNIQUE Constraint can be a combine of multiple columns:
SQL Server / MySQL / Oracle / MS Access:
ALTER TABLE Users ADD CONSTRAINT uc_UserFullName UNIQUE (FirstName, LastName )
To drop a UNIQUE Constraint:
SQL Server:
ALTER TABLE Users DROP CONSTRAINT uc_UserFullName
My SQL:
ALTER TABLE Users DROP INDEX uc_UserFullName