Teradata identity column and "Duplicate unique prime key error in dbname.tablename"

Question

I created a table using the below definition for a Teradata identity column:

      ID INTEGER GENERATED BY DEFAULT AS IDENTITY
           (START WITH 1 
            INCREMENT BY 1 
            MINVALUE 0 
            MAXVALUE 100000000 
            NO CYCLE),
----
      UNIQUE PRIMARY INDEX ( ID )

For several months, the ID column has been working properly, automatically generating a unique value for the column. Over the past month, however, ELMAH has been intermittently reporting the following exception from our .NET 4.0 ASP.NET app:

Teradata.Client.Provider.TdException: [Teradata Database] [2801] Duplicate unique prime key error in DATABASENAME.TABLENAME.

I was able to replicate it by opening SQL Assistant and inserting a bunch of records into the table with raw SQL. As expected, most of the time it would insert successfully, but other times it would throw the above exception.

It appears that this error is occuring because Teradata is trying to generate a value for this column that it has previously generated.

Does anyone have any idea how to get to the bottom of what's happening? At the very least, I'd like some way to debug the issue a bit deeper.

Answer 1

I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE

EDIT:

If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY column. Personal preference would be to use a negative domain.