467,189 Members | 1,280 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,189 developers. It's quick & easy.

Cascading delete

Hi

I have 3 tables in db2 database. I would like to write a cascading
delete statement and I dont want to use a trigger. Could you please
explain how the statement needs to be?

My table straucture is as follows:

Table1
---ID (PK)
---Field 1
---Field 2

Table 2
---ID (FK) referencing Table1.ID
---Field 3
---Field 4

Table 3
---ID (FK) referencing Table1.ID
---Field 5
---Field 6

What I would like to have is, when I perform a delete on Table 1, I
would like the corresponding related records in table2 and table 3 to
be deleted as well.

Thanks for the help in advance.

Cheers
Mahesh

May 10 '06 #1
  • viewed: 1741
Share:
3 Replies
Mahesh S wrote:
Hi

I have 3 tables in db2 database. I would like to write a cascading
delete statement and I dont want to use a trigger. Could you please
explain how the statement needs to be?

My table straucture is as follows:

Table1
---ID (PK)
---Field 1
---Field 2

Table 2
---ID (FK) referencing Table1.ID
---Field 3
---Field 4

Table 3
---ID (FK) referencing Table1.ID
---Field 5
---Field 6

What I would like to have is, when I perform a delete on Table 1, I
would like the corresponding related records in table2 and table 3 to
be deleted as well.


DELETE FROM table1
WHERE ...
You don't have to do anything else. Of course, the referential actions
specified for the foreign key constraints have to be defined with the
clause ON DELETE CASCADE.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
May 10 '06 #2
Hi Knut

I actually didnt specify the clause for delete. Only did it for
update. Is there any way to get around it?

Thanks
Mahesh

May 10 '06 #3
Mahesh S wrote:
Hi Knut

I actually didnt specify the clause for delete. Only did it for
update. Is there any way to get around it?


Drop the constraint and add it again with the required definition:

ALTER TABLE ... DROP CONSTRAINT ...
ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
May 10 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Doug Serie | last post: by
7 posts views Thread by CheGueVerra | last post: by
1 post views Thread by JMosey@gmail.com | last post: by
6 posts views Thread by visionstate@googlemail.com | last post: by
1 post views Thread by cesar.guinovart@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.