Exception Handling at the Table Level

I got a very interesting question from my client asking about the way to implement exception handling at the table level, that is if we insert duplicate records into a table then SQL Server should not throw any Primary Key violation errors.

This is very interesting and challenging question.

--Create a demo table
CREATE TABLE Customers(
Customer_ID int NOT NULL,
Customer_Name varchar(30) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Customer_ID] ASC
)
)

--Let’s insert dummy data
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')
INSERT INTO Customers VALUES(2,'VIJAYA KRISHNA')
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')

When you try to insert 3rd record you will get below error.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'.
The statement has been terminated.

This is very common behavior of any database. My task is to supress this error message. Initially I thought it is impossible to do it. But the Impossible word itself has “Possible”, so nothing is impossible. After closey observing the various parameters used in Table creation script, finally my search ended with IGNORE_DUP_KEY parameter.

I am not sure how many people would have got a chance to understand this parameter. This is the parameter which tells the SQL Server to throw an error message when it violates the constraints. By default this is set Off. We need to turn it on to enable exception handling at the constraint level.

-- Lets re-create demo table
CREATE TABLE Customers(
Customer_ID int NOT NULL,
Customer_Name varchar(30) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Customer_ID] ASC
)WITH (IGNORE_DUP_KEY = ON)
)

--Lets insert dummy data
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')
INSERT INTO Customers VALUES(2,'VIJAYA KRISHNA')
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')

When you try to insert 3rd record you will get below message.

Duplicate key was ignored.
(0 row(s) affected)

You can also use this clause at the Index Creation script level.
Ex:
CREATE UNIQUE NONCLUSTERED INDEX UNiDX_Customers ON Customers(Customer_Name) WITH (IGNORE_DUP_KEY = ON)