duplicate key row??

Question

duplicate key row??

Angelindiego

SSCertifiable

Points: 6048
More actions
November 14, 2006 at 3:59 pm

#67806

the following is a message that I got when trying to do an insert into my PERSON table. I had tried to do a previous insert with one row, making it fail on purpose to test some error code. The insert failed (as it should have) but now, when I fix it and try to move forward with the insert again, I get this error. Now, what baffles me is that there wasn't a previous insert...so why do I get this????? HELP OH WISE ONES!! Thanks in advance!!!!

Server: Msg 2601, Level 14, State 3, Line 2740
Cannot insert duplicate key row in object 'Person' with unique index 'PersonUserName'.
The statement has been terminated.
Unexpected error occurred in InsertPersonTable: 2601.
RowID FailedRowID
----------- -----------
Thank you!!,
Angelindiego

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

The row already exists in the index PersonUserName. Whether or not you've ran a previous INSERT, the row already exists. That is the only way to produce this message! It would help if you would include your SQL code for the INSERT examples.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Angelindiego SSCertifiable Points: 6048 More actions · Answer 2

Thanks John. Here is my insert code. It is done within a loop. For testing purposes, I am only trying to insert one record. Like I said, I already tried, and if failed (on purpose). What I don't understand here is if the insert FAILED....why is there a duplicate key in the index?? How do you reverse that? Can you reverse that? I wanted the insert to fail...but not to have this issue because of it...

insert into Person

(NhsNumber, LastName, FirstName, BirthDate, UserName, LanguageID, DomainID)

values

(@MRN, @Lastname, @FirstName, @Birthdate, @sHMC + convert(varchar(10),@iRowCounter), @LANGUAGE_ID, @DOMAIN_ID)

select @errorcode = @@ERROR, @PersonID = SCOPE_IDENTITY()

if @errorcode <> 0

begin

print 'Unexpected error occurred in InsertPersonTable: ' + convert(varchar(10), @errorcode) + '.'

set @bSuccessfulInsertGroup = @bFALSE

end

else

begin

set @bSuccessfulInsertGroup = @bTRUE

set @iInsertertedRowID = @PersonID

-- write ImportBackout table

insert into utilities.dbo.ImportBackout (ImportName, TableName, RowID) values (@sHMC, @sPERSON, @iInsertertedRowID)

end

Thank you!!,

Angelindiego

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Can you post your table DDL (including indexes) for the Person table? Also, are you starting with an empty table? The only way to get this error is if the data already exists. Are you asking for a way to write your INSERT statement so that it will only insert data if the row does not exist?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Angelindiego SSCertifiable Points: 6048 More actions · Answer 4

There is indeed data in the PERSON table! I am testing the import of new 'Persons'. What I am doing in the testing phase is 'de-identifying' the patient info and putting it in as Patient1, Patient2....with username HMC1, HMC2.....

With the insert failing, why does it say that the index duplicate key exists?? this is what I am failing to understand....

Here is my table schema: (sorry it is so long...)

CREATE TABLE [Person] (

[PersonID] [int] IDENTITY (1, 1) NOT NULL ,

[FirstName] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[MiddleName] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[LastName] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[UserName] [nvarchar] (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Password] [nvarchar] (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[PasswordReminder] [nvarchar] (250) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Title] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Address1] [nvarchar] (100) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Address2] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[City] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[StateCode] [nvarchar] (2) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[PostCode] [nvarchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CountryCode] [varchar] (3) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[LanguageID] [smallint] NOT NULL CONSTRAINT [DF_Person_LanguageID] DEFAULT (0),

[BirthDate] [datetime] NULL ,

[SocialSecurity] [nvarchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Gender] [varchar] (1) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[DayPhone] [varchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[HomePhone] [varchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CellularPhone] [varchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CellularPhone2] [varchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Fax] [varchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[InternetAccess] [bit] NULL ,

[Email] [varchar] (80) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CustomerNumber] [int] NULL ,

[Comment] [nvarchar] (255) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Allergens] [nvarchar] (255) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Signature] [bit] NULL ,

[ActivatedDate] [datetime] NULL ,

[InactivatedDate] [datetime] NULL ,

[DomainID] [int] NOT NULL ,

[CreatedTime] [datetime] NULL CONSTRAINT [DF_Person_CreatedTime] DEFAULT (getdate()),

[CreatedBy] [int] NULL ,

[LastModifiedTime] [datetime] NULL ,

[LastModifiedBy] [int] NULL ,

[SponsorID] [int] NULL ,

[PaperReport] [bit] NULL ,

[CreditCardTypeID] [int] NULL ,

