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.
LVL 7
DougAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DougAuthor Commented:
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
dqmqCommented:
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
DougAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.