Violation of Unique Key constraint with SQL Server

How to avoid Violation of Unique Key constraint with SQL Server ? Insert or update data in an SQL Server table with a simple query? Here are two simple solutions to execute an update or insert and avoid errors. The SQL Server error ” Violation of UNIQUE KEY constraint . Cannot insert duplicate key in object . The duplicate key value is ” because the line you are inserting already exists in the target table.

How to avoid Violation of Unique Key constraint with SQL Server : “Violation of unique key constraint, cannot insert duplicate key in object” ?

Indeed, this SQL Server script creates a table with two columns: [Month] and [Amount]. In other words the month and the number of sales realized. Please note that the [Month] column as the UNIQUE keyword, so the table cannot store two lines for the same month.

Please execute this query first before using the solution queries. It’s creating a Sales sample table.

-- If the SALES table already exists in the database, we delete it
IF EXISTS(
SELECT 1 FROM sys.objects
WHERE object_id = object_id(N'[dbo].[SALES]') AND type in (N'U')
)
BEGIN
DROP TABLE [dbo].[SALES]
END
GO

-- Create the SALES table with the MONTH column declared as unique
CREATE TABLE [dbo].[SALES] (
[MONTH] nvarchar(20) UNIQUE,
[AMOUNT] numeric(5)
)
GO

-- Insert the sampla data
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 2000)

The error message displayed:

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint ‘UQ__SALES__*’. Cannot insert duplicate key in object ‘dbo.SALES’. The duplicate key value is (January).
The statement has been terminated.

Two solutions exist to do a simple INSERT or UPDATE.

1. The first solution to avoid violation of unique key constraint is the insert or update in a SQL query

Firstly, one solution is to use the EXISTS() function to check if a line with ‘January’ Month value already exists in the table. If no line exists then we insert the sale for 2000$ instead of 1000$.

IF EXISTS(SELECT * FROM dbo.SALES WHERE MONTH = 'January')
BEGIN
    UPDATE dbo.SALES
    SET AMOUNT = 2000
    WHERE MONTH = 'January'
END
ELSE
BEGIN
    INSERT INTO dbo.SALES ( MONTH, AMOUNT )
    VALUES ( N'January', 2000)
END

 

2. The second solution to avoid insertion of duplicate key is to execute an update or insert

Secondly, the other solution is to update the table, then check the number of lines updated, if it equals zero, then no line is there and we can insert our line with 2000$ as sales amount using a classical INSERT statement.

UPDATE dbo.SALES
SET AMOUNT = 2000
WHERE MONTH = 'January'

IF @@ROWCOUNT = 0
BEGIN
 INSERT INTO dbo.SALES ( MONTH, AMOUNT )
 VALUES ( N'January', 2000)
END

To conclude, we have seen two different ways or insert or update data in an SQL table and avoid the ” Violation of UNIQUE KEY constraint . Cannot insert duplicate key ” classical error. You might also want to learn more about the Arithmetic overflow error.