Cannot insert PK higher than 10

Question

Cannot insert PK higher than 10

lbgtp

Grasshopper

Points: 20
More actions
September 4, 2009 at 12:43 pm

#210296

I hope I have a simple issue for you experts.
Process: I'm using Altava Mapforce to do EDI mapping. Within the map I've inserted a SQL database for handling control numbers that I map to the output(target) file. Everytime the map(s) execute it attempts to add '1' to the controlNum and INSERT that as new row with the next incremented controlNum and date stamp. See the attached file edi-controlNum.jpg.
Issue: this is working successfully until it reaches a PK value 10. At that point I get the cannot insert duplicate controlNum errors. Also notice the order of the controlNum column below… the 10 is right after the 1. I'm thinking that is a little clue of my issue, but I'm not sure.
Table Name: InterchangeControl
Column Information:
controlNum – char(9) – Primary Key (Not Null Constraint)
transDate – datetime – (Not Null Constraint)
SELECT '1 ','Sep 2 2009 11:46AM'
SELECT '10 ','Sep 4 2009 10:12AM'
SELECT '2 ','Sep 4 2009 10:09AM'
SELECT '3 ','Sep 4 2009 10:09AM'
SELECT '4 ','Sep 4 2009 10:09AM'
SELECT '5 ','Sep 4 2009 10:09AM'
SELECT '6 ','Sep 4 2009 10:10AM'
SELECT '7 ','Sep 4 2009 10:10AM'
SELECT '8 ','Sep 4 2009 10:10AM'
SELECT '9 ','Sep 4 2009 10:10AM'
Error: Error: Transformation-Error in Database SQL-execution:
Error in: INSERT INTO [dbo].[InterchangeControl] ([controlNum],[transDate]) VALUES ('10','9/4/2009 10:17:31 AM')
Reason: Violation of PRIMARY KEY constraint 'PK_InterchangeControl'. Cannot insert duplicate key in object 'dbo.InterchangeControl'.
I'd appreciate any advice!

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

My first question would be why you have a column that's defined as an incrementing number, and the data type is char(10).

Second, what do you get if you select from that table where controlNum = '10'?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

lbgtp Grasshopper Points: 20 More actions · Answer 2

1. Good question. I made it char(9) because the field I'm mappping it to is a 9 character fixed length field. That was my thinking at the time.

2. I get SELECT '10 ','Sep 4 2009 10:12AM'

..thanks for the help...

GSquared SSC Guru Points: 260824 More actions · Answer 3

If there's already a row that's got 10 in the PK column, you can't insert another one with the same value. Primary Keys don't allow duplicate values.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

lbgtp Grasshopper Points: 20 More actions · Answer 4

Yes I know that. I think a 10 is trying to be inserted again because of the way the table is sorted... Mapforce sees the 9 as the last row.

Why would the 10 not be the last row?

Maybe I need to add an ORDER BY statement before the INSERT happens.

GSquared SSC Guru Points: 260824 More actions · Answer 5

The sequence of the inserts won't matter. If there's a duplicate value, there's a duplicate value. That's all that matters here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

lbgtp Grasshopper Points: 20 More actions · Answer 6

Can these two SELECT statements be mergerd into one with the ORDER BY happening first?

SELECT [controlNum], [transDate] FROM [EDI_Control].[dbo].[InterchangeControl] WHERE controlNum > 0 ORDER BY [transDate];

SELECT [controlNum], [transDate] FROM [EDI_Control].[dbo].[InterchangeControl] WHERE controlNum = (SELECT MAX(controlNum) FROM [EDI_Control].[dbo].[InterchangeControl]);

Solved = SELECT TOP 1 * FROM [EDI_Control].[dbo].[InterchangeControl] WHERE controlNum > 0 ORDER BY [transDate] DESC;

Mike McQueen SSChasing Mays Points: 631 More actions · Answer 7

Definitely an odd problem, as for why the 10 is showing up after the 1, it looks like since you've got it set to a char, it's sorting it in alphabetical order, ASC.

I will play around a little bit and see if I can recreate the problem and find a solution.