[CreditCardNumber] [varchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardExpirationDate] [varchar] (5) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardPersonName] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardAddress] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardCity] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardStateCode] [varchar] (2) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardPostCode] [nvarchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[CreditCardCountryCode] [varchar] (3) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[BillingFrequency] [tinyint] NULL ,

[SystemAdministrator] [bit] NULL ,

[DomainAdministrator] [bit] NULL ,

[CustomerCarePerson] [bit] NULL ,

[QueryCoordinator] [bit] NULL ,

[QueryAdministrator] [bit] NULL ,

[Age] [int] NULL ,

[NhsNumber] [nvarchar] (20) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[GpName] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[RaceID] [int] NULL ,

[EthnicGroupID] [int] NULL ,

[DiabetesPlanOwner] [int] NULL ,

[OldPassword] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[PrivacyKey] [varchar] (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[MaritalStatusID] [int] NULL ,

[ContactAvailability] [nvarchar] (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[OwnerPersonID] [int] NULL ,

[OtherHCPName] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[OtherHCPPhone] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[OtherHCPProfession] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[OtherHCP2Name] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[OtherHCP2Phone] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[OtherHCP2Profession] [nvarchar] (40) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

[Credentials] [nvarchar] (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,

CONSTRAINT [PK_person] PRIMARY KEY NONCLUSTERED

(

[PersonID]

&nbsp WITH FILLFACTOR = 90 ON [PRIMARY] ,

CONSTRAINT [FK_Person_Country] FOREIGN KEY

(

[CountryCode]

&nbsp REFERENCES [Country] (

[CountryCode]

&nbsp,

CONSTRAINT [FK_Person_CreditCardType] FOREIGN KEY

(

[CreditCardTypeID]

&nbsp REFERENCES [CreditCardType] (

[CreditCardTypeID]

&nbsp,

CONSTRAINT [FK_Person_EthnicGroup] FOREIGN KEY

(

[EthnicGroupID]

&nbsp REFERENCES [EthnicGroup] (

[EthnicGroupID]

&nbsp,

CONSTRAINT [FK_Person_HealthCareDomain] FOREIGN KEY

(

[DomainID]

&nbsp REFERENCES [HealthCareDomain] (

[DomainID]

&nbsp,

CONSTRAINT [FK_Person_Language] FOREIGN KEY

(

[LanguageID]

&nbsp REFERENCES [Language] (

[LanguageID]

&nbsp,

CONSTRAINT [FK_Person_MaritalStatus] FOREIGN KEY

(

[MaritalStatusID]

&nbsp REFERENCES [MaritalStatus] (

[MaritalStatusID]

&nbsp,

CONSTRAINT [FK_Person_OwnerPerson] FOREIGN KEY

(

[OwnerPersonID]

&nbsp REFERENCES [Person] (

[PersonID]

&nbsp,

CONSTRAINT [FK_Person_Race] FOREIGN KEY

(

[RaceID]

&nbsp REFERENCES [Race] (

[RaceID]

&nbsp,

CONSTRAINT [FK_Person_Sponsor] FOREIGN KEY

(

[SponsorID]

&nbsp REFERENCES [Sponsor] (

[SponsorID]

&nbsp

) ON [PRIMARY]

GO

-----------------------

Index columns legend:

index_name index_description index_keys

IX_Person_OwnerPerson,

nonclustered located on PRIMARY,

OwnerPersonID

----------------

PersonUserName,

nonclustered, unique located on PRIMARY,

UserName

--------------------

PK_person,

nonclustered unique, primary key located on PRIMARY,

PersonID

Thank you!!,

Angelindiego

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Well, whatever value you are passing in for @sHMC and @RowCounter are already in the table.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

John McC Hall of Fame Points: 3410 More actions · Answer 6

Could it be that there is a null record already in the table?

The table allows nulls into UserName but the index based on it is unique.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

John Rowan SSC Guru Points: 56440 More actions · Answer 7

I would step through your code and find out what the value of @sHMC and @iRowCounter are right before the error. I think you'll find that the value already exists. Is @sHMC NULL? You have a unique index on the column, but still allow nulls. Make sure @sHMC has a valid value in it because @sHMC + convert(varchar(10),@iRowCounter) is NULL if either variable is a NULL value.

On another note, you said this is all happening within a loop. You may want to post your code. Odds are, there may be a set based way to accomplish what you are doing without looping.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Angelindiego SSCertifiable Points: 6048 More actions · Answer 8

Many thanks to all who helped me with ideas here!! I appreciate you!! Ok, I did total debug today...a dozen print statements to find out that my transaction rollback code wasn't working. It took a while, but when I found the problem, and fixed it, then it rolled back and I didn't get that duplicate key error. All is fine and dandy now!!!

Thank you!!,

Angelindiego

John Rowan SSC Guru Points: 56440 More actions · Answer 9

Well, what was the problem?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden