INF: Cascade-to-Null relations are supported in JET 4

**John Mishefske** · Jul 15 '06, 02:35 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

Allen Browne wrote:

In Access 2000 and later, you can create a relation between tables where
related records can be automatically set to Null rather than deleted when
the primary record is deleted.
>
I have not seen anyone using or discussing this feature, so there is a new
article explaining the concept here:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

Nice. Thanks for the great explanation. Any downsides?

--
'---------------
'John Mishefske
'---------------

**Allen Browne** · Jul 15 '06, 02:55 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

No downsides, John.

I've been using this in real-world apps for more than 12 months now.

The only issue is the posssibility that the database is not rebuilt properly
by someone else, so the article addresses that maintenance question.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Mishefske" <jmishefskeNO@S PAMyahoo.comwro te in message
news:tyYtg.80$u 11.10@tornado.r dc-kc.rr.com...

Allen Browne wrote:

>In Access 2000 and later, you can create a relation between tables where
>related records can be automatically set to Null rather than deleted when
>the primary record is deleted.
>>
>I have not seen anyone using or discussing this feature, so there is a
>new article explaining the concept here:
> Cascade to Null Relations
>at:
> http://allenbrowne.com/ser-64.html

>
>
Nice. Thanks for the great explanation. Any downsides?
>
--
'---------------
'John Mishefske
'---------------

**RoyVidar** · Jul 15 '06, 07:05 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

In Access 2000 and later, you can create a relation between tables

where related records can be automatically set to Null rather than
deleted when the primary record is deleted.
>
I have not seen anyone using or discussing this feature, so there is
a new article explaining the concept here:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

It's also documented in the Jet SQL reference, see for instance the
help file on the constraint clause:

"Syntax
Single-field constraint:

CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]}"

So, in your sample, you could fire it like any other Jet DDL through
ADO, too

Sub MakeRelJetADO()
Dim strSql As String
strSql = "ALTER TABLE tblProduct ADD CONSTRAINT FK_ProdCat " & _
"FOREIGN KEY (CategoryID) REFERENCES " & _
"tblCategor y (CategoryID) ON DELETE SET NULL"
CurrentProject. Connection.Exec ute strSql, , _
adExecuteNoReco rds + adCmdText
End Sub

--
Roy-Vidar

**Allen Browne** · Jul 15 '06, 09:35 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

Thanks, Roy.

Yes, I should have included the DDL approach as well as the DAO and ADOX.
Done.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RoyVidar" <roy_vidarNOSPA M@yahoo.nowrote in message
news:mn.7a147d6 79d1f3cfc.57374 @yahoo.no...

>In Access 2000 and later, you can create a relation between tables where
>related records can be automatically set to Null rather than deleted when
>the primary record is deleted.
>>
>I have not seen anyone using or discussing this feature, so there is a
>new article explaining the concept here:
> Cascade to Null Relations
>at:
> http://allenbrowne.com/ser-64.html

>
It's also documented in the Jet SQL reference, see for instance the
help file on the constraint clause:
>
"Syntax
Single-field constraint:
>
CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]
[ON UPDATE CASCADE | SET NULL]
[ON DELETE CASCADE | SET NULL]}"
>
So, in your sample, you could fire it like any other Jet DDL through
ADO, too
>
Sub MakeRelJetADO()
Dim strSql As String
strSql = "ALTER TABLE tblProduct ADD CONSTRAINT FK_ProdCat " & _
"FOREIGN KEY (CategoryID) REFERENCES " & _
"tblCategor y (CategoryID) ON DELETE SET NULL"
CurrentProject. Connection.Exec ute strSql, , _
adExecuteNoReco rds + adCmdText
End Sub

**Rick Brandt** · Jul 15 '06, 11:45 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

John Mishefske wrote:

Allen Browne wrote:

In Access 2000 and later, you can create a relation between tables
where related records can be automatically set to Null rather than
deleted when the primary record is deleted.

I have not seen anyone using or discussing this feature, so there
is a new article explaining the concept here:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

>
>
Nice. Thanks for the great explanation. Any downsides?

The database on our AS400 has had this for some time now. I fail to see any
circumstance where one would ever want that behavior though. Do you have an
example where it makes sense?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

**polite person** · Jul 15 '06, 11:45 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

On Sat, 15 Jul 2006 10:05:42 +0800, "Allen Browne" <AllenBrowne@Se eSig.Invalidwro te:

>In Access 2000 and later, you can create a relation between tables where
>related records can be automatically set to Null rather than deleted when
>the primary record is deleted.
>
>I have not seen anyone using or discussing this feature, so there is a new
>article explaining the concept here:
Cascade to Null Relations
>at:
http://allenbrowne.com/ser-64.html

Allen, can this be done in a similar way in SQL-server? Will upsizing handle it?

**polite person** · Jul 15 '06, 11:45 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

On Sat, 15 Jul 2006 11:44:38 GMT, "Rick Brandt" <rickbrandt2@ho tmail.comwrote:

>John Mishefske wrote:

>Allen Browne wrote:

In Access 2000 and later, you can create a relation between tables
where related records can be automatically set to Null rather than
deleted when the primary record is deleted.
>
I have not seen anyone using or discussing this feature, so there
is a new article explaining the concept here:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

>>
>>
>Nice. Thanks for the great explanation. Any downsides?

>
>The database on our AS400 has had this for some time now. I fail to see any
>circumstance where one would ever want that behavior though. Do you have an
>example where it makes sense?

I expected this question (less politely) from David Fenton :)

**Allen Browne** · Jul 15 '06, 12:45 PM

Re: INF: Cascade-to-Null relations are supported in JET 4

Haven't experimented.

Perhaps someone who has can comment.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"polite person" <snip@snippers. comwrote in message
news:qalhb29fcb u31e3fmsljp96eb svh15c64v@4ax.c om...

On Sat, 15 Jul 2006 10:05:42 +0800, "Allen Browne"
<AllenBrowne@Se eSig.Invalidwro te:
>

>>In Access 2000 and later, you can create a relation between tables where
>>related records can be automatically set to Null rather than deleted when
>>the primary record is deleted.
>>
>>I have not seen anyone using or discussing this feature, so there is a new
>>article explaining the concept here:
> Cascade to Null Relations
>>at:
> http://allenbrowne.com/ser-64.html

>
Allen, can this be done in a similar way in SQL-server? Will upsizing
handle it?

**Allen Browne** · Jul 15 '06, 12:45 PM

Re: INF: Cascade-to-Null relations are supported in JET 4

The examples where I find this really useful are those discussed in the
article: operations where a child record (such as a weighbridge docket or
donation) later becomes a child of another parent (such as a monthly account
or thank-you letters), and the batch undo needs to return the 2nd foreign
key to null.

But any non-required foreign key field (such as items that can be
uncategorized) is a candidate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in message
news:Gy4ug.6627 7$fb2.51850@new ssvr27.news.pro digy.net...

John Mishefske wrote:

>Allen Browne wrote:

In Access 2000 and later, you can create a relation between tables
where related records can be automatically set to Null rather than
deleted when the primary record is deleted.
>
I have not seen anyone using or discussing this feature, so there
is a new article explaining the concept here:
Cascade to Null Relations
at:
http://allenbrowne.com/ser-64.html

>>
>>
>Nice. Thanks for the great explanation. Any downsides?

>
The database on our AS400 has had this for some time now. I fail to see
any circumstance where one would ever want that behavior though. Do you
have an example where it makes sense?
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

**Sky** · Jul 15 '06, 09:45 PM

Re: Cascade-to-Null relations are supported in JET 4

If you permit changes to the parent key field, perhaps you need:
dbRelationCasca deNull + dbRelationUpdat eCascade
to enable the equivalent of:
On Update Cascade, On Delete Set Null

- Steve

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:44b84d7b$0 $21696$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

In Access 2000 and later, you can create a relation between tables where
related records can be automatically set to Null rather than deleted when
the primary record is deleted.
>
I have not seen anyone using or discussing this feature, so there is a new
article explaining the concept here:
Cascade to Null Relations
at:

Microsoft Access tips: Cascade to Null Relationships

http://allenbrowne.com/ser-64.html

Highlights a little-known database feature, where related records can be automatically set to Null rather than deleted when the primary record is deleted. Applies to Microsoft Access 2000 and later.

>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>

**David W. Fenton** · Jul 16 '06, 01:35 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

polite person <snip@snippers. comwrote in
news:udlhb21amc 20lml74g2tj6tgm 07cqc3t9f@4ax.c om:

On Sat, 15 Jul 2006 11:44:38 GMT, "Rick Brandt"
<rickbrandt2@ho tmail.comwrote:
>

>>John Mishefske wrote:

>>Allen Browne wrote:
>In Access 2000 and later, you can create a relation between
>tables where related records can be automatically set to Null
>rather than deleted when the primary record is deleted.
>>
>I have not seen anyone using or discussing this feature, so
>there is a new article explaining the concept here:
> Cascade to Null Relations
>at:
> http://allenbrowne.com/ser-64.html
>>>
>>>
>>Nice. Thanks for the great explanation. Any downsides?

>>
>>The database on our AS400 has had this for some time now. I fail
>>to see any circumstance where one would ever want that behavior
>>though. Do you have an example where it makes sense?

>
I expected this question (less politely) from David Fenton :)

Why? Cascade to Null makes far more sense to me that Cascade Update,
which I consider completely useless.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

**David W. Fenton** · Jul 16 '06, 01:35 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in
news:44b8b70f$0 $21723$5a62ac22 @per-qv1-newsreader-01.iinet.net.au :

Yes, I should have included the DDL approach as well as the DAO
and ADOX.

I don't see why. DAO is by far the best way to handle changes to
table structures in Jet. Why bother with the others at all?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

**Allen Browne** · Jul 16 '06, 02:05 AM

Re: Cascade-to-Null relations are supported in JET 4

Thank you Sky.

That is correct if you want cascading updates as well as cascading deletes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sky" <sky@NOSPAMstan leyassociates.c omwrote in message
news:9odug.649$ rT6.404@trnddc0 3...

If you permit changes to the parent key field, perhaps you need:
dbRelationCasca deNull + dbRelationUpdat eCascade
to enable the equivalent of:
On Update Cascade, On Delete Set Null
>
- Steve
>
"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in message
news:44b84d7b$0 $21696$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...

>In Access 2000 and later, you can create a relation between tables where
>related records can be automatically set to Null rather than deleted when
>the primary record is deleted.
>>
>I have not seen anyone using or discussing this feature, so there is a
>new article explaining the concept here:
> Cascade to Null Relations
>at:
> http://allenbrowne.com/ser-64.html

**Allen Browne** · Jul 16 '06, 02:05 AM

Re: INF: Cascade-to-Null relations are supported in JET 4

Personally, I agree that DAO is the most appropriate, David, but the
interesting thing is that it is not documented as part of the DAO model.

It is documented for ADOX (which is how I originally discovered it) and for
DDL (as Roy pointed out.) So it probably doesn't hurt to included the
documented approaches for completeness. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9801DC2 581B5Af99a49ed1 d0c49c5bbb2@127 .0.0.1...

"Allen Browne" <AllenBrowne@Se eSig.Invalidwro te in
news:44b8b70f$0 $21723$5a62ac22 @per-qv1-newsreader-01.iinet.net.au :
>

>Yes, I should have included the DDL approach as well as the DAO
>and ADOX.

>
I don't see why. DAO is by far the best way to handle changes to
table structures in Jet. Why bother with the others at all?

INF: Cascade-to-Null relations are supported in JET 4

INF: Cascade-to-Null relations are supported in JET 4

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment