T-SQL - UNIQUE
The T-SQL (Transact-SQL) UNIQUE constraint is used to ensure that all values in a column of a table are different (unique). To ensure uniqueness of values in a column either UNIQUE or PRIMARY KEY constraints can be used. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, a table can have only one PRIMARY KEY constraint, but many UNIQUE constraints.
T-SQL UNIQUE constraint with CREATE TABLE
The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which UNIQUE constraint is applied on column EmpID.
CREATE TABLE Employee ( EmpID INT NOT NULL UNIQUE, Name VARCHAR(255) NOT NULL, City VARCHAR(100), Age INT, Salary DECIMAL(18,2) );
Alternatively, it can also be created as mentioned below:
CREATE TABLE Employee ( EmpID INT NOT NULL, Name VARCHAR(255) NOT NULL, City VARCHAR(100), Age INT, Salary DECIMAL(18,2), UNIQUE(EmpID) );
To provide a name to UNIQUE constraint, and to define a UNIQUE constraint on multiple columns (say EmpID and Name), the statement is given below:
CREATE TABLE Employee ( EmpID INT NOT NULL, Name VARCHAR(255), City VARCHAR(100), Age INT, Salary DECIMAL(18,2), CONSTRAINT UC_Employee UNIQUE(EmpID, Name) );
T-SQL UNIQUE constraint with ALTER TABLE
In the above example, a table called Employee is created. To enforce UNIQUE constraint on Salary column, the statement is given below:
ALTER TABLE Employee ADD UNIQUE (Salary);
To provide a name to UNIQUE constraint, and to define a UNIQUE constraint on multiple columns (say Age and Salary), the statement is given below:
ALTER TABLE Employee ADD CONSTRAINT UC_Employee UNIQUE (Age, Salary);
DROP UNIQUE constraint
To drop UC_Employee UNIQUE constraint from table called Employee, the statement is given below:
ALTER TABLE Employee DROP CONSTRAINT UC_Employee;