The UNIQUE constraint guarantees uniqueness for all values within a column.
The subsequent SQL establishes a UNIQUE constraint on the “ID” column during the creation of the “Persons” table:
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) ); |
To name a UNIQUE constraint and define it on multiple columns, employ the following SQL 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) ); |
To add a UNIQUE constraint on the “ID” column after the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD UNIQUE (ID); |
To name a UNIQUE constraint and define it on multiple columns, employ the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName); |
To remove a UNIQUE constraint, utilize the following SQL:
MySQL:
ALTER TABLE Persons DROP INDEX UC_Person; |
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT UC_Person; |