UNIQUE KEY in SQL Server

This article’s goal is to add to “SQL Server All in One.”

The use of a unique constraint makes sure that no duplicate values are recorded in any given column that does not function as the primary key. A corresponding unique index is generated when a unique constraint is created.

Though the PRIMARY KEY consists of UNIQUENESS and NOT NULL, only one PRIMARY KEY constraint is allowed for each table. If the table contains additional columns that must be unique, the solution is UNIQUE KEY. There can be numerous unique key constraints per table.

Example-1 (System-generated constraint name)::
CREATE TABLE tblEmp(
ID INT IDENTITY(1,1) PRIMARY KEY,
RegistrationNumber INT UNIQUE,
EmailID VARCHAR(100) UNIQUE,
vcPhoneNumber VARCHAR(100) UNIQUE
);

INSERT INTO tblEmp (RegistrationNumber,EmailID,vcPhoneNumber) VALUES(‘200824′,’shaikshafi.nabi@gmail.com’,’09101291′)
INSERT INTO tblEmp (RegistrationNumber,EmailID,vcPhoneNumber) VALUES(‘200825′,’shafishaik.dxb@gmail.com’,’09101292′)
INSERT INTO tblEmp (RegistrationNumber,EmailID,vcPhoneNumber) VALUES(‘200826′,’nab_iam@yahoo.com’,’09101293′)

SELECT * FROM tblEmp;

Unique_Key_Constraint

Example-2 – (Defining user-defined constraint name):
CREATE TABLE tbTrxnDetails
(
TrxnID INT NOT NULL,
CONSTRAINT UNX_KEY_TransactionID UNIQUE(TrxnID)
)

Example-3 – Add a UNIQUE constraint to a column in an existing table.
ALTER TABLE tblEmployee
ADD CONSTRAINT UNIQUE_KEY_EmailID UNIQUE (EmailID);

Hope you find this article helpful.