I'd like to insert record into GROUP table, get autogenerated value group_id and then insert record into another table, USER, with this group_id as a foreign key.
Code below works perfect, but since this possibility is not documented, I'm slightly aware. Would you recommend this way or do you know a better way?
DB2 v8.1. If you are going to test this query in Control Center be aware that you need to run commit after this.
I've put DDL below. Thanks a lot !
DDL:
Code below works perfect, but since this possibility is not documented, I'm slightly aware. Would you recommend this way or do you know a better way?
DB2 v8.1. If you are going to test this query in Control Center be aware that you need to run commit after this.
I've put DDL below. Thanks a lot !
Code:
with tmp1 as
(select group_id
from FINAL TABLE (INSERT INTO GROUP (group_name)
VALUES ('test'))),
tmp2 as
(select count(*) as num
from FINAL TABLE (INSERT INTO USER (group_id, user_id)
select group_id, 'test_user' from tmp1)
)
select num from tmp2;
DDL:
Code:
CREATE TABLE "GROUP" (
"GROUP_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"GROUP_NAME" VARCHAR(30) NOT NULL);
ALTER TABLE "GROUP"
ADD PRIMARY KEY
("GROUP_ID");
CREATE TABLE "USER" (
"GROUP_ID" INTEGER NOT NULL ,
"USER_ID" VARCHAR(64) NOT NULL);
ALTER TABLE "USER"
ADD CONSTRAINT "GROUP_ID_FK" FOREIGN KEY
("GROUP_ID")
REFERENCES "GROUP"
("GROUP_ID")
ON DELETE CASCADE
ON UPDATE RESTRICT
ENFORCED
ENABLE QUERY OPTIMIZATION;