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

[Allen Browne]

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

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]

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" <jm**********@SPAMyahoo.comwrote in message
news:ty*************@tornado.rdc-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]

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 " & _
"tblCategory (CategoryID) ON DELETE SET NULL"
CurrentProject.Connection.Execute strSql, , _
adExecuteNoRecords + adCmdText
End Sub

--
Roy-Vidar

[Allen Browne]

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" <ro*************@yahoo.nowrote in message
news:mn***********************@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 " & _
"tblCategory (CategoryID) ON DELETE SET NULL"
CurrentProject.Connection.Execute strSql, , _
adExecuteNoRecords + adCmdText
End Sub

[Rick Brandt]

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]

On Sat, 15 Jul 2006 10:05:42 +0800, "Allen Browne" <Al*********@SeeSig.Invalidwrote:

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

On Sat, 15 Jul 2006 11:44:38 GMT, "Rick Brandt" <ri*********@hotmail.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]

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" <sn**@snippers.comwrote in message
news:qa********************************@4ax.com...

On Sat, 15 Jul 2006 10:05:42 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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

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" <ri*********@hotmail.comwrote in message
news:Gy*******************@newssvr27.news.prodigy. 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]

If you permit changes to the parent key field, perhaps you need:
dbRelationCascadeNull + dbRelationUpdateCascade
to enable the equivalent of:
On Update Cascade, On Delete Set Null
- Steve

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@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 - 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]

polite person <sn**@snippers.comwrote in
news:ud********************************@4ax.com:

On Sat, 15 Jul 2006 11:44:38 GMT, "Rick Brandt"
<ri*********@hotmail.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]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:44***********************@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]

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" <sk*@NOSPAMstanleyassociates.comwrote in message
news:9odug.649$rT6.404@trnddc03...

If you permit changes to the parent key field, perhaps you need:
dbRelationCascadeNull + dbRelationUpdateCascade
to enable the equivalent of:
On Update Cascade, On Delete Set Null

- Steve

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:44***********************@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]

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" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:44***********************@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?

[Allen Browne]

Oh dear, I use cascading updates in every database I write.

Mostly for natural keys in lookup tables.

Gets the user out of trouble when they have misspelled a category, such as:
Recieved
and need to cascade-update the category to all related records.

(One of the advantages of text-based keys is that, unlike hidden keys, the
value does not disappear in the combo in a continuous form when the combo's
RowSource is filtered.)

--
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" <XX*******@dfenton.com.invalidwrote in message
news:Xn*********************************@127.0.0.1 ...

polite person <sn**@snippers.comwrote in
news:ud********************************@4ax.com:

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

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:44***********************@per-qv1-newsreader-01.iinet.net.au:

Oh dear, I use cascading updates in every database I write.

Mostly for natural keys in lookup tables.

Well, I would agree that there is the one situation where natural
keys make sense, i.e., in a single-column lookup table. In that
case, yes, cascade update makes sense.

But given that the number of one-column lookup tables is fairly
limited in any application, it's a very limited scope in which
cascade update is sensible.

I even have problems philosophically with it in single-column lookup
tables, as I am bothered by basing any of the relations in my
database on data that a user can change.

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

[RoyVidar]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
<Xn**********************************@127.0.0.1> :

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?

To mention a couple of possible reasons
1 - not everyone working with Jet use Access
2 - some like/need to utilize some of the features that were introduced
to Jet six years ago, which are not supported by DAO or Access
3 - customer requirements to avoid DAO
4 - customer programming standards/requirements favouring DDL vs more
programmatic approaches

Say, for adding a lookup table, the statements below, should do the
same, regardless of wether it is executed on a connection to SQL server
*) or Jet.

strSql = "Create Table myLookup (" & _
"id Int Identity CONSTRAINT PK_id PRIMARY KEY, " & _
"myText Varchar(25) CONSTRAINT idx_myText UNIQUE NOT NULL)"
cn.Execute strSql, , adCmdText + adExecuteNoRecords
strSql = "ALTER TABLE testTable " & _
"ADD CONSTRAINT FK_testTable FOREIGN KEY (fk) " & _
"REFERENCES myLookup ON DELETE SET NULL"
cn.Execute strSql, , adCmdText + adExecuteNoRecords

*) Note - as far as I know, the ON DELETE SET NULL/ON UPDATE SET NULL
is
a new feature in SQL Server 2005

Offering more approaches, makes Allen Brownes articles even more
readable, in my view.

--
Roy-Vidar

[RoyVidar]

"polite person" <sn**@snippers.comwrote in message
<qa********************************@4ax.com>:

