If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. -- |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. -- |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. -- |
#6
|
|||
|
|||
"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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
UNION Query with Criteria | Dkline | Running & Setting Up Queries | 1 | August 4th, 2004 09:15 PM |
Need someone to look at SQL statement | Dkline | Running & Setting Up Queries | 4 | August 3rd, 2004 05:12 PM |
"UNION" Query with different fields in the two tables | Dkline | Running & Setting Up Queries | 5 | July 30th, 2004 09:05 PM |
Access inconsistencies | Hoopie | Running & Setting Up Queries | 2 | June 15th, 2004 10:53 AM |