Cascading Delete for Recursive Relationship
| Author |
Message |
|
George Padvora
#1 / 11
|
 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 |
|
 |
|
Andy Bar
#2 / 11
|
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 |
|
|
|
George Padvora
#3 / 11
|
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 |
|
|
|
Juergen Frielin
#4 / 11
|
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 |
|
|
|
Andy Bar
#5 / 11
|
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 |
|
|
|
Robert Boldu
#6 / 11
|
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 |
|
|
|
Michael Kapla
#7 / 11
|
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 |
|
|
|
George Padvora
#8 / 11
|
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 |
|
|
|
Calum Rea
#9 / 11
|
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 |
|
|
|
Alex Dybenk
#10 / 11
|
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 |
|
|
|
Andy Bar
#11 / 11
|
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 |
|
| |
|
