By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

SQLSTATE: 23505 UDB DB2 8.1.5

P: n/a
Given the DDL:

CREATE TABLE JOHNTEST.TASK_LIST (
TASK BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
PRI CHAR(1) NOT NULL,
SUBP DECIMAL(7,4),
ATO VARCHAR(25),
REASON VARCHAR(25),
DESCRIPTION VARCHAR(2000),
FILES varchar(500),
TOLD CHAR(1),
last_changed timestamp not null default current_timestamp
)

CREATE INDEX JOHNTEST.tl_primary
ON JOHNTEST.TASK_LIST(TASK)

Alter TABLE JOHNTEST.TASK_LIST add constraint primary
PRIMARY KEY(TASK)

Why does the sql statement:

INSERT INTO JOHNTEST.TASK_LIST(TASK, "ATO", "PRI", "SUBP", "DESCRIPTION")
VALUES(default,'Bob', 'z', 999.999, 'testing')

caues the error message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC:
1;JOHNTEST.TASK_LIST
Message: 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 "JOHNTEST.TASK_LIST" from having duplicate rows for
those columns. 

I thought my primary key would be generated every time, uniquely
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Bob Stearns wrote:
....
I thought my primary key would be generated every time, uniquely


I cannot recreate this on my V8.2 FP9 database; how did you populated this
table? Only by using quoted INSERT or did you use LOAD?

Jan M. Nelken
Nov 12 '05 #2

P: n/a
Jan M. Nelken wrote:
Bob Stearns wrote:
...
I thought my primary key would be generated every time, uniquely

I cannot recreate this on my V8.2 FP9 database; how did you populated
this table? Only by using quoted INSERT or did you use LOAD?

Jan M. Nelken


Good guess. My colleague was using a set of insert statements generated
by an export, so that the rows had the task value. All I had to do was
set START WITH greater than the largest inserted row.
Nov 12 '05 #3

P: n/a
Bob Stearns wrote:
Given the DDL:

CREATE TABLE JOHNTEST.TASK_LIST (
TASK BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
PRI CHAR(1) NOT NULL,
SUBP DECIMAL(7,4),
ATO VARCHAR(25),
REASON VARCHAR(25),
DESCRIPTION VARCHAR(2000),
FILES varchar(500),
TOLD CHAR(1),
last_changed timestamp not null default current_timestamp
)

CREATE INDEX JOHNTEST.tl_primary
ON JOHNTEST.TASK_LIST(TASK)

Alter TABLE JOHNTEST.TASK_LIST add constraint primary
PRIMARY KEY(TASK)

Why does the sql statement:

INSERT INTO JOHNTEST.TASK_LIST(TASK, "ATO", "PRI", "SUBP", "DESCRIPTION")
VALUES(default,'Bob', 'z', 999.999, 'testing')

caues the error message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC:
1;JOHNTEST.TASK_LIST
Message: 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 "JOHNTEST.TASK_LIST" from having duplicate rows for
those columns. 

I thought my primary key would be generated every time, uniquely

There are a couple of options:
1. At some point an insert was doen providing a value instead of letting
DB2 do it. This value is now cauing a collision.
2. A LOAD was performed which provided the values (OVERRIDE).

Given the flexibility (CYCLE, RESTART, flip INCREMENT, ...) of IDENTITY
DB2 cannot automagically synch up.

Here is a handy proc which you can use to "synch up". The procedure
assumes positive increments:

CREATE PROCEDURE GOODSTUFF.SYNCIDENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."'
|| tablename || '" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
COMMIT;
END
$
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
Serge Rielau wrote:
Bob Stearns wrote:
Given the DDL:

CREATE TABLE JOHNTEST.TASK_LIST (
TASK BIGINT GENERATED BY DEFAULT AS IDENTITY
(START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
PRI CHAR(1) NOT NULL,
SUBP DECIMAL(7,4),
ATO VARCHAR(25),
REASON VARCHAR(25),
DESCRIPTION VARCHAR(2000),
FILES varchar(500),
TOLD CHAR(1),
last_changed timestamp not null default current_timestamp
)

CREATE INDEX JOHNTEST.tl_primary
ON JOHNTEST.TASK_LIST(TASK)

Alter TABLE JOHNTEST.TASK_LIST add constraint primary
PRIMARY KEY(TASK)

Why does the sql statement:

INSERT INTO JOHNTEST.TASK_LIST(TASK, "ATO", "PRI", "SUBP", "DESCRIPTION")
VALUES(default,'Bob', 'z', 999.999, 'testing')

caues the error message:

DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC:
1;JOHNTEST.TASK_LIST
Message: 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 "JOHNTEST.TASK_LIST" from having
duplicate rows for those columns. 

I thought my primary key would be generated every time, uniquely


There are a couple of options:
1. At some point an insert was doen providing a value instead of letting
DB2 do it. This value is now cauing a collision.
2. A LOAD was performed which provided the values (OVERRIDE).

Given the flexibility (CYCLE, RESTART, flip INCREMENT, ...) of IDENTITY
DB2 cannot automagically synch up.

Here is a handy proc which you can use to "synch up". The procedure
assumes positive increments:

CREATE PROCEDURE GOODSTUFF.SYNCIDENTITY(IN schemaname VARCHAR(128),
IN tablename VARCHAR(128))
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE s STATEMENT;
DECLARE cur CURSOR FOR s;

SELECT colname INTO idcolname
FROM SYSCAT.COLUMNS
WHERE tabname = tablename
AND tabschema = schemaname
AND identity = 'Y';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'can''t find identity column';
END IF;
SET stmttxt = 'SELECT MAX("' || idcolname || '") FROM "' ||
schemaname || '"."' || tablename || '"';
PREPARE s FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || schemaname || '"."'
|| tablename || '" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
COMMIT;
END
$

Thanks a lot. I don't use identity keys often (3 times in two years) but
this is a handy tool for when I make a mistake.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.