Search code examples
sqlsql-serverasp-classicoledb

Violation of PRIMARY KEY constraint PK. Cannot insert duplicate key in object (table1), The duplicate key value is (Col1, Col2, Col3, Col4)

I have a product entry page through we keep on adding product entries in our database.

Product location wise there are 2 series. e.g., ABCTMP(Series(1-max)) and XYZ(Series(1-max)).

Table is having primary key constraint which is a combination of 4 columns. Out of 4, only one is giving an issue while increment series combination wise.

That first column is location wise product code as stated above and it is of data type char(20) as it stores values like ABCTMP01 and through classic asp code. We increment that last 01 value by addition of one into existing value.

Right now, facing issue when last value reaches 99 and turns to 100. It generates code 100 through code but unable to insert in database and giving this error that was due to existing entry in database.

Duplicate key part is same one which I mentioned above in subject/header. If I delete record from table of record no. 100 to check, it gives me proper record of 99 through above query and through above classic asp code, it generates next code as 99+1 = 100.

But when I again try to add next series record for 101, even through SQL mgt studio, it gives me below error.

Violation of PRIMARY KEY constraint 'PK'. Cannot insert duplicate key in object 'prdct_mst_tab'. The duplicate key value is (PWATERTMP100 , 006, Y, 01). The statement has been terminated.

Have tried by dropping constraint and changing size of data type char(20) to char(30) as there are dependencies on table. But not worked. Then, have tried by changing data type from char(30) to varchar(30), still not worked. Then again tried by manually

executing insert command

in SQL itself, but same error occurred for 101th record.

Before generating next series, there is select statement to check latest inserted record which will get incremented later.

For generating next record of 101,there select statement must show last inserted record of 100, but it's still giving 99th record and code is generating as 100 again and the error continues the same. I do not understand why it's not taking 100th record when I execute SELECt statement in SQL mgt studio. DataType of that PWATERTMP100 column is char(20).

Below my classic asp code for series generation and SQL 'SELECT top 1 *' statement for record count for location wise product.

select top 1 * 
from prdct_mst_tab 
where pmt_prdct_cd like 'PWATER%' 
and pmt_umt_unit_cd='006' 
AND PMT_CMT_CMPNY_CD='01' 
order by pmt_prdct_cd desc

Classic ASP Code: -

If recordset.eof Then 
    getcode="ABCTMP01" 
Else
    getcode = clng(Mid(recordset("Column1"),10,20))
        response.write("Hello" & getcode)
    getcode = getcode +1
        response.write("<br />Hello" & getcode)
    getcode = "ABCTMP" & getcode
        response.write("<br />Hello" & getcode)
End if

Below for adding generated product code in database table.

Sql is as below 

select * from Table1
recordset.open sql,con,3,2
recordset.addnew
recordset("Column1")=getcode
recordset.update 
recordset.close

Note : Values given above are sample one.

I want the record gets inserted even when it turns from 99 to 100, means code will become ABCTMP99 - ABCTMP100 and continue from series starting with 100 range(3 digits)like 100, 101, 102....

Solution

  • The problem is that the order by in this:

    select top 1 * from prdct_mst_tab 
where pmt_prdct_cd like 'PWATER%' 
and pmt_umt_unit_cd='006' 
AND PMT_CMT_CMPNY_CD='01' 
order by pmt_prdct_cd desc

    Does not do what you expect.

    Try running this in management studio:

    select * from prdct_mst_tab 
where pmt_prdct_cd like 'PWATER%' 
and pmt_umt_unit_cd='006' 
AND PMT_CMT_CMPNY_CD='01' 
order by pmt_prdct_cd desc

    You'll see that the 100 appears before 99 because it is ordering it alphanumerically not numerically.

    In fact you will also see that 10 appears before 9 - how did you ever get past this?

    You have a fundamental design flaw. I will add to that by posing a solution which cements the design flaw in place and introduces new bugs. But it will give you a result.

    One workaround is to do something this:

    select 
MAX(
  CASE 
    WHEN ISNUMERIC(RIGHT(RTRIM(pmt_prdct_cd),3)) = 1 
    THEN RIGHT(RTRIM(pmt_prdct_cd),3)
    ELSE '0' + RIGHT(RTRIM(pmt_prdct_cd),2)
  END
) As LargestNumber
from prdct_mst_tab 
where pmt_prdct_cd like 'PWATER%' 
and pmt_umt_unit_cd='006' 
AND PMT_CMT_CMPNY_CD='01'

    What does this do?

    It checks if the last three characters are a number. If it is it uses it.

    If it isn't a number it grabs the last two characters and puts a zero in front.

    Then it picks the largest number out of all of those.

    note - this returns a number, it doesn't return the full product code. So you'll need to remove the ASP Mid code that tries to pull the number out.

    This might work until you find some other data or case that you haven't mentioned yet. Like for example if there are trailing characters that aren't numeric. Or for when you need a four character number

    Make no mistake - you have a fundamental design flaw and this just prolongs the issue, adds complexity, and introduces more bugs down the track

    Some basic observations:

    • char is a bad data type for this

    • It has concurrency issues - if two requests call this at the same time (easily done from a web app), it returns the same number and they both try and insert a duplicate value

    • You should not be assigning and storing incrementing numbers like this. Just use an IDENTITY in the database.

    I guess since you are using classic ASP, you are not in a situation that you can redesign this.

    You need to decide whether you are going to patch this with something that will introduce new bugs or fix it properly.

    Does each product code really need to be incremented within it's own domain like that? Is there any issue with having ABC01 then DEF02 then XYZ03?