Composite foreign key relationships
Hello,
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A composite index, does not mean you have a composite PK. A composite PK does mean you have a composite index.
A composite FK must reference a composite PK and a simple FK may not reference a composite PK.
I have no way to prove it, but I really expect if you declare a composite PK, the designer will allow you (insist actually) to create a composite FK.
A composite FK must reference a composite PK and a simple FK may not reference a composite PK.
I have no way to prove it, but I really expect if you declare a composite PK, the designer will allow you (insist actually) to create a composite FK.
ASKER
dqmq, the syntax did what I needed to establish the constraints and I could see the results using SHOW TABLE. Interestingly, the relationship would show in the Designer view aftwerward but *only* if the key was a simple key. Composite key relationships were only demonstrated by using SHOW TABLE. The Designer does not show the relation lines between a composite PK and a composite FK. Unfortunately for me, after I set up and confirmed all of the FK constraints, Access would still not play nice with MySQL. My form recordsources were not obeying the table constraints. I chalked this up to the complex nature of my front end db (multiple nested subforms among other things). Many thanks for your help.
ASKER
>>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.