Sorry if this is top-posting. I think that I do need to stick with
GENERATED BY DEFAULT, but something weird is going on here.

I can see from the documentation, that GENERATED BY DEFAULT should
create an ID if I don't supply one:

* Another clause that may be used in creating identity columns is
the |GENERATED BY DEFAULT AS IDENTITY| clause. This will cause DB2
to generate unique values for the identity column during insert
operations if no value is specified for the identity column.
However, if a value is specified for the identity column, DB2 will
use it in the insert operation.


So, does anyone have any idea why this:

INSERT INTO VMC.PARTICIPANTS
(PARTICIPANTS_ID,LASTNAME,FIRSTNAME)
VALUES
(DEFAULT,'Potter','Harry')



would yield this output?

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 "VMC.PARTICIPANTS" from having duplicate values for the index key. SQLSTATE=23505


DDL is:

"PARTICIPANTS_ID" INTEGER NOT NULL GENERATED BY DEFAULT
AS IDENTITY (START WITH 2000, INCREMENT BY 1, NO CACHE,
NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),

DB2 & UDB email list listserv db2-l LazyDBA home page