Unique Constraint in SQL Server -- Part 1

In this article I would like to explain about “Unique Constraints”. Constraints are one of the key factors in designing a table. Before I explain what is the use of constraints lets take one real world example. When you are opening a bank account, there is a bank rule that says, you can not have more than one account with in same branch or location. This is a business rule.

To implement this kind of rule either we can use UNIQUE Constraint or Primary Key Constraint. This article will implement this business rule using UNIQUE KEY constraint.

The way I see is, implementing this in Back-end gives more flexibility. For now I will explain about how to implement this business rule in back-end.

There are total six types of constraints in SQL Server to implement different types of business rules and they are:

1) Check Constraints.
2) Rules
3) Default Constraints
4) Unique Constraints
5) Primary Key Constraints.
6) Foreign Key Constraints.

Other Relevant articles related to Constraints:
.. List all the Primary Keys in SQL Server Database
.. List all the Foreign Keys in a SQL Server
.. Foreign Key and Primary Key in SQL Server

Unqiue Constraint can be created in two different ways i.e. CREATE TABLE/ALTER TABLE and also at the two levels i.e. Table Level or Column Level. When the constraint is defined at the table level or on multiple columns it is also called as Composite constraint.

In my previous articles we did talk about Default,Check, Primary and Foreign Key Constraints. In this article we will talk about Unique Constraint.

Lets create ACCOUNT table and insert few records.

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,CHECK_IN_AMT INT,SAVINGS_AMT INT);

INSERT INTO ACCOUNT VALUES (10001,'NY',100010,1000,0);
INSERT INTO ACCOUNT VALUES (10002,'NJ',100020,800,0);
INSERT INTO ACCOUNT VALUES (10003,'LA',100030,0,800);

Table Level:

Business rule says “you can not have more than one account with in same branch or location”. This can be implemented by placing unique constraint on ACCOUNT_LOCATION and CUSTOMER_ID.

ALTER TABLE:

alter table ACCOUNT
add constraint UC_account01 unique(CUSTOMER_ID,ACCOUNT_LOCATION)

Let's try to insert below record.

INSERT INTO ACCOUNT VALUES(10004,'NJ',100020,1000,100);

SQL Server will throw below error
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UC_ACCOUNT01'. Cannot insert duplicate key in object 'dbo.ACCOUNT'.
The statement has been terminated.

CREATE TABLE:

CREATE TABLE ACCOUNT(ACCOUNT_ID INT,ACCOUNT_LOCATION VARCHAR(10),
CUSTOMER_ID INT,CHECK_IN_AMT INT,SAVINGS_AMT INT, CONSTRAINT UC_ACCOUNT01 UNIQUE(CUSTOMER_ID,ACCOUNT_LOCATION));

Couple of points on Unique Constraints:

1) UNIQUE constraints are to make sure that no duplicate values are entered in specific columns that do not participate in a primary key.
2) When you are placing an unique constraint it will automatically create Unique Index to maintain the integrity.
3) Unique Allows one Null value per column where as Primary key doesn’t allow null values.
4) There is no limit on having number of unqiue constraints on a table.
5) Unique Key constraint can be referenced in Foreign Key Column.