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
|
|||
|
|||
issues on table with relationship to self
I have a table that has a column related to another column. (It's a linked
list.) This relationship is defined explicitly and enforced. I want to enforce the relationship as an integrity safety net. Now, DELETE * FROM MyTable; doesn't work since there are related records. (I'm trying to do this from VBA via ADO.) I've played with the Cascade Delete setting to no avail. Setting it just results in record getting deleted in mid deletion and the query errors. So my questions: 1) In general, what's the best way to programmatically treat DELETE * on a table that has an enforced relationship to itself? 2) Is there any way to get there from here with ADO? ADOX has the ability to define the relationships but I can't find any way to control the setting for referential integrity. My thinking was to programmatically remove this requirement, do the delete, then re-establish this relationship. 3) I've considered nested queries to delete from the deepest items to the root items with a loop and so forth. Since I'm trying not to set a limit to depth, this gets ugly and doesn't scale well. Am I missing something? 4) Are the ADO Relationship Attribute values defined anywhere? Thoughts? Thanks in advance! |
#2
|
|||
|
|||
issues on table with relationship to self
Assuming that your foreign key field is not Required, perhaps you could
execute an Update query and update it to Null: UPDATE MyTable SET MyForeignKey = Null; DELETE FROM MyTable; JET actually supports cascade-to-null relationships, so you may be able to use that instead of the UPDATE query: 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. "Dick Watson" wrote in message ... I have a table that has a column related to another column. (It's a linked list.) This relationship is defined explicitly and enforced. I want to enforce the relationship as an integrity safety net. Now, DELETE * FROM MyTable; doesn't work since there are related records. (I'm trying to do this from VBA via ADO.) I've played with the Cascade Delete setting to no avail. Setting it just results in record getting deleted in mid deletion and the query errors. So my questions: 1) In general, what's the best way to programmatically treat DELETE * on a table that has an enforced relationship to itself? 2) Is there any way to get there from here with ADO? ADOX has the ability to define the relationships but I can't find any way to control the setting for referential integrity. My thinking was to programmatically remove this requirement, do the delete, then re-establish this relationship. 3) I've considered nested queries to delete from the deepest items to the root items with a loop and so forth. Since I'm trying not to set a limit to depth, this gets ugly and doesn't scale well. Am I missing something? 4) Are the ADO Relationship Attribute values defined anywhere? Thoughts? Thanks in advance! |
#3
|
|||
|
|||
issues on table with relationship to self
The UPDATE to Null then the DELETE will probably work. That's a great idea.
I'll give it a try! Thanks! "Allen Browne" wrote in message ... Assuming that your foreign key field is not Required, perhaps you could execute an Update query and update it to Null: UPDATE MyTable SET MyForeignKey = Null; DELETE FROM MyTable; JET actually supports cascade-to-null relationships, so you may be able to use that instead of the UPDATE query: http://allenbrowne.com/ser-64.html |
Thread Tools | |
Display Modes | |
|
|