The UNIQUE constraint guarantees that all values in a column are distinct.
Both UNIQUE and PRIMARY KEY constraints ensure uniqueness for a column or set of columns.
A PRIMARY KEY constraint inherently includes a UNIQUE constraint.
While multiple UNIQUE constraints are permissible per table, only one PRIMARY KEY constraint is allowed per table.
The provided 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 assign a name to a UNIQUE constraint and define it across multiple columns, utilize the following SQL syntax:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) ); |
To implement a UNIQUE constraint on the “ID” column after the table has been created, employ the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD UNIQUE (ID); |
To assign a name to a UNIQUE constraint and specify it across multiple columns, utilize the following SQL syntax:
ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName); |
To remove a UNIQUE constraint, employ the following SQL:
MySQL:
ALTER TABLE Persons DROP INDEX UC_Person; |
ALTER TABLE Persons DROP CONSTRAINT UC_Person; |