DB2 SQL error -104 when creating foreign key

Question

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

when trying to create 2 temporary tables with 1 foreign key, as follows:

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;

If I remove the

constraint

part, it executes fine. I've tried both

references session.company (id_comp)

and

references session.company.id_comp

, with the same result.

How can I solve this?

Thanks in advance.

UPDATE:

It might be considered a different question but, as it was suggested that I generate

id_comp

as a PRIMARY_KEY
in

session.company

, I can't get it work this way neither.

I tried to create a table with a

PRIMARY KEY

in a new script with that simple table (as you can see, I tried to do it with the

primary key

constraint after a comma:

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;

Also tried all this different options:

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)

and none of them works, all return `Error Code: -104'.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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
    ) ;

DB2 SQL error -104 when creating foreign key

Sign up