Unique Key in SQL
A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.
You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.
The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.
There is an automatically defined unique key constraint within a primary key constraint.
There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.
SQL UNIQUE KEY constraint on CREATE TABLE:
If you want to create a UNIQUE constraint on the "S_Id" column when the "students" table is created, use the following SQL syntax:
SQL Server / Oracle / MS Access:
(Defining a unique key constraint on single column):
(
S_Id int NOT NULL UNIQUE,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255)
)
MySQL:
CREATE TABLE students
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255),
UNIQUE (S_Id)
)
(Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
(
S_Id int NOT NULL,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255),
CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)
)
SQL UNIQUE KEY constraint on ALTER TABLE:
If you want to create a unique constraint on "S_Id" column when the table is already created, you should use the following SQL syntax:
(Defining a unique key constraint on single column):
MySQL / SQL Server / Oracle / MS Access:
ADD UNIQUE (S_Id)
(Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
ADD CONSTRAINT uc_StudentId UNIQUE (S_Id, LastName)
DROP SYNTAX FOR A FOREIGN KEY constraint:
If you want to drop a UNIQUE constraint, use the following SQL syntax:
MySQL:
DROP INDEX uc_studentID
SQL Server / Oracle / MS Access:
DROP CONSTRAINT uc_studentID
