troubleshooting Question

Composite foreign key relationships

Avatar of Doug
DougFlag for United States of America asked on
Microsoft AccessMySQL Server
4 Comments1 Solution1055 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros