SQL0803N on identity column

Gregory G. V.

SQL0803N on identity column
Hello,
DB2 V8 FP4, got an error
[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 "IP" from having
duplicate rows for those columns. SQLSTATE=23505
Of course I have checked what is that indes "1" and found that it is a
primary key, generated always as identity.
Now I am really do not understand how it could happen, to have such error
on identity column...
Any idea?

Thank you,

--
Gregory G. V.
---------------
Any opinions in this posting are my own and not those of my present
or previous employers.

According Isham Research's Devil's IT Dictionary mainframe is:
"an obsolete device still used by thousands of obsolete companies
serving billions of obsolete customers and making huge obsolete profits
for their obsolete shareholders.
And this year's run twice as fast as last year's."

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ian D. Bjorhovde

Re: SQL0803N on identity column
(in response to Gregory G. V.)
[login to unmask email] wrote:
> Hello,
> DB2 V8 FP4, got an error
> [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 "IP" from having
> duplicate rows for those columns. SQLSTATE=23505
> Of course I have checked what is that indes "1" and found that it is a
> primary key, generated always as identity.
> Now I am really do not understand how it could happen, to have such error
> on identity column...

This can happen in 2 situations:

1) The identity column was altered to start with a number that is below
the maximum value of the column

2) Someone loaded data into the table with the IDENTITYOVERRIDE modifier,
and the values loaded are greater than the current value of the
identity.

Either way, to resolve this you need to alter the identity column and
reset its starting point.

Good luck,


Ian.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm