Cascading Delete for Recursive Relationship 
Author Message
 Cascading Delete for Recursive Relationship

Sometimes my tables have recursive relationships where the primary key is
also the foreign key.  If I enforced referential integrity, I could use the
cascading deletes which Access offers.  But because it is recursive,
sometime there will be a null value in the recursive field and therefore
enforced referential integrity won't work.

I need to be able to delete the top parent record and delete all related
child records along with it.  Since some of these mdb.s use Access and not
SQL server of the back end I can't use triggers.  Is there a good known way
to do this?

--
George Padvorac



Fri, 31 Aug 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
Could you give an example? If the field in question is part of the
primary key, I don't see how it can be null.

For example, suppose I set up a table with two fields, ItemID (primary
key) and ParentID (foreign key), with referential integrity enforced,
and with cascade deletes. I could enter the following 3 records:
ItemID  ParentID
----------      -------------
A       Null
B       A
C       A

If I delete the first record, all three will be deleted.

 -- Andy

Quote:

>Sometimes my tables have recursive relationships where the primary key is
>also the foreign key.  If I enforced referential integrity, I could use the
>cascading deletes which Access offers.  But because it is recursive,
>sometime there will be a null value in the recursive field and therefore
>enforced referential integrity won't work.

>I need to be able to delete the top parent record and delete all related
>child records along with it.  Since some of these mdb.s use Access and not
>SQL server of the back end I can't use triggers.  Is there a good known way
>to do this?

>--
>George Padvorac




Fri, 31 Aug 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
Ok here's an example:

First of all we are only talking about a single table.

Emp_ID                AutoNumber
Emp_Emp_ID    Long Integer    (the recursive field.  Its like a FK, but with
out referential integrity.)
Emp_Name        Text

Note:  a recursive relationship is where a table links back to itself.  In
this case Aa_ID links to Emp_Emp_ID.

If I enter the first record like this
(1st record)
Emp_ID                = 1
Emp_Emp_ID    = Null
Emp_Name        = "Bob"

(2nd record)
Emp_ID                = 2
Emp_Emp_ID       = 1
Emp_Name           = "Joe"

This uses a single table and shows that Joe is a child record of Bob,
perhaps Bob manages Joe.  Since the top level record can not be a child of
another record, the FK field (Emp_Emp_ID) must be null...  And...
therefore, referential integrity can not be used and neither can the
cascading delete feature of Access.

Any Ideas?

Thanks.

Quote:

>Could you give an example? If the field in question is part of the
>primary key, I don't see how it can be null.

>For example, suppose I set up a table with two fields, ItemID (primary
>key) and ParentID (foreign key), with referential integrity enforced,
>and with cascade deletes. I could enter the following 3 records:
>ItemID ParentID
>---------- -------------
>A Null
>B A
>C A

>If I delete the first record, all three will be deleted.

> -- Andy


>>Sometimes my tables have recursive relationships where the primary key is
>>also the foreign key.  If I enforced referential integrity, I could use
the
>>cascading deletes which Access offers.  But because it is recursive,
>>sometime there will be a null value in the recursive field and therefore
>>enforced referential integrity won't work.

>>I need to be able to delete the top parent record and delete all related
>>child records along with it.  Since some of these mdb.s use Access and not
>>SQL server of the back end I can't use triggers.  Is there a good known
way
>>to do this?

>>--
>>George Padvorac




Sat, 01 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship

George Padvorac schrieb in Nachricht ...

Quote:
>Ok here's an example:

>First of all we are only talking about a single table.

>Emp_ID                AutoNumber
>Emp_Emp_ID    Long Integer    (the recursive field.  Its like a FK, but
with
>out referential integrity.)
>Emp_Name        Text

>Note:  a recursive relationship is where a table links back to itself.  In
>this case Aa_ID links to Emp_Emp_ID.

>If I enter the first record like this
>(1st record)
>Emp_ID                = 1
>Emp_Emp_ID    = Null
>Emp_Name        = "Bob"

>(2nd record)
>Emp_ID                = 2
>Emp_Emp_ID       = 1
>Emp_Name           = "Joe"

>This uses a single table and shows that Joe is a child record of Bob,
>perhaps Bob manages Joe.  Since the top level record can not be a child of
>another record, the FK field (Emp_Emp_ID) must be null...  And...
>therefore, referential integrity can not be used and neither can the
>cascading delete feature of Access.

>Any Ideas?

>Thanks.

.....

Just tried your example, works fine with RI and cascadin deletes! Where is
your problem?

Regards, Juergen



Sat, 01 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship

Quote:
>Since the top level record can not be a child of
>another record, the FK field (Emp_Emp_ID) must be null...  And...
>therefore, referential integrity can not be used and neither can the
>cascading delete feature of Access.

This is a false statement. There is no problem having a non-required
(sometimes null) foreign key field. The example in my original post
exactly covers your situation. You can set up the relationship with
referential integrity and cascade deletes, and it will behave exactly
as you want.

 -- Andy



Sat, 01 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
Well Georges there actually a way to have a cascade delete or update.  In
the Relationship window, you just had another copy of your table and link
the fields to set up the relationship.  The only problem I have encounter
doing this, is that Access doesn't support (AFIAK) recursive queries the way
Oracle does, i.e., it won't go up to the parent level if you have entries in
different level of hierarchy, I'm only able to retrieve information at a
specified level, using as many aliases as there are levels of hierarchy.

