Cascading delete

Mahesh S

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

Knut Stolze

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

Mahesh S

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

Knut Stolze

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