Parent-Child relationship

I have a field which is the primary key in table A, and in table B i have th
e
same field which is the foreign key.When it comes to deleting a record I
should kill the child first.If i try to delete the parent then i get an
error, which is expected. But if i turn the delete cascade option on, it let
s
me delete the parent first without giving an error.Why?
Thanks in advance.On Tue, 15 Feb 2005 08:21:11 -0800, PH wrote:

>I have a field which is the primary key in table A, and in table B i have t
he
>same field which is the foreign key.When it comes to deleting a record I
>should kill the child first.If i try to delete the parent then i get an
>error, which is expected. But if i turn the delete cascade option on, it le
ts
>me delete the parent first without giving an error.Why?
>Thanks in advance.
Hi PH,
Because the on delete cascade options tells the SQL Server engine that it
should automatically delete all "orphaned" childs when you delete a
parent. After SQL Server has done that, there are no more rows violating
the foreign key constraint, so there's no reason to give you an error.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What did you think that cascade would do? The error is not there because
it deleted the child first, then deleted the parent.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"PH" <PH@.discussions.microsoft.com> wrote in message
news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>I have a field which is the primary key in table A, and in table B i have
>the
> same field which is the foreign key.When it comes to deleting a record I
> should kill the child first.If i try to delete the parent then i get an
> error, which is expected. But if i turn the delete cascade option on, it
> lets
> me delete the parent first without giving an error.Why?
> Thanks in advance.|||Only use cascade delete when you are always sure that it never makes sense
to have a child when the parent is deleted. There are cases where it makes
sense for a child to have a null in the FK column, usually this is when
there are multiple Fk's in the child table, BUT, the is usually always ONE
Master table, as in Invoice and InvoiceItem when you must decide to either
cascade delete the InvoiceItem when and Invoice is deleted or to DENY
deleting and Invoice if it has InvoiceItems. This is a business rule that
you must decide on.
JIM
"PH" <PH@.discussions.microsoft.com> wrote in message
news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>I have a field which is the primary key in table A, and in table B i have
>the
> same field which is the foreign key.When it comes to deleting a record I
> should kill the child first.If i try to delete the parent then i get an
> error, which is expected. But if i turn the delete cascade option on, it
> lets
> me delete the parent first without giving an error.Why?
> Thanks in advance.|||Thanks for your answers
"james" wrote:

> Only use cascade delete when you are always sure that it never makes sense
> to have a child when the parent is deleted. There are cases where it make
s
> sense for a child to have a null in the FK column, usually this is when
> there are multiple Fk's in the child table, BUT, the is usually always ONE
> Master table, as in Invoice and InvoiceItem when you must decide to either
> cascade delete the InvoiceItem when and Invoice is deleted or to DENY
> deleting and Invoice if it has InvoiceItems. This is a business rule that
> you must decide on.
> JIM
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>
>