By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

Cascading delete

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.