CODEWITHSUNDEEP
db2foreign-keysconstraintssql-deleteibm-midrange
Harold L. Brown
Harold L. Brown

Reputation: 9966

DB2: Can not delete rows from empty table after it was referenced in foreign key

There is an empty table called ADDRESS.

I perform DELETE FROM ADDRESS and everything is OK.

There is another empty called ADDRESSMAPPING.

Now I add a foreign key constraint to ADDRESSMAPPING which references ADDRESS.

ALTER TABLE ADDRESSMAPPING 
ADD CONSTRAINT FK_ADDRESSMAPPING_ADRESS_ID
FOREIGN KEY (ADDRESS_ID) REFERENCES ADDRESS

When I then perform DELETE FROM ADDRESS the following error occurs:

[55019][-7008] [SQL7008] ADDRESS in MY_SCHEMA not valid for the operation

Both mentioned tables are still empty. If I remove the constraint the DELETE statement terminates again properly.

Why does this error occur? And how can I fix it?

Upvotes: 2

Views: 414

Answers (1)

Łukasz Rzeszotarski
Łukasz Rzeszotarski

Reputation: 6140

Described problem occurs because the tables are not journaled. Adding journaling to the tables should solve the problem.

Upvotes: 2

Related Questions