I'm trying to create temporary tables to do some testing and I'm facing this error:
[DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=key;(
id int primary;<references_spec>, DRIVER=3.50.152
declare global temporary table session.company (
id_comp int not null generated always as identity (start with 0 increment by 1),
name_comp varchar(60)
) with replace on commit preserve rows not logged;
delete from session.company;
declare global temporary table session.employee (
id_emp int not null generated always as identity (start with 0 increment by 1),
name varchar(40),
id_comp int,
constraint fk_id_comp
foreign key (id_comp)
references session.company (id_comp)
on delete cascade
) with replace on commit preserve rows not logged;
delete from session.employee;
constraint
references session.company (id_comp)
references session.company.id_comp
id_comp
PRIMARY_KEY
session.company
PRIMARY KEY
primary key
declare global temporary table session.t1 (
id int primary key generated always as identity (start with 0 increment by 1) -- ,
-- primary key (id)
) with replace on commit preserve rows not logged;
primary key id int generated always as identity (start with 0 increment by 1)
---
primary key (id) int
---
primary key id int
---
id int,
primary key (id)
If your Db2 server runs on Linux/Unix/Windows, then a DGTT cannot participate in declarative RI. See the documentation at this link specifically this text:
Restrictions on the use of declared temporary tables: Declared temporary tables cannot:.. Be specified in referential constraints (SQLSTATE 42995).
You can use programmed RI however (that is to say, manually enforce the integrity by using set null or delete as appropriate for your data model and business). This means your code must populate both tables and then program the RI checks and resulting actions (to set null or delete rows) accordingly using plain SQL.
You should explain clearly why you don't want persistent tables so that the motivation is known - you then may get a better solution.
You can use persistent tables with the 'not logged ' characteristic at transaction level, but this is a bad idea because you then have to drop/recreate persistent tables after any Db2 error.
If you do not need DGTT (session tables) and if you are happy to use persistent tables then the example syntax below is valid for Db2 current versions on LUW:
create table company (
id_comp int not null generated always as identity (start with 0 increment by 1) constraint pk1 primary key,
name_comp varchar(60)
) ;
create table xemployee (
id_emp int not null generated always as identity (start with 0 increment by 1),
name varchar(40),
id_comp int,
constraint fk_id_comp
foreign key (id_comp)
references company (id_comp)
on delete cascade
) ;