Primary Key conflict

[dburniston]

I have a table that has a primary key for a column called itemserialnumber based on 5 other columns in the table. I entered a new row then deleted it.

When I try to add it again I get an error saying it cannot insert duplicate key row for the column itemserialnumber.

Is this because the index needs to be cleaned up? If so how do I do this.

I'm running SQL Server 2000.

Thanks,

...Dave

 

[SonOfEmidec1100]

The indexes should get cleaned up automatically, to see if you have any problems run DBCC checkdb ('dbname')

Look up DBCC in BOL how to proceed if there are any problems.

If there are no problems I would have to guess the new value for itemserialnumber is not the same as the one being deleted.

Hope this helps

 

[dburniston]

I've done some more investigation.

The error says that a duplicate for column itemserialnumber would occur for key aaaatblNewItem_PK.

When I look at the data, the values for itemserialnumber are all different numbers except for the last question I entered which is a zero. I expect that the next question I tried to enter also resulted in a zero.

Itemserialnumber colum has a default value of dbo.UW_ZeroDefault.

The definition for the key aaaatblNewItem_PK is:

Table Name: tblNewItem
Selected Index: aaaatblNewItem_PK
Type: PRimary Key
Index Name: aaaatblMewItem_PK
Column Names:
VendorID ascending
CourseNumber ascending
Language ascending
SectionNumber ascending
ObjectiveNumber ascending
ItemNumber ascending

Index Filegroup: Primary

The value for my entries doesn't seem to be calculated the same as the original entries in the table. The database was designed by another company and we inherited it, so I'm not super familiar with the design.

Here's what I'm using to enter new data:

INSERT INTO tblNewItem (SectionNumber,ObjectiveNumber,ItemNumber,Stem,TutorialText,VendorId,CourseNumber,[Language],TechnicallyCorrect,GrammaticallyCorrect,Congruent,OnSampleTest1,OnSampleTest2,OnSampleTest3,OnSampleTest4,OnSampleTest5,ItemType,McNumberCorrectRequired,FrCaseSignificant,FrMultipleConsecutiveSpacesSig,Weighting,RandomInd,ItemUsage)
Values(0,0,7,'Prior to a training session,...additional text','The correct answer is D.','1','FKA','enu',0,0,0,0,0,0,0,0,'mc',0,0,0,0,1,'p')


The itemserialnumber always comes out as zero when I add instead of a non-unique number as in the already existing data.

Any suggestions?

Sorry for the long post.

...Dave

 

[dburniston]

Seems I misunderstood something and thought the value of
the field was calculated. Apparently it isn't and I have
to find a way to generate the number.

Thanks for the help

...Dave