Solved

Composite foreign key relationships

Posted on 2012-03-18
4
970 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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

11 Experts available now in Live!

Get 1:1 Help Now