Link to home
Create AccountLog in
Avatar of stevefNYC
stevefNYC

asked on

Using foreign keys in a one-to-many relationship in MySQL

All,

I'm trying to understand one-to-many relationships in better depth.

To my understanding if I have two tables, foo and bar -- if foo can have many bars and bars and only have one foo, I put a foreign key from foo into bar.

My question is now, what kind of data integrity setback could I face if I put a foreign key from bar into foo (in the opposite direction)

What kind of issues would that cause? If you need a more solid example, please let me know and I'll use real tables.
Avatar of stevefNYC
stevefNYC

ASKER

Just to give an example, perhaps to better clear things up --

I have two tables, sessions and emails.

One session can have one email. An email can belong to many sessions.

According to the rules of normalization, this means I should place an email_id into the sessions table. Now, I have another developer asking me why don't we just put a session_id into the emails table. I'm trying to think of a solid reason as to how this can cause issues -- I just do know it's against normalization rules. However, I don't see where it would sacrifice the integrity of my data.
Avatar of arnold
You might want to use triggers to enforce.

Usually you will either get errors because what you adding will violate the likely unique rule often applied to a column used as a key. you would violate the one entry in foo referencing many entries in bar.

If you are able to add an entry to foo, your will have a possibility of duplicate entries.

Please provide an example of what you are thinking.
Please clarify further.
a session is an event that generates an email message.
The same email message can be used for multiple sessions.

Is this what the setup is?
A session is an event that's associated with an email address. The same email address CAN be associated with multiple sessions.

You're correct in that setup assumption. now I need to know why it would break if I put a session_id into my emails table.
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I am assuming you have 2 tables like

Sessions (session_id, session_name,.....)

Emails (email_id, session_id, email_nameorwhatever...)

And that:
Each session in the emails table should be unique BUT the emails table can have the same email associated with several sessions.

But:
In the sessions table, there can be only ONE UNIQUE session and session_id.

Now, if my assumption is correct, you can only make session_id a foreign key in the emails table. Which is what your friend suggested. You probably are getting something wrong about foreign keys.

Doing it the other way (your way) is WRONG and would not work as a foreign key should.  So do it your friend's way for that is THE ONE CORRECT way.

Do this and then try other table/record manipulations like update, delete, etc and  I am sure will convince yourself that that is the correct thing to do.

Take Care.
cedlinx, you interepreted the setup incorrectly.
two tables:
emails (email_id, ......)
sessions( session_id, email_id,.....)

Arthur_Wood, has explained it clearly.

stevefnyc,  if you were to put the session_id into the emails table as your developer requested, the only information that change will provide is which session referenced this email address last (by session number).  This information can be retrieved by getting a descending list of the session_ids.

Glad to be of assistance

AW
Thank you, Arthur!