I have a web application and am using ADO Recordset DTC's. I create two recordsets "1" which has a join between tables A & B. The second recordset "2" which has a join between tables B&C. Each table has an ID field A_ID, B_ID and C_ID the value of which is autogenerated. Here's the problems:
If I set the ID field of each table as a PRIMARY key and then do a ".deleteRecord()" on recordset "2", not only does it delete the record in table "C" but the joined record in table B and A also get deleted. If I remove the PRIMARY key designation on the ID fields, only the record in table "C" is deleted - in other words it works correctly.
If I remove only one of the PRIMARY key designations and try to execute the ".deleteRecord()" is get "insufficient key column information..."
It appears that having a "key" field and then also designating it as a PRIMARY field seems redundant but I wouldn't think it would cause this type of behavior.
Any help in explaining this would be appreciated.

How is your referential integrity set? Meaning, do you have cascade deletes on? Do you even have RI (referential integrity) between the tables (meaning, PK / FK relationships between the tables)?

Scott

There is no explicit referential integrity set i.e. no relationships explicitly defined. It appears that when executing the ADO.deleteRecord on a recordset derived from a JOIN select statement appears to identify the PRIMARY key fields and cascade the delete BACKWARDS. What I mean is that if table relations are specifically identified, the cascade delete is from PRIMARY to FOREIGN key tables, but I'm seeing a cascade delete from FOREIGN to PRIMARY tables. In other words, the "child" delete is also deleting the "parent" and leaving orphans.

This is really confusing because you can't have cascade delete's without RI, UNLESS you have a trigger on those tables that is firing when the delete is happening. Do those tables have any triggers on them?

There is nothing configured on the tables. I created the table, defined the fields and set one field as a primary key. After the tables were created, I connect to the database and create an ADO recordset using the Recordset DTC control on my asp page. The recordset is populated using a store procedure that contains a select statement that includes a join between two tables (the child table contains the same field name as the primary key field in the parent table). On the asp page the recordset is displayed in a grid control with navigation keys. The user selects a record and clicks delete button which executes rsRecordset.deleteRecord() method. When this occurs, not only does the record in the child table get deleted, so to does the record with the same ID in the common field between the two tables. So in other words, deleting the child also deletes the parent.
If I remove the primary key designation from one of the tables and try to delete I get the "insufficient key column information..." error. If I delete both primary key designations then only the record in the child table gets deleted.

Hmmmmm...ok, let me dig around a bit and look at some things. This is very intriguing...

SQLScott,

Glad you're taking an interest. Look forward to your insight. Have a good Thanksgiving!