Unique Constraint in SQL Server -- Part 2

In this article I will talk about Column Level unqiue Constraints in SQL Server. These are very useful if you want to have rules based on one single column within a table.

Please check my article on Creating Unique constraint at the table Level.


This can be created in two different ways i.e. CREATE TABLE/ALTER TABLE

Let’s go with an example.

Business rule which I want to set is Customer SSN should be unqiue.

Lets create CUSTOMER table and insert few records.

CREATE TABLE CUSTOMER
(CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(50),
CUSTOMER_LOCATION VARCHAR(10),SSN VARCHAR(12))

INSERT INTO CUSTOMER VALUES(1,'SAMY','NY','111-11-1111');
INSERT INTO CUSTOMER VALUES(2,'BECK','NJ','222-22-2222');
INSERT INTO CUSTOMER VALUES(3,'STAN','TX','333-33-3333');
INSERT INTO CUSTOMER VALUES(4,'NICK','FL','444-44-4444');

ALTER TABLE:

alter table CUSTOMER
add constraint UC_Customer01 unique(SSN);

CREATE TABLE:

CREATE TABLE CUSTOMER
(CUSTOMER_ID INT, CUSTOMER_NAME VARCHAR(50),
CUSTOMER_LOCATION VARCHAR(10),SSN VARCHAR(12),CONSTRAINT UC_Customer01 unique(SSN))

Now lets try to insert one record with a new customer and existing SSN.

INSERT INTO CUSTOMER VALUES(5,'VINCE','FL','444-44-4444');

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UC_Customer01'. Cannot insert duplicate key in object 'dbo.CUSTOMER'.
The statement has been terminated.

It’s very simple isn’t it?How do you find list of Unique constraints in a database defined on a table? Is there any way I can drop these constraints?? To find the answers to these questions you need to check my next article.