I typically develop in MS Access and occasionally connect to a MySQL back end. I have a MySQL back end that isn't cascading deletes as I'd expect when I delete records. I'm wondering if it's because of how I've set up the table relationships (foreign keys). I don't know enough about MySQL to know if I've done this right. In designer view I set up the relationships using the designer view in MySQL. For a composite primary key field (InterviewID, Coder ID) in tblInterviews I created two separate relations to tblSB for each of these two primary key fields (tblSB includes a 3rd field, SBid, as its composite PK). The designer view is a little different from Access in that you can't highlight more than one field at a time to set up relationships. I did find forums that discuss the syntax for setting up the relationship with the foreign key but I don't know if it's equivalent to what I did in designer. I suspect not because currently when I try to delete a specific record (unique InterviewID, CoderID combination) *ALL* interview records for the CoderID in the InterviewID, CoderID combination get deleted (and this cascades through to other subordinate tables as well). I also am wondering if I need to set up my primary key in a way that I am not currently doing (i.e., setting the primary key as unique). Any help would be appreciated. I spent all day yesterday going through MySQL forums (including EE) and MySQL documentation but had no luck figuring this out. Thanks in advance.
>>You symptoms indicate two different foreign key relationships and do not enforce the same business rule as the composite fk. <<
In other words, I don't actually have a composite FK set up in my child table like I thought I had done in designer. This would make sense as the problem. It unfortunately means that I have to delete the existing relationships for many tables because of how I tried to set it up in Designer. Then I'll need to set up the relationships with syntax you posted. I'll give that a shot.
I actually did have the composite PK (InterviewID, CoderID) set in the parent table (tblInterviewRecord) and had a composite PK (SBid, InterviewID, CoderID) set in the child table (tblSB). The composite primary keys show under the Index section of each table in the Structure view so I interpret that as meaning that I don't need to separately set the primary keys as indexes. I think this means that I just need to try setting up the relations using DDL, as you suggested.