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...
>
>

Parent Child index?

I have a table that has recursive relationship with itself. The primary key
is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier). I
would like to create an index for these two fields, including cascading
deletes (regardless of the number of decendants). Is this possible? If so,
can someone step me thru it?
Thanks,
Craig BuchananHi
You can't create a FK with cascading deletes when the FK refers to the same
table. If you implemented this with a trigger you would be restricted to 32
levels. What you may want to do is to run a batch process that clears up the
table.
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message
news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> I have a table that has recursive relationship with itself. The primary
key
> is Id (uniqueidentifier), the foreign key is ParentId (uniqueidentifier).
I
> would like to create an index for these two fields, including cascading
> deletes (regardless of the number of decendants). Is this possible? If
so,
> can someone step me thru it?
> Thanks,
> Craig Buchanan
>|||John-
How would I create a trigger between two tables where PK and FK are
uniqueidentifiers?
Thanks,
Craig
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> Hi
> You can't create a FK with cascading deletes when the FK refers to the
same
> table. If you implemented this with a trigger you would be restricted to
32
> levels. What you may want to do is to run a batch process that clears up
the
> table.
> John
> "Craig Buchanan" <someone@.microsoft.com> wrote in message
> news:%23sUfYcbDEHA.1544@.TK2MSFTNGP09.phx.gbl...
> key
(uniqueidentifier).
> I
> so,
>|||Hi Craig
Something like:
CREATE TABLE ParentChildTable ( id int not null CONSTRAINT
PK_ParentChild PRIMARY KEY,
parentid int not null )
DROP TABLE ParentChildTable
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
SELECT * FROM deleted
DELETE FROM ParentChildTable
FROM ParentChildTable P JOIN DELETED D ON P.Parentid = d.id
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
sp_configure 'nested triggers'
/*
Needs to be 1
name minimum maximum
config_value run_value
-- -- --
-- --
nested triggers 0 1 1
1
*/
sp_dboption testdb, 'recursive triggers'
/*
Needs to be ON
OptionName CurrentSetting
-- --
recursive triggers ON
*/
DELETE FROM ParentChildTable WHERE id = 3
/* id parentid
-- --
3 2
(1 row(s) affected)
id parentid
-- --
4 3
(1 row(s) affected)
id parentid
-- --
5 4
(1 row(s) affected)
id parentid
-- --
6 5
(1 row(s) affected)
id parentid
-- --
7 6
(1 row(s) affected)
id parentid
-- --
8 7
(1 row(s) affected)
id parentid
-- --
9 8
(1 row(s) affected)
id parentid
-- --
10 9
(1 row(s) affected)
id parentid
-- --
11 10
(1 row(s) affected)
id parentid
-- --
12 11
(1 row(s) affected)
id parentid
-- --
13 12
(1 row(s) affected)
id parentid
-- --
14 13
(1 row(s) affected)
id parentid
-- --
15 14
(1 row(s) affected)
id parentid
-- --
16 15
(1 row(s) affected)
id parentid
-- --
17 16
(1 row(s) affected)
id parentid
-- --
18 17
(1 row(s) affected)
id parentid
-- --
19 18
(1 row(s) affected)
id parentid
-- --
20 19
(1 row(s) affected)
id parentid
-- --
21 20
(1 row(s) affected)
id parentid
-- --
22 21
(1 row(s) affected)
id parentid
-- --
23 22
(1 row(s) affected)
id parentid
-- --
24 23
(1 row(s) affected)
id parentid
-- --
25 24
(1 row(s) affected)
id parentid
-- --
26 25
(1 row(s) affected)
id parentid
-- --
27 26
(1 row(s) affected)
id parentid
-- --
28 27
(1 row(s) affected)
id parentid
-- --
29 28
(1 row(s) affected)
id parentid
-- --
30 29
(1 row(s) affected)
id parentid
-- --
31 30
(1 row(s) affected)
id parentid
-- --
32 31
(1 row(s) affected)
id parentid
-- --
33 32
(1 row(s) affected)
id parentid
-- --
34 33
(1 row(s) affected)
Server: Msg 217, Level 16, State 1, Procedure TRG_ParentChildTable,
Line 7
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
*/
John
"Craig Buchanan" <someone@.microsoft.com> wrote in message news:<eOG$Y6BEEHA.2404@.TK2MSFTNGP
11.phx.gbl>...
> John-
> How would I create a trigger between two tables where PK and FK are
> uniqueidentifiers?
> Thanks,
> Craig
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:A7F6c.20706$tN7.466483449@.news-text.cableinet.net...
> same
> 32
> the
> key
> (uniqueidentifier).
> I
> so,|||Hi Craig
This may be an alternative, but if it may cause contention on this table and
you may be better off running a scheduled task to do the deletions in a qui
et period.
sp_dboption testdb, 'recursive triggers', false
sp_dboption testdb, 'recursive triggers'
/*
Needs to be off
OptionName CurrentSetting
-- --
recursive triggers off
*/
DROP TRIGGER TRG_ParentChildTable
CREATE TRIGGER TRG_ParentChildTable ON ParentChildTable
FOR DELETE
AS
BEGIN
WHILE @.@.ROWCOUNT > 0
DELETE FROM ParentChildTable
FROM ParentChildTable
WHERE ParentId <> 0
AND ParentId NOT IN ( SELECT Id FROM ParentChildTable )
END
TRUNCATE TABLE ParentChildTable
DECLARE @.id int
SET @.id = 1
WHILE @.id < 41
BEGIN
INSERT INTO ParentChildTable ( id , parentid ) VALUES ( @.id , @.id - 1
)
SET @.id = @.id + 1
END
SELECT * FROM ParentChildTable
DELETE FROM ParentChildTable WHERE Id = 3
SELECT * FROM ParentChildTable
John