INF: Cascade-to-Null relations are supported in JET 4
Allen Browne
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
> 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
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" <jmishe...@SPAMyahoo.com> wrote in message
news:tyYtg.80$u11...@tornado.rdc-kc.rr.com...
RoyVidar
> 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
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_vid...@yahoo.no> wrote in message
news:mn.7a147d679...@yahoo.no...
Rick Brandt
> 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
>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
>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
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.com> wrote in message
news:qalhb29fcbu31e3fm...@4ax.com...
Allen Browne
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" <rickb...@hotmail.com> wrote in message
news:Gy4ug.66277$fb2....@newssvr27.news.prodigy.net...
Sky
dbRelationCascadeNull + dbRelationUpdateCascade
to enable the equivalent of:
On Update Cascade, On Delete Set Null
- Steve
"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message
news:44b84d7b$0$21696$5a62...@per-qv1-newsreader-01.iinet.net.au...
David W. 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
> 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
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" <s...@NOSPAMstanleyassociates.com> wrote in message
news:9odug.649$rT6.404@trnddc03...
Allen Browne
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" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9801DC2581B5Af9...@127.0.0.1...
Allen Browne
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" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9801DBFC9A80f9...@127.0.0.1...
> polite person <sn...@snippers.com> wrote in
> news:udlhb21amc20lml74...@4ax.com:
>
> ... Cascade to Null makes far more sense to me that Cascade Update,
David W. Fenton
> 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.
RoyVidar
> 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
<qalhb29fcbu31e3fm...@4ax.com>:
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
news:mn.846d7d67e...@yahoo.no:
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
><Xns9801DC2581B5Af9...@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.
RoyVidar
> Changing table
> structures is not something that should be in application code at
> all, in my opinion.
I agree.
--
Roy-Vidar
Larry Linson
> 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:
lol!
Jamie.
--
Pieter Wijnen
Pieter
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1153146778.0...@h48g2000cwc.googlegroups.com...
Pieter Wijnen
Pieter
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1153146778.0...@h48g2000cwc.googlegroups.com...
>
--
----------------------------------------
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
Pieter
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1153209852.7...@m73g2000cwd.googlegroups.com...
--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4282 spam emails to date.