issues on table with relationship to self

[Dick Watson]

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!

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Dick Watson]

The UPDATE to Null then the DELETE will probably work. That's a great idea.
I'll give it a try!

Thanks!