SQL UNIQUE Constraint
SQL UNIQUE constraint is uniquely identifies each row(s) in a table. You can have multiple UNIQUE constraints defined in a table. It prevents to enter duplicate values on uniquely identified columns that do not have primary key specified.
We can create UNIQUE constraint on single column or multiple columns at the time of creating a table. You can also add the constraint after creating a table.
We will use UNIQUE constraint on RollNumber column of Students table as follows.
CREATE TABLE Students ( RollNumber int NOT NULL UNIQUE, Title varchar(8), FirstName varchar(50) NOT NULL, MiddleName varchar(50), LastName varchar(50), Country varchar(50) )
If you have a requirement to create multiple UNIQUE constraint on a single table, then you can create the same as shown below.
CREATE TABLE Students ( RollNumber int NOT NULL, Title varchar(8), FirstName varchar(50) NOT NULL, MiddleName varchar(50), LastName varchar(50), Country varchar(50) CONSTRAINT cn_students UNIQUE (RollNumber, Country) )
Where cn_students is constraint name with UNIQUE constraints on clumns RollNumber and Country.
You can add UNIQUE constraint on existing table column as given below. Here we have added constraint on LastName column.
ALTER TABLE Students ADD UNIQUE (LastName)
Or you can add multiple UNIQUE constraint as below.
ALTER TABLE Students ADD CONSTRAINT cn_students UNIQUE (RollNumber, Country)
If you want to delete UNIQUE constraint then you can do the same by below query.
ALTER TABLE Students DROP CONSTRAINT cn_students
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
