Cascading Referential Integrity Constraints

In a master-detail one-to-many relationship, I have the foreign key set to '
allow null'. I would like, in this particular case, to automatically have th
e foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will al
ways delete the detail/many records when the master/one record is deleted. I
f the foreign key is nullable, would it not make sense to null it, and if it
is not nullable to delete
the detail/many records?
Is there any efficient way to set a table up so that foreign keys are automa
tically nulled when the primary key record is deleted?That functionality won't be available until the next release of SQL Server
(Yukon). Meanwhile, you will have to handle RI through triggers in that
case. This link may be useful:
http://msdn.microsoft.com/library/d...efintegrity.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:025F9D33-F01D-43AA-BF12-09C50AAD4670@.microsoft.com...
In a master-detail one-to-many relationship, I have the foreign key set to
'allow null'. I would like, in this particular case, to automatically have
the foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will
always delete the detail/many records when the master/one record is deleted.
If the foreign key is nullable, would it not make sense to null it, and if
it is not nullable to delete the detail/many records?
Is there any efficient way to set a table up so that foreign keys are
automatically nulled when the primary key record is deleted?