Foreign key with SET NULL: possible?

Is it possible to create a Jet 4.0 FOREIGN KEY with SET NULL for the ON
UPDATE and ON DELETE rules?

I've seen it mentioned in two Microsoft documents:

http://support.microsoft.com/default...b;en-us;275561
Description of the new features that are included in Microsoft Jet 4.0

http://office.microsoft.com/en-us/as...322141033.aspx
Assistance Access 2003: CONSTRAINT Clause

However, I have so far been unable to create such a constraint using
either DDL (i.e. CREATE TABLE or ALTER TABLE) or ADOX. I get the same
errors as I do when using a rule I know Jet does not support (i.e. SET
DEFAULT).

This leaves me wondering whether the syntax is supported at all. My
suspicions are further raised by the fact that both of the
abovementioned articles use the same examples containing the same
syntax errors i.e.

... FOREIGN KEY (CustId) REFERENCES Customers ON ...

should be

... FOREIGN KEY (CustId) REFERENCES Customers (CustId) ON ...

and the CREATE TABLE statements are missing their closing parentheses.
(I'm thinking MS are not interested in receiving text corrections g.)

Possibly the syntax is only supported in the Access2003 UI but can't
test because I no longer have an Access2003 install.

Any confirmation, thoughts, etc?

Jamie.

--

Hi Jamie.

JET 4 does support cascade-to-null. I don't recall if you can do it with a
DDE query statement, but if you can I imagine it would need to be executed
under ADO and not through the interface (which uses DAO.)

You can certainly do it with ADOX. The syntax looks like this, assuming a
data structure where one contractor can have many bookings:

Sub CreateKeyAdox()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim ky As New ADOX.Key

Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("tblBooking")

'Create as foreign key to tblContractor.ContractorID
With ky
.Type = adKeyForeign
.Name = "tblContractortblBooking"
.RelatedTable = "tblContractor"
.Columns.Append "ContractorID"
.Columns("ContractorID").RelatedColumn = "ContractorID"
.DeleteRule = adRISetNull 'Cascade to Null on delete.
End With
tbl.Keys.Append ky

Set ky = Nothing
Set tbl = Nothing
Set cat = Nothing
Debug.Print "Key created."
End Sub

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

"Jamie Collins" wrote in message
ups.com...
Is it possible to create a Jet 4.0 FOREIGN KEY with SET NULL for the ON
UPDATE and ON DELETE rules?

I've seen it mentioned in two Microsoft documents:

http://support.microsoft.com/default...b;en-us;275561
Description of the new features that are included in Microsoft Jet 4.0

http://office.microsoft.com/en-us/as...322141033.aspx
Assistance Access 2003: CONSTRAINT Clause

However, I have so far been unable to create such a constraint using
either DDL (i.e. CREATE TABLE or ALTER TABLE) or ADOX. I get the same
errors as I do when using a rule I know Jet does not support (i.e. SET
DEFAULT).

This leaves me wondering whether the syntax is supported at all. My
suspicions are further raised by the fact that both of the
abovementioned articles use the same examples containing the same
syntax errors i.e.

... FOREIGN KEY (CustId) REFERENCES Customers ON ...

should be

... FOREIGN KEY (CustId) REFERENCES Customers (CustId) ON ...

and the CREATE TABLE statements are missing their closing parentheses.
(I'm thinking MS are not interested in receiving text corrections g.)

Possibly the syntax is only supported in the Access2003 UI but can't
test because I no longer have an Access2003 install.

Any confirmation, thoughts, etc?

Jamie.

Allen Browne wrote:
JET 4 does support cascade-to-null.

Thanks Allen. I can get it to work for ON DELETE (both DDL and ADOX)
but not for ON UPDATE i.e.

.UpdateRule = adRISetNull ' (ON UPDATE SET NULL)

returns an error.

On reflection, ON UPDATE SET NULL would not be very useful anyhow. Not
that I'm thinking of using ON DELETE SET NULL any time soon, either
g. I'm just trying to establish what Jet does support so that my app
can support the same features.

Thanks again,
Jamie.

--

ADOX is very buggy and inconsistent, but I have that code working in ADOX
2.8 on JET 4.0.8618.0.

I agree that it is not a good idea to use cascade-to-null, especially since
the interface cannot show this kind and so anyone who must maintain the
database in the future has no visual clue that this kind of relation is in
use. If it were propertly implemented it might be a useful concept though.

Examples:
- Delete a category and all the related records become uncategorized but you
don't lose them.

- Delete a client, and all their invoices become "cash" i.e. the f.k.
ClientID is null because the client is now unknown.

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

"Jamie Collins" wrote in message
oups.com...
Allen Browne wrote:
JET 4 does support cascade-to-null.

Thanks Allen. I can get it to work for ON DELETE (both DDL and ADOX)
but not for ON UPDATE i.e.

.UpdateRule = adRISetNull ' (ON UPDATE SET NULL)

returns an error.

On reflection, ON UPDATE SET NULL would not be very useful anyhow. Not
that I'm thinking of using ON DELETE SET NULL any time soon, either
g. I'm just trying to establish what Jet does support so that my app
can support the same features.

Thanks again,
Jamie.

Allen Browne wrote:
ADOX is very buggy and inconsistent, but I have that code working in
ADOX
2.8 on JET 4.0.8618.0.

I am using the same components and versions. The delete rule I can get
to work. The update rule I cannot get to work.

If you can do it for UPDATE (your earlier example was for DELETE only),
please post your code.

Examples:
- Delete a category and all the related records become uncategorized
but you
don't lose them.

- Delete a client, and all their invoices become "cash" i.e. the f.k.

ClientID is null because the client is now unknown.

I agree your examples are potentially very useful for delete but what
about for *update*, when would that be desireable to cascade to null?

Thanks,
Jamie.

--

"Jamie Collins" wrote in message
oups.com...
I agree your examples are potentially very useful for delete but what
about for *update*, when would that be desireable to cascade to null?

Dunno. Can't imagine wanting to do that, and don't think I ever had reason
to try it.

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