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 !
- 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:
- 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;
-
