|
View previous topic :: View next topic
|
| Author |
Message |
anju subrahmanian
New User
Joined: 11 Jul 2010
Posts: 21
Location: Bangalore
|
|
|
|
Hi,
I am very new to DB2.
I want to delete one record from a particular table say 'A'. Before this obviously I have to delete rows from tables dependent on 'A'.
My question is can we do this in one shot? i.e when i say to delete from table 'A' corresponding rows from dependent tables should also get deleted. Also while defining the constraints between tables 'CASCADE' option is not used. |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Anju,
Its time to use the CASCADE option.
Thanks,
Sushanth |
|
| Back to top |
|
|
anju subrahmanian
New User
Joined: 11 Jul 2010
Posts: 21
Location: Bangalore
|
|
|
|
In fact, I cannot touch the way table is defined.
What I am supposed to do is prepare a jcl to delete a particular row from a table. But I am finding a chain of dependencies for this table which makes my life miserable. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Anju,
Simple, tell your manager this is the easiest & efficient way and it will a lot of programming time. Ask him to raise a DBA request for this.
You don't have to ALTER anything, DBA will take care of it.
Sushanth |
|
| Back to top |
|
|
anju subrahmanian
New User
Joined: 11 Jul 2010
Posts: 21
Location: Bangalore
|
|
|
|
I could not find any useful information so far regarding this. I have to delete rows from each and every depenedent table explicitly.
Thanks for replying......... |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Anju,
You should be looking at DB2 V9 - SQL REFERENCE manual and for definations click here
| Code: |
ALTER TABLE DSN8910.DEPT
FOREIGN KEY(ADMRDEPT) REFERENCES DSN8910.DEPT ON DELETE CASCADE;
|
Thanks,
Sushanth |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| anju subrahmanian wrote: |
| But I am finding a chain of dependencies for this table which makes my life miserable. |
May be only once. After finding the dependent tables, you can prepare standard SQLs to do it next time without having to find out the relations again. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Arun,
If this is done by SQL, whenever an dependent table is created, now or later.
Developer has to remember to add delete statements for this new table also, when deleting from parent table. Otherwise you will be finding a lot of orphan rows.
If you are going to use DELETE statements, you can also have an option of using TRIGGERs.
Thanks,
Sushanth |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| sushanth bobby wrote: |
| Developer has to remember to add delete statements for this new table also, when deleting from parent table. |
Not if properly documented. |
|
| Back to top |
|
|
anju subrahmanian
New User
Joined: 11 Jul 2010
Posts: 21
Location: Bangalore
|
|
|
|
Sushanth, I understood what you suggested, but I was not in a position to do such things..........
Thanks for the support |
|
| Back to top |
|
|
|
|
