[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Composite foreign key relationships

Posted on 2012-03-18
4
Medium Priority
?
998 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 1800 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 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