Duplicate Unique Prime key error in Identity column Table

Database
Enthusiast

Duplicate Unique Prime key error in Identity column Table

Hi,

I'm getting duplicate Prime key error while loading into a Table with Identity column
(which is defined as the UPI)

Since the number is autogenerated i'm not sure why this happens
(I have also defined with the clauses "Gen always as identity and No cycle"

Currently if i simply rerun the job for the same data set it succeeds.

1. This scenario is not consistent (We are facing this error only once in a while, but this error
is not restricted to any one particular Table. We have seen this error in different
Identity column tables)

2. Earlier few months back we faced this issue when we restored Tables from Tape
(That time tables were consistently failing and simply rerunning them was working)
TD ticket was raised and they replied saying the archival might not have happened
properly and we should be not be facing any issues going forward.

Please let me know your comments on why this might be happening/what could be done to
resolve this.

Regards,
Annal T
2 REPLIES

Re: Duplicate Unique Prime key error in Identity column Table

I'm facing exactly the same errors while trying to insert data using custom web application through JDBC. The column is defined "gen by default" though. We didn't restore any tables recently, so I don't think it has anything to do with it.
Enthusiast

Re: Duplicate Unique Prime key error in Identity column Table

You may want to check the max(identity col) value against the avail_value using the following query:

SELECT
dbase.DatabaseName,
tvm.TVMName AS TableName,
tvfields.FieldName AS ColumnName,
tvfields.FieldType AS ColumnType,
tvfields.Nullable,
tvfields.CommentString,
tvfields.TotalDigits(FORMAT 'Z9') AS DecimalTotalDigits,
tvfields.ImpliedPoint(FORMAT 'Z9') AS DecimalFractionalDigits,
tvfields.IdColType,
idcol.AvailValue,
idcol.StartValue,
idcol.MinValue,
idcol.MaxValue,
idcol.Increment,
idcol.Cyc
FROM DBC.TVM
JOIN DBC.tvfields
ON tvm.tvmid = tvfields.tableid
JOIN DBC.Dbase
ON tvm.DatabaseId = dbase.DatabaseId
JOIN dbc.idcol
ON idcol.TableId = tvm.tvmid
AND IdColType IS NOT NULL
and dbase.databasename = 'dbname'
and tvm.tvmname = 'tblname'
order by 1,2,3,4;

if you drop the table and then re-create (preserving the data) without adjusting the starts with in the table definition, the avail_value in the dictionary will start over at 1 setting the stage for a potential dupe. The reason I say 'potential', is because there are gaps in the sequencing and one can get lucky for a period of time.

Another tip... move the identity col to the stage table and then carry it forward from there. The reason for this is a join index cannot be defined on an identity col which is very very limiting if the identity column is a surrogate key.