[GENERAL] deleting the master but not the detail

2008-07-17 Thread Ismael ....
hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched But the delete command doesn't let me delete the master as long as there are details referencing it. ON DELETE RESTRICT | NO ACTION won't let me delete the master

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread A. Kretschmer
am Thu, dem 17.07.2008, um 11:11:00 -0500 mailte Ismael folgendes: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched But the delete command doesn't let me delete the master as long as there are details

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Raymond O'Donnell
On 17/07/2008 17:11, Ismael wrote: ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Can you just drop the constraint that's doing the referential integrity? Ray.

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Ismael ....
So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Because I *still* need to verify that NEW records in the details table direct to something that exists ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE |

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Douglas McNaught
On Thu, Jul 17, 2008 at 12:11 PM, Ismael [EMAIL PROTECTED] wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Scott Marlowe
On Thu, Jul 17, 2008 at 10:52 AM, Douglas McNaught [EMAIL PROTECTED] wrote: On Thu, Jul 17, 2008 at 12:11 PM, Ismael [EMAIL PROTECTED] wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Ismael ....
It seems like 3 vs 1 so you win :) I'll drop the constraint and verify the integrity of the new records manually tanks On Thu, Jul 17, 2008 at 10:52 AM, Douglas McNaught wrote: On Thu, Jul 17, 2008 at 12:11 PM, Ismael wrote: hi I have one of those master-detail relationships here

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Webb Sprague
hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched when you create the table with an FK constraint, use the ON DELETE SET NULL option, or SET DEFAULT. And read the docs on CREATE TABLE:

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Stephan Szabo
On Thu, 17 Jul 2008, Ismael wrote: So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Ismael ....
So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but making it only prevent the deletions