Cannot insert duplicate key

Question

Cannot insert duplicate key

techdur

SSC Enthusiast

Points: 186
More actions
March 31, 2010 at 4:52 am

#386612

I am getting this error.
Violation of PRIMARY KEY constraint 'XPKOrganizationCodes'. Cannot insert duplicate key in object 'dbo.OrganizationCodes'.
This is the line:
INSERT INTO dbo.OrganizationCodes (OrganizationNumber, OrganizationCode, OrganizationName) VALUES ('37', 'FSIS', 'First line of description')
Here is the table:
CREATE TABLE OrganizationCodes
(
OrganizationNumber varchar(20) NOT NULL ,
OrganizationCode varchar(40) NOT NULL ,
OrganizationName varchar(80) NOT NULL ,
BusinessAreaID int NULL ,
CONSTRAINT XPKOrganizationCodes PRIMARY KEY NONCLUSTERED (OrganizationNumber ASC,OrganizationCode ASC)
)
Thanks for the help.
Arun

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

The error message tells you that the table already contains a row with the values of '37', 'FSIS' for OrganizationNumber and OrganizationCode respectively. The primary key constraint is preventing you from entering a duplicate.

Can you provide a few more details please? For instance, is this from a client app, part of a data migration etc.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

techdur SSC Enthusiast Points: 186 More actions · Answer 2

The script is being used to insert values into this lookup table.

The error happened when testing the scripts to create a database, with all the tables etc.

The 'INSERT' is the first line in the script. There are about 20 more.

Is there any way that I can get past this error?

Thanks,

Arun

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Of course - in fact there are two very easy ways.

1. Remove the INSERT statement which contains the dupe

2. Remove the original row from the target table

It's not going to be that straightforward though.

Does the target table contain data before the script containing the INSERTs is run?

If it does, should a cleardown script run before the population script?

Is the data used for the INSERTs sourced from another table or built into the code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

DrJogalog SSC Veteran Points: 283 More actions · Answer 4

As Chris has already advised, you are trying to insert the same data into the table which is already there.

Your primary key on this table is the columns OrganizationNumber & OrganizationCode. This means there cannot be 2 records in this table with the same 2 values for both fields.

You are trying to insert a record with '37' as the OrganisationNumber and 'FSIS' as the OrganizationCode. This record ALREADY exists in that table so will not let you violate this primary key by adding the SAME record.

Either delete this record, don't try to re-insert it. or remove the primary key if you want to have duplicate values.

techdur SSC Enthusiast Points: 186 More actions · Answer 5

I will try these suggestions. I am sure there is pre-existing data, which should not be there!

Thanks much.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

techdur (3/31/2010)
I will try these suggestions. I am sure there is pre-existing data, which should not be there!
Thanks much.

No worries. Post back your findings, you can practically guarantee that someone's been in the same boat before.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

techdur SSC Enthusiast Points: 186 More actions · Answer 7

Yes. There was data in the table.

Once I deleted them, it ran fine.

Thanks for saving me a lot of time with this.

Appreciate the help.

Arun