Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004

    Unanswered: Duplicate error inserting default into identity column

    We have a table with a single unique index. The index is implicitly created by the primary key, which is on an integer identity column. Every so often an application gets the following error during an insert

    ERROR [23505] [IBM][CLI Driver][DB2/LINUX] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "MYUSER.MYTABLE" from having duplicate rows for those columns. SQLSTATE=23505

    The insert statement specifies DEFAULT for the identity column.

    The application is not thrashing the system and the database server is very lightly loaded. On retrying the statement it generally works fine.

  2. #2
    Join Date
    Aug 2004
    if your Mytab table has structure:

    (id -- identity column,
    name )

    use insert statement as

    insert into mytab(name) values ('aaa')

  3. #3
    Join Date
    Jul 2004
    Ottawa, Canada
    Is your column GENERATED BY DEFAULT? That column type does not guarentee a unique number especially if there are gaps in the numbers...i.e deleted rows.

  4. #4
    Join Date
    Aug 2004
    The column is defined to allow us to insert specific values, but in fact we never do in this table (after the initial set up). Data is never deleted from the table in question.

    My point is that the same insert statement sometimes fails, then simply retrying causes it to work. My initial thought was that two threads were hiting the table at the same instant. However, there is very little activity on the table and it keeps happening.

    If there were a value in the table higher than the current identity column value, when we got to that value then the insert statement would always fail at that point.

    I remain bemused!


  5. #5
    Join Date
    Dec 2003


    Hi ,
    If you specify GENERATED ALWAYS, this problem will not come, but the draw back would be u cant insert directly into the field with identity value. U need to do a load replace.

    If u have replication, GENERATED ALWAYS will not work.

    In GENERATED BY DEFAULT both inserts with values and replication will work. But my doubt is if u dont give default and do the insert, it would take the next value automatically. Please try this, remove the default and remove the identity column name.

    It will start putting identity values from database.

    Let me know if it solved ur problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts