'Unique Constraint' is used on a column to ensure that the values being entered in the specified column are distinct and not repeating.
Unique Constraint
The 'Unique Constraint' ensures that all values in this column or set of columns are unique and there is no duplicate values. Even a null value
can be used here but only once in an unique column.
SQL 'Unique Constraint' Syntax
CREATE Table tablename
(
column1 datatype UNIQUE,
column2 datatype,
column3 datatype,
column4 datatype,
.
.
.
columnn datatype
)
The above method of creating a table creates an 'Unique' constraint on column1 at the column level as it is defined as part of a column definition.
The Unique Constraint Example
CREATE TABLE Employees
(
EmployeeID smallint UNIQUE,
EmployeeName nvarchar(50) NOT NULL,
DateOfBirth smalldatetime NOT NULL,
DesignationID smallint NULL,
DeptID smallint NULL,
PhoneNo nvarchar(12) NULL,
City nvarchar(50) NOT NULL,
Salary decimal(5, 2) NULL
)
Note: When creating the columns, the data type of the column must be given so that you know what datatype it holds.
If it's a non integer, you need to specify the length of the column also. Here we are ensuring that 'EmployeeID' column is unique.