Unique Key Constraint with Example

UNIQUE constraint is used to enforce uniqueness of a column. It means that all values in the column are different i.e. the column should not allow any duplicate values.

Both PRIMARY KEY and UNIQUE KEY are used to enforce uniqueness of a column.

What is the difference between Primary Key and Unique Key?

  • A table can have only one Primary Key but more than one Unique Key.
  • Primary Key does not allow null, whereas Unique Key allows one null per column.
  • Also, Primary Key results in clustered unique indexes by default, whereas Unique Key results in non-clustered unique indexes by default.

The question is when do you choose Unique Key constraint?

Unique Key can be chosen if you want to apply uniqueness on 2 or more columns or when you have columns that should not contain duplicates.

Person Table

ID First_Name Last_Name Gender Contact Email
1 John Brown Male 123456 john@aa.com
2 Mary Wheeler Female 789456 mw@abc.com
3 Matt Hayden Male 112233 mat@xyz.com
4 Kathy Riche Female 456321 kat@bb.com
5 Geoff Adams Male 789123 ad@pqr.com

Assuming that ID is the primary key, and then we may want to place a unique constraint on Contact and Email to ensure each Person has unique info details.

Example of Adding Unique Constraint­­

ALTER TABLE Person

ADD CONSTRAINT UC_Person UNIQUE (Contact, Email);

SYNTAX

ALTER TABLE Table_Name

ADD CONSTRAINT Constraint_Name Unique(Column_Name)

A unique constraint is created using person table called UC_Person using more than one field. The pair of contact and email value is unique in the Person table separated by a comma.

Removing Sql Unique Constraint

ALTER TABLE Person

DROP CONSTRAINT UC_Person;

SYNTAX

ALTER TABLE Table_Name

DROP CONSTRAINT Constraint_Name;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s