generated by default as identity

Klp · #1 (**permalink**) 09-23-05, 05:49

I have installed a DB2 v. 7.2

After that I run a sql script ( text file ) to insert some test data.
In the script I declare the pk myself.

When I after that run my app, the pk constraint fails because the DB tryies to insert existing pk.

Can I tell the DB to figure out what the next pk should be or somethin like that

Wim · #2 (**permalink**) 09-23-05, 09:42

If you use this, DB2 genereates the value of the PK for you. Don't supply a value for ID yourself.

Code:

CREATE TABLE xxxx (
	ID	INTEGER		NOT NULL	GENERATED ALWAYS AS IDENTITY,
	field2 CHAR(10),
	CONSTRAINT PK_xxx PRIMARY KEY (ID)
);

INSERT INTO xxx (field2) VALUES('A');
INSERT INTO xxx (field2) VALUES('Z');
INSERT INTO xxx (field2) VALUES('B');
INSERT INTO xxx (field2) VALUES('C');

SELECT * FROM xxx order by field2;

ID	fileld2
1	A
3	B
4	C
2	Z

The ID was not supplied, but generated by DB2.

Internet Services

Web Development

Digital Marketing

Consumer Tech