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