|
|
Error Message
Server: Msg 2601, Level 16, State 1, Line 1
Cannot insert duplicate key row in object '<Object Name>'
with unique index '<Index Name>'.
Causes
A unique index guarantees that the index key contains no duplicate values and therefore every row in the table in some way unique. A unique index is created by including the argument UNIQUE when creating an index using the CREATE INDEX statement. The CREATE INDEX statement creates a relational index on a specified table or view and the index can be created before there is data in the table.
|
As the error message suggests, when inserting data into a table that contains a unique index and the data in the column or columns participating in the unique index already exist in the table, this error message will be raised.
To illustrate, here’s a simple table that contains a unique index on one of its columns:
CREATE TABLE [dbo].[Currency] (
[CurrencyCode] CHAR(3),
[CurrencyName] VARCHAR(50)
)
GO
CREATE UNIQUE INDEX [IX_Currency_CurrencyCode] ON [dbo].[Currency] ( [CurrencyCode] )
GO
The following script populates the table with data of currency codes:
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'USD', 'U.S. Dollar' )
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'EUR', 'Euro' )
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'GBP', 'Pound Sterling' )
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'INR', 'Indian Rupee' )
If by accident the same script is executed, the following error message will be generated for each INSERT statement executed:
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.Currency' with unique index 'IX_Currency_CurrencyCode'.
Another way of encountering this error is when inserting data to a table and the data are coming from another table. Here’s a script that illustrates this scenario.
CREATE TABLE [dbo].[Currency_New] (
[CurrencyCode] CHAR(3),
[CurrencyName] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Currency_New] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'CAD', 'Canadian Dollar' )
INSERT INTO [dbo].[Currency_New] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'SWF', 'Swiss Franc' )
INSERT INTO [dbo].[Currency_New] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'AUD', 'Australian Dollar' )
INSERT INTO [dbo].[Currency_New] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'CAD', 'Canadian Dollar' )
INSERT INTO [dbo].[Currency_New] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'JPY', 'Japanese Yen' )
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
SELECT [CurrencyCode], [CurrencyName]
FROM [dbo].[Currency_New] A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] B
WHERE A.[CurrencyCode] = B.[CurrencyCode])
GO
Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.Currency' with unique index 'IX_Currency_CurrencyCode'.
As can be seen from the script, the source table ([dbo].[Currency_New]) contains duplicate entries for the Canadian Dollar currency (CAD).
Solution / Work Around:
When inserting literal values into a table that contains a unique index in one of its columns that serves as the table’s key, there are a couple of ways of avoiding this error message. The first method is with the use of an IF statement that checks for the existence of the new data before performing an INSERT. If the row does not yet exist in the destination table, then it can be inserted.
Here’s how the script will look like:
IF NOT EXISTS (SELECT 'X' FROM [dbo].[Currency]
WHERE [CurrencyCode] = 'USD')
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'USD', 'U.S. Dollar' )
GO
The validation must be done for each set of literal values that needs to be inserted into the table.
Another way of checking for the existence of row in a table is still with the use of the NOT EXISTS statement but instead of using it in an IF statement, it can be used in the WHERE clause of a SELECT statement, as can be seen in the following script:
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
SELECT 'USD', 'U.S. Dollar'
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency]
WHERE [CurrencyCode] = 'USD')
GO
Instead of using the VALUES clause of the INSERT statement, a SELECT statement is used to insert the data into the table. Since the data is not coming from any table, the FROM clause of the SELECT statement need not be included. Similar to the first option, this has to be done for each row of data that will be inserted into the destination table.
In the case of inserting data coming from another table and the source table contains duplicates, there are also a couple of ways of avoiding this error from being raised. The first option is simply to add the DISTINCT clause in the SELECT statement to make sure that only distinct rows will be inserted into the destination table.
Here’s an updated version of the script with the DISTINCT clause:
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
SELECT DISTINCT [CurrencyCode], [CurrencyName]
FROM [dbo].[Currency_New] [New]
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] [Old]
WHERE [New].[CurrencyCode] = [Old].[CurrencyCode])
GO
Alternatively, the MERGE statement can be used instead of the INSERT INTO … SELECT WHERE NOT EXISTS statement. The MERGE statement performs insert, update or delete operations on a target table based on the results of a join with a source table.
Here’s how the script will look like using the MERGE statement.
MERGE [dbo].[Currency] AS [Target]
USING (SELECT DISTINCT [CurrencyCode], [CurrencyName]
FROM [dbo].[Currency_New]) AS [Source]
ON [Target].[CurrencyCode] = [Source].[CurrencyCode]
WHEN MATCHED THEN
UPDATE SET [CurrencyName] = [Source].[CurrencyName]
WHEN NOT MATCHED THEN
INSERT ( [CurrencyCode], [CurrencyName] )
VALUES ( [Source].[CurrencyCode], [Source].[CurrencyName] );
GO
As can be seen in this MERGE statement, a DISTINCT clause is still needed in the source table. If the DISTINCT clause is not included, the following error message will be raised:
Msg 8672, Level 16, State 1, Line 2
The MERGE statement attempted to UPDATE or DELETE the same row more than once.
This happens when a target row matches more than one source row.
A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.
Refine the ON clause to ensure a target row matches at most one source row, or use
the GROUP BY clause to group the source rows.
|