
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.
Be the first to comment