|
|
SQL Server Error Messages - Msg 2627
Error Message
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint Constraint Name.
Cannot insert duplicate key in object Table Name.
Causes:
This error occurs when you are trying to insert a new row into a table that contains a PRIMARY KEY constraint and the key of the row being inserted already exists in the table. As an example, suppose you have a table containing the different currency codes:
|
CREATE TABLE [dbo].[Currency] (
[CurrencyCode] VARCHAR(3) NOT NULL PRIMARY KEY,
[CurrencyName] VARCHAR(50) NOT NULL
)
GO
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
VALUES ( 'CAD', 'Canadian Dollar' ),
( 'CHF', 'Swiss Franc' ),
( 'EUR', 'Euro' ),
( 'GBP', 'Pound Sterling' ),
( 'JPY', 'Yen' ),
( 'KRW', 'Won' ),
( 'USD', 'U.S. Dollar' )
GO
SELECT * FROM [dbo].[Currency]
GO
| CurrencyCode | CurrencyName |
|--------------|-----------------|
| CAD | Canadian Dollar |
| CHF | Swiss Franc |
| EUR | Euro |
| GBP | Pound Sterling |
| JPY | Yen |
| KRW | Won |
| USD | U.S. Dollar |
Figure 1: Error Message 2627 - [dbo].[Currency] Table
And you have another table, [dbo].[CountryCurrency], that contains the different countries and their corresponding currency code.
CREATE TABLE [dbo].[CountryCurrency] (
[Country] VARCHAR(50) NOT NULL,
[CurrencyCode] VARCHAR(3) NOT NULL,
[CurrencyName] VARCHAR(50) NOT NULL
)
GO
INSERT INTO [dbo].[CountryCurrency] ( [Country], [CurrencyCode], [CurrencyName] )
VALUES ( 'Argentina', 'ARS', 'Argentina Peso' ),
( 'Australia', 'AUD', 'Australian Dollar' ),
( 'Austria', 'EUR', 'Euro' ),
( 'Brazil', 'BRL', 'Brazilian Real' ),
( 'China', 'CNY', 'Yuan Renminbi' ),
( 'India', 'INR', 'Indian Rupee' ),
( 'Netherlands', 'EUR', 'Euro' ),
( 'Panama', 'USD', 'U.S. Dollar' ),
( 'Puerto Rico', 'USD', 'U.S. Dollar' ),
( 'Philippines', 'PHP', 'Philippine Peso' ),
( 'Tuvalu', 'AUD', 'Australian Dollar' )
GO
SELECT * FROM [dbo].[CountryCurrency]
GO
| Country | CurrencyCode | CurrencyName |
|-------------|--------------|-------------------|
| Argentina | ARS | Argentina Peso |
| Australia | AUD | Australian Dollar |
| Austria | EUR | Euro |
| Brazil | BRL | Brazilian Real |
| China | CNY | Yuan Renminbi |
| India | INR | Indian Rupee |
| Netherlands | EUR | Euro |
| Panama | USD | U.S. Dollar |
| Puerto Rico | USD | U.S. Dollar |
| Philippines | PHP | Philippine Peso |
| Tuvalu | AUD | Australian Dollar |
Figure 2: Error Message 2627 - [dbo].[CountryCurrency] Table
You want to add into the [dbo].[Currency] table all the currencies defined in the [dbo].[CountryCurrency] as follows:
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
SELECT [CurrencyCode], [CurrencyName]
FROM [dbo].[CountryCurrency]
GO
Since some of the currencies in the [dbo].[CountryCurrency] already exists in the [dbo].[Currency] table, such as EUR for Euro and USD for U.S. Dollar, the following error is encountered:
Server: Msg: 2627, Line 1, State: 1, Level: 14
Violation of PRIMARY KEY constraint 'PK__Currency__408426BE0016E031'.
Cannot insert duplicate key in object 'dbo.Currency'.
The duplicate key value is (EUR).
Msg: 3621, Line 1, State: 0, Level: 0
The statement has been terminated.
Figure 3: Error Message 2627 - Violation of PRIMARY KEY Constraint - EUR
Solution / Work Around:
To avoid this error, add a condition in the SELECT statement to only include currency codes that does not yet exist in the [dbo].[Currency] table:
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
SELECT [CurrencyCode], [CurrencyName]
FROM [dbo].[CountryCurrency] cc
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] c
WHERE cc.[CurrencyCode] = c.[CurrencyCode])
GO
But since there are 2 rows in the [dbo].[CountryCurrency] that is using the same currency, namely AUD for the Australian Dollar, even though this currency does not yet exist in the [dbo].[Currency] table, a similar error is generated:
Server: Msg: 2627, Line 1, State: 1, Level: 14
Violation of PRIMARY KEY constraint 'PK__Currency__408426BE0016E031'.
Cannot insert duplicate key in object 'dbo.Currency'.
The duplicate key value is (AUD).
Msg: 3621, Line 1, State: 0, Level: 0
The statement has been terminated.
Figure 4: Error Message 2627 - Violation of PRIMARY KEY Constraint - AUD
This error can be overcome by adding the DISTINCT clause in the SELECT query:
INSERT INTO [dbo].[Currency] ( [CurrencyCode], [CurrencyName] )
SELECT DISTINCT [CurrencyCode], [CurrencyName]
FROM [dbo].[CountryCurrency] cc
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] c
WHERE cc.[CurrencyCode] = c.[CurrencyCode])
GO
SELECT * FROM [dbo].[Currency]
GO
| CurrencyCode | CurrencyName |
|--------------|-------------------|
| ARS | Argentina Peso |
| AUD | Australian Dollar |
| BRL | Brazilian Real |
| CAD | Canadian Dollar |
| CHF | Swiss Franc |
| CNY | Yuan Renminbi |
| EUR | Euro |
| GBP | Pound Sterling |
| INR | Indian Rupee |
| JPY | Yen |
| KRW | Won |
| PHP | Philippine Peso |
| USD | U.S. Dollar |
Figure 5: Error Message 2627 - Solution
|