HTH

Robert Bolduc

Quote:

>Ok here's an example:

>First of all we are only talking about a single table.

>Emp_ID                AutoNumber
>Emp_Emp_ID    Long Integer    (the recursive field.  Its like a FK, but
with
>out referential integrity.)
>Emp_Name        Text

>Note:  a recursive relationship is where a table links back to itself.  In
>this case Aa_ID links to Emp_Emp_ID.

snip
Quote:
>This uses a single table and shows that Joe is a child record of Bob,
>perhaps Bob manages Joe.  Since the top level record can not be a child of
>another record, the FK field (Emp_Emp_ID) must be null...  And...
>therefore, referential integrity can not be used and neither can the
>cascading delete feature of Access.

>Any Ideas?

>Thanks.



Sat, 01 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
Many companies solve this by making the boss report to
themselves.... thus no column is null. Everyone has a
superior, and then you can enforce RI.  Its not even
inaccurate.... he is his own boss. :-)

Michael


Quote:
> There is a problem.  If the relationship is set to

Referential Integrity =
Quote:
> True, then by definition, a value is required in the FK

column which already
Quote:
> exists in the PK column of the parent table (in this case
its the same
> table), and therefore can not be null since the PK of the
parent table can
> not be null.




Quote:
> >>Since the top level record can not be a child of
> >>another record, the FK field (Emp_Emp_ID) must be
null...  And...
> >>therefore, referential integrity can not be used and
neither can the
> >>cascading delete feature of Access.

> >This is a false statement. There is no problem having a
non-required
> >(sometimes null) foreign key field. The example in my
original post
> >exactly covers your situation. You can set up the
relationship with
> >referential integrity and cascade deletes, and it will
behave exactly
> >as you want.

> > -- Andy



Sun, 02 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
There is a problem.  If the relationship is set to Referential Integrity =
True, then by definition, a value is required in the FK column which already
exists in the PK column of the parent table (in this case its the same
table), and therefore can not be null since the PK of the parent table can
not be null.
Quote:

>>Since the top level record can not be a child of
>>another record, the FK field (Emp_Emp_ID) must be null...  And...
>>therefore, referential integrity can not be used and neither can the
>>cascading delete feature of Access.

>This is a false statement. There is no problem having a non-required
>(sometimes null) foreign key field. The example in my original post
>exactly covers your situation. You can set up the relationship with
>referential integrity and cascade deletes, and it will behave exactly
>as you want.

> -- Andy



Mon, 03 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
I can remember doing some y2k testing on a personal database the CEO had the
boss GOD :)

Oh and GOD had an hourly rate of 1.79769313486232E308. No idea how the
paycheque was written!

Calum

Quote:

>Many companies solve this by making the boss report to
>themselves.... thus no column is null. Everyone has a
>superior, and then you can enforce RI.  Its not even
>inaccurate.... he is his own boss. :-)

>Michael



Mon, 03 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship

Quote:

>There is a problem.  If the relationship is set to Referential Integrity =
>True, then by definition, a value is required in the FK column which
already
>exists in the PK column of the parent table (in this case its the same
>table), and therefore can not be null since the PK of the parent table can
>not be null.

it CAN be null also, just try. At least at me such things works fine


Mon, 03 Sep 2001 03:00:00 GMT  
 Cascading Delete for Recursive Relationship
Hi George,

We seem to be going round and round. I understand that only one table
is being used. The original example I posted was based on this
understanding. Imagine two fields, ItemID and ParentID. ItemID is the
primary key and is related to ParentID as a foreign key. There is no
problem enforcing referential integrity and having a record with a
null ParentID. Primary key fields are required to be non-null, but not
foreign key fields. What am I missing?

 -- Andy

Quote:

>There is a problem.  If the relationship is set to Referential Integrity =
>True, then by definition, a value is required in the FK column which already
>exists in the PK column of the parent table (in this case its the same
>table), and therefore can not be null since the PK of the parent table can
>not be null.


>>>Since the top level record can not be a child of
>>>another record, the FK field (Emp_Emp_ID) must be null...  And...
>>>therefore, referential integrity can not be used and neither can the
>>>cascading delete feature of Access.

>>This is a false statement. There is no problem having a non-required
>>(sometimes null) foreign key field. The example in my original post
>>exactly covers your situation. You can set up the relationship with
>>referential integrity and cascade deletes, and it will behave exactly
>>as you want.

>> -- Andy



Mon, 03 Sep 2001 03:00:00 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Cascade Delete Relationships with SQL Server

2. rst.delete, with cascade delete???

3. Creating Cascade Update Relationship with VBA

4. Recursive Relationships and Underlaying repeateated sections

5. Controlling Cascading Deletes

6. Acc97: Turning off Cascading Deletes via program code

7. Acc97: Turing off cascading deletes via program code

8. using SQL to set cascade delete

9. Accidental Cascade Delete: Data Recovery Procedures, please

10. Cascading Delete Error - Need Help!

11. Trapping Cascade Delete Error

12. How to Trap Cascade Delete Errors from Jet Engine

 

 
Powered by phpBB® Forum Software