Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

DELETE with CASCADE -- is it possible with FK constraints in place?

0 views
Skip to first unread message

C

unread,
Nov 6, 1996, 3:00:00 AM11/6/96
to

DELETE with CASCADE -- is it possible with FK constraints in place?

I have tables 'Parent' and 'Child' where 'Child' has a foreign key
reference to 'Parent'. I want to allow deletes on 'Parent', and if there
exists children rows in 'Child', delete them too. I've created a trigger on
'Parent' to delete all children, but the delete command on 'Parent' fails
due to the FK constraint before the trigger has the opportunity to execute.

I see a single reference to 'delcascadetrig' in the SQL Server 6.5
documentation, under the "Nester Triggers" topic, but there's no
elaboration on this. Can someone out there suggest the best approach for
this? I'm sure this is a common occurrence. I don't think I want to
sacrifice the foreign keys.

Thank you.

--
Chris Little
Value America
Ph: 804.970.7880 Fax: 804.970.1981
cli...@valueamerica.com
http://www.valueamerica.com/


James J. Raden

unread,
Nov 7, 1996, 3:00:00 AM11/7/96
to

C wrote:
>
> DELETE with CASCADE -- is it possible with FK constraints in place?
>
> Thank you.
>
> --


DRI doesn't work with cascaded deletes or updates. :{

Oracle provides a pre-delete trigger which works wonders, I'm told, but
SQL Server has only post-delete triggers, which are prevented from
occurring by DRI.

See Dejanews for past threads on this topic.

- Jim Raden

Ned Otter

unread,
Nov 7, 1996, 3:00:00 AM11/7/96
to

Hi Chris,

The answer to your question is NO - it is NOT possible with FK
constraints in place.

'delcascadetrig' - must work on a table without FK constraints.

Regards,

Ned Otter
Softech Data Inc.


--------------------------


DELETE with CASCADE -- is it possible with FK constraints in place?

I have tables 'Parent' and 'Child' where 'Child' has a foreign key

Jeremy H. Griffith

unread,
Nov 8, 1996, 3:00:00 AM11/8/96
to

On 6 Nov 96 15:23:05 GMT, "C" <cli...@valueamerica.com> wrote:

>DELETE with CASCADE -- is it possible with FK constraints in place?
>
>I have tables 'Parent' and 'Child' where 'Child' has a foreign key
>reference to 'Parent'. I want to allow deletes on 'Parent', and if there
>exists children rows in 'Child', delete them too. I've created a trigger on
>'Parent' to delete all children, but the delete command on 'Parent' fails
>due to the FK constraint before the trigger has the opportunity to execute.
>
>I see a single reference to 'delcascadetrig' in the SQL Server 6.5
>documentation, under the "Nester Triggers" topic, but there's no
>elaboration on this. Can someone out there suggest the best approach for
>this? I'm sure this is a common occurrence. I don't think I want to
>sacrifice the foreign keys.

This is practically a FAQ. It's simple; if you want to cascade delete
with triggers, you cannot use the DRI. (You would then need to check
the FK's on insert in the child table with a trigger too.) The other
way is with a stored procedure, which deletes all the child table items
before the parent table item; then you *can* use DRI to prevent inserts
(in the child table) with an incorrect FK. It's a matter of taste.

For more info, read this Microsoft KB article:
INF: Cascading Deletes and Updates of Primary Keys [sqlserver]
ID: Q142480 CREATED: 15-JAN-1996 MODIFIED: 16-JAN-1996

If you can't find it on your MSDN or TechNet CD, I'll mail it to you [long].

--Jeremy

Patrick J. Cuff Jr.

unread,
Nov 8, 1996, 3:00:00 AM11/8/96
to

"C" <cli...@valueamerica.com> wrote:

>DELETE with CASCADE -- is it possible with FK constraints in place?

>I have tables 'Parent' and 'Child' where 'Child' has a foreign key
>reference to 'Parent'. I want to allow deletes on 'Parent', and if there
>exists children rows in 'Child', delete them too. I've created a trigger on
>'Parent' to delete all children, but the delete command on 'Parent' fails
>due to the FK constraint before the trigger has the opportunity to execute.

>I see a single reference to 'delcascadetrig' in the SQL Server 6.5
>documentation, under the "Nester Triggers" topic, but there's no
>elaboration on this. Can someone out there suggest the best approach for
>this? I'm sure this is a common occurrence. I don't think I want to
>sacrifice the foreign keys.

>Thank you.

>--
>Chris Little
>Value America
>Ph: 804.970.7880 Fax: 804.970.1981
>cli...@valueamerica.com
>http://www.valueamerica.com/

Put the foreign keys on the alter...

The foreign key constraint fires before the trigger, so if you want to
implement cascading deletes you have to either:
1. Enforce the foreign key constraint with an Insert/Update trigger, and
write a Cascade Delete trigger, or
2. Perform the Cascade Delete via stored procedures (delete child rows first,
then parent). The "advantage" to this approach is that you can keep your
foreign key constraints.

To the best of my knowledge, MS has no plans of adding Delete Cascade to DRI
in the near future...

Good luck,

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

___________________________________________________________
//)| |
/ / | The opinions expressed here are my own and do not |
_( (_ | represent Shared Medical Systems Corporation. |
(((\ \>|_/()_______________________________________________________|
(\\\\ \_/ /
\ / Patrick J. Cuff Jr.
\ _/ Senior Analyst, People and Resource Systems
/ / SMS Inc., Malvern, PA
/___/ http://www.smed.com
mailto: PAT....@SMED.COM

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

0 new messages