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**********@S PAMyahoo.comwro te in message
news:ty******** *****@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]

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]

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 " & _
"tblCategor y (CategoryID) ON DELETE SET NULL"
CurrentProject. Connection.Exec ute strSql, , _
adExecuteNoReco rds + 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*********@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]

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

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.c om...

On Sat, 15 Jul 2006 10:05:42 +0800, "Allen Browne"
<Al*********@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]

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*********@ho tmail.comwrote in message
news:Gy******** ***********@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