38

If I have a relationship between two tables (both tables have their own primary keys) what should guide my decision as to which table should store the foreign key? I understand that the nature of the relationship probably matters (one-to-one, one-to-many, many-to-many, uni-directional, bi-directional), and probably access patterns matter too. What is a systematic way of making that decision though?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
vicatcu
  • 5,407
  • 7
  • 41
  • 65
  • 2
    "What is a systematic way of making that decision?" "understand that the nature of the relationship probably matters". Correct. The nature of the relationship matters. I don't get the question. Do you want to know how to define which is dependent on the other? – S.Lott Jul 20 '10 at 19:54
  • The [MySQL documentation of foreign keys](https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html#idm139680617903472) provides a simple example of the relationship between 2 parent tables: `customer`, `product` and one child table: `product_order`. In that example `product_order` is the child table which should contain the foreign keys. – Paul Rougieux Jul 30 '17 at 13:23

4 Answers4

46

Which table is the child in the relationship?
Answer that, and you know which table needs the foreign key column, referencing the parent's [typically] primary key. That's for a one-to-many relationship...

A many-to-many would require you to add a third table, using the keys from both of the two tables as it's primary key.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 4
    ... and if there isn't a child / parent relationship. it's probably many-to-many in a join-table. – Wrikken Jul 20 '10 at 19:53
  • 8
    Or it's a one-to-one relationship and OP should be asking himself why it's split into two tables in the first place. – Allan Jul 20 '10 at 19:58
  • Now, what about the bi-directional many-to-many relations on self (1 links to 2,3,&4, so evidently 2 links to 1, but can also link to 3,6, or whatever), that's on I don't have a really good workable answer for. Either a fabricated relationship-id + some way to enforce only 2 rows with that relationship-id in a (relationship-id,subject-id) table, or a 2 column table with a a join or union on self to get relationships 'stored the other way around'. – Wrikken Jul 20 '10 at 20:22
12

"What is a systematic way of making that decision though?"

There appear to be two choices: The "One" side as FK's to the "Many side", or the "Many" Side has FK's to the "One" side.

Let's actually look a the choices.

  • All the rows of the "Many" side can easily reference one row on the "One" side.

  • The one row on the "One" side cannot ever reference ALL of the rows on the "Many" side.

Only one technique works: "Many" side has FK to "One" side.

There is only one actual implementation choice. There's no "decision".

S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • 6
    In short: it's a matter of cardinality. If you have a many-to-one relationship, the key should reside on the 'many' side. If it is one-to-perhaps-one, on the 'one' side. If it is many-to-many, you need an intermediate table. If it's one-to-one, you're free to choose. – reinierpost Jul 20 '10 at 22:26
6

A foreign key is simply a field in one table that refers to a key field of another table. It's not absolutely critical to identify the foreign key field as such. That is, you don't need to explicitly add the FOREIGN KEY ... REFERENCES constraint to the table for it to be a foreign key. When you join the two tables together, the primary key of the parent table will be set equal to the foreign key of the child table. Whichever one is not the primary key is the foreign key.

In one-to-many relationships, the FK goes on the "many" side. It can't go on the "one" side because that's where the PK goes and the definition of a primary key includes disallowing duplicates.

If you have a many-to-many relationship, you'll need to re-work the tables so you end up with two one-to-many relationships and an intermediate resolution table.

Barry Brown
  • 20,233
  • 15
  • 69
  • 105
0

Like a primary key, a foreign key is also a type of constraint placed on one or more columns in a table.

The foreign key establishes a link between the key columns and related columns in another table. (You can also link the foreign key columns to columns within the same table.)

The table that contains the foreign key is considered the child table, and the table that the foreign key references is the parent table.

Key Points

  1. The foreign key must reference a primary key or unique constraint, although that reference can be on the same table or on a different table
  2. A foreign key must also have the same number of columns as the number of columns in the referenced constraint, and the data types must match between corresponding columns.
  3. Unlike Primary key, Foreign key columns can contain NULL values.
Mehraj Malik
  • 14,872
  • 15
  • 58
  • 85