On Sat, 15 Jul 2006 10:05:42 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

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

The feature, as far as I've understood, was introduced in SQL Server
2005. So it should be available there. DDL through ADO seems to work,
I haven't tested other approaches.

--
Roy-Vidar

[David W. Fenton]

RoyVidar <ro*************@yahoo.nowrote in
news:mn***********************@yahoo.no:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
<Xn**********************************@127.0.0.1 >:

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

To mention a couple of possible reasons
1 - not everyone working with Jet use Access

But they wouldn't be posting in an Access newsgroup or reading a
site like Allen's that is devoted to Access.

2 - some like/need to utilize some of the features that were
introduced to Jet six years ago, which are not supported by DAO or
Access

This one is one that *is* supported by DAO, so there's no need for
ADO.

In the choice between DAO and DDL, DAO wins hands down, because it's
got access to more of the properties of an Access database than DDL,
which is much more generic.

3 - customer requirements to avoid DAO

Yes, sometimes one is working for idiots.

4 - customer programming standards/requirements favouring DDL vs
more programmatic approaches

DDL is useful when you need to write SQL to work on multiple
platforms, but is this a case where the Jet DDL is compatible with
other databases? If so, then it makes sense if that's a requirement,
but if it's not compatible, then it makes no sense even if it *is* a
requirement (or desirable).

Say, for adding a lookup table, the statements below, should do
the same, regardless of wether it is executed on a connection to
SQL server *) or Jet.

strSql = "Create Table myLookup (" & _
"id Int Identity CONSTRAINT PK_id PRIMARY KEY, " & _
"myText Varchar(25) CONSTRAINT idx_myText UNIQUE NOT
NULL)"
cn.Execute strSql, , adCmdText + adExecuteNoRecords
strSql = "ALTER TABLE testTable " & _
"ADD CONSTRAINT FK_testTable FOREIGN KEY (fk) " & _
"REFERENCES myLookup ON DELETE SET NULL"
cn.Execute strSql, , adCmdText + adExecuteNoRecords

*) Note - as far as I know, the ON DELETE SET NULL/ON UPDATE SET
NULL is
a new feature in SQL Server 2005

Offering more approaches, makes Allen Brownes articles even more
readable, in my view.

I am categorically against DDL as a way of manipulating Jet database
structures unless you have outside requirements, such as the need to
be able to run the same DDL against more than one database engine.

Fortunately, I've never had to do such a thing, and don't think DDL
belongs in an application in the first place. Changing table
structures is not something that should be in application code at
all, in my opinion.

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

[RoyVidar]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
<Xn**********************************@127.0.0.1> :

Changing table
structures is not something that should be in application code at
all, in my opinion.

I agree.

--
Roy-Vidar

[Larry Linson]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote

I even have problems philosophically
with it in single-column lookup
tables, as I am bothered by basing
any of the relations in my
database on data that a user can change.

I agree, but have one table that uses a natural key... a table of state
abbreviations and names, used in a number of applications. The user has no
ability to change that table, and the government/post office changes it
infrequently.

But, to those who feel compelled to use a key that a user can change, I
strongly recommend Cascade Update.

Larry Linson
Microsoft Access MVP

[Jamie Collins]

Allen Browne wrote:

I have not seen anyone using or discussing this feature

That's funny, I have:

http://groups.google.com/group/micro...8601b4391581d/

lol!

Jamie.

--

[Pieter Wijnen]

because we're all sensible enough to program cascading deletes <g>

Pieter

"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>
Allen Browne wrote:

>I have not seen anyone using or discussing this feature

That's funny, I have:

http://groups.google.com/group/micro...8601b4391581d/

lol!

Jamie.

--

[Pieter Wijnen]

because we're all sensible enough to program cascading deletes <g>

Pieter

"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>
Allen Browne wrote:

>I have not seen anyone using or discussing this feature

That's funny, I have:

http://groups.google.com/group/micro...8601b4391581d/

lol!

Jamie.

--

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4231 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

[Jamie Collins]

Pieter Wijnen wrote:

because we're all sensible enough to program cascading deletes <g>

AFAIK for Access/Jet using ON DELETE CASCADE is the only way of doing
so as a single atomic action.

As someone said, 'Trust the engine, Luke.'

Jamie.

--

[Pieter Wijnen]

that's true

Pieter

"Jamie Collins" <ja**********@xsmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

>
Pieter Wijnen wrote:

>because we're all sensible enough to program cascading deletes <g>

AFAIK for Access/Jet using ON DELETE CASCADE is the only way of doing
so as a single atomic action.

As someone said, 'Trust the engine, Luke.'

Jamie.

--

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4282 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len