Violation of unique key constraint cannot insert duplicate

2 solutions to fix the SQL Server error : violation of unique key constraint cannot insert duplicate key.

How to avoid and fix the Violation of unique key constraint, cannot insert duplicate key error during SQL Server development? 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 message is : Violation of UNIQUE KEY constraint . Cannot insert duplicate key in object . The duplicate key value is… The error appears because the line you are inserting already exists in the target table.

Table of Contents

1. Create the sample SQL Server table with a constraint

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 through these steps and results:

Drop the table if it already in the database
Create the sales sample table with a unicity constraint on the Month column
Insert 2 lines with the same month, i.e. January
Once the script is executed, it trigger the unique constraint error.

-- 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);

***SQL Server error : Violation of UNIQUE KEY constraint cannot insert duplicate key in SSMS***

The error message displayed:

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

To avoid the SQL Server violation of unique key error, 2 different but very similar solutions exists:

Do a simple INSERT or UPDATE : check if the line is in the table, and do an INSERT or an UPDATE.
Do an UPDATE on the table and count the lines updated, if it is 0 then perform the INSERT.

2. Avoid the violation of unique key constraint error using insert or update

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 NOT EXISTS(SELECT * FROM dbo.SALES WHERE MONTH = 'January')
BEGIN
    INSERT INTO dbo.SALES ( MONTH, AMOUNT )
    VALUES ( N'January', 2000)
END
ELSE
BEGIN
    UPDATE dbo.SALES
    SET AMOUNT = 2000
    WHERE MONTH = 'January'
END

3. Fix the insertion of duplicate key error with an update or insert

Secondly, the other solution is to update the table, then check the number of lines updated. If the number equals zero, then no line exists in the table. 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

4. Conclusion on T-SQL unique key constraint error

In conclusion, we have explored two distinct solutions to avoid the Violation of unique key constraint cannot insert duplicate key error in SQL Server. Both methods utilize INSERT and UPDATE statements to handle this issue effectively. The first approach checks if the record exists and performs either an INSERT or an UPDATE, while the second method uses an UPDATE followed by an INSERT if no rows were affected by the UPDATE.

By employing these strategies, you can prevent the violation of unique key constraints and ensure data integrity within your database. Additionally, understanding these techniques will help you to address other related errors, such as the Arithmetic overflow error, and improve your overall SQL Server expertise.