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):
CREATE TABLE students
(
S_Id int NOT NULL UNIQUE,
LastName varchar (255) NOT NULL,
FirstName varchar (255),
City varchar (255)
) MySQL:
CREATE TABLE students
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:
CREATE TABLE students
(
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:
ALTER TABLE students
ADD UNIQUE (S_Id) (Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE students
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:
ALTER TABLE students
DROP INDEX uc_studentID SQL Server / Oracle / MS Access:
ALTER TABLE students
DROP CONSTRAINT uc_studentID
Leave a Reply