Solved

Composite foreign key relationships

Posted on 2012-03-18
4
974 Views
Last Modified: 2012-03-25
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.
0
Comment
Question by:Doug
  • 2
  • 2
4 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 450 total points
ID: 37735541
I cannot advise you on how to declare a composite foreign key constraing using the MySQL designer view.

But I can tell you that the symptoms you report suggest that you have not done it correctly.  You symptoms indicate two different foreign key relationships and do not enforce the same business rule as the composite fk.
 

You can always declare the desired constraint using DDL.  Note, you need to create the index first.

Alter table YourTable
   add constraint YourConstraintName  Foreign Key YourIndex(col1, col2) references YourParentTable (col1, col2) on delete cascade


On second thought,  you cannot declare a relationship to part of the parent key.  That suggests to me that you have not declared a composite key on the parent table.  Perhaps if you do that, the designer will allow you to specify both columns of the foreign key.
0
 
LVL 7

Author Comment

by:Doug
ID: 37735937
You said:
>>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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37736421
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.
0
 
LVL 7

Author Closing Comment

by:Doug
ID: 37762734
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.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now