Hi friends i need advise on delete routine. say i've a parent table which has 1 child. i've 2 columns in child table that reference (FKey) to same parent table. from our business point of view, if a parent record (the record referring to say child.fld1) is deleted i want child record is set to null. if a parent record (the record referring to say child.fld2) is deleted i want child record to be deleted(i.e. cascade).
as you all know we can set rules when defining a fireign key so i tried to set one to ON DELETE CASCADE and other one to ON DELETE SET NULL
but sql server wont let me do this and it gives following error "Introducing FOREIGN KEY constraint 'bl..blahh' on table 'child' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."
my question is how do you handle this situation ? you dont create a child table that has 2 or more columns referencing same parent table ?
your design does not work, simply because if you delete a record you can't perform two different actions on the same child record, a delete is a delete, so you can either cascade the delete, or set the child to null, you can't do both since the net result would be a cascaded delete.
Topic Locked
Posted - 14 August 2006 : 10:18:22
