Questions? Comments? Email faq-forkeys [at] tucuxi [dot] org
What are foreign keys?
Foreign keys are a way of referencing other tables' rows, in a manner that maintains referential integrity. Why is this important? Because typically, you want the data in a database to be consistent and authoritative. Foreign keys ensure that this happens in a manner guaranteed by your DBMS - in the example below, you can't add a row to the member_plays table unless the memberid exists in members, and the sportid in sports.
How do I design foreign keys into a schema?
Think about your data. If you have a one-to-one, or one-to-many mapping, clearly, one of the tables will need to reference another. In the case of one-to-one, you can either collapse the two tables into one, insert a foreign key into one of the tables (for this example, a 'sport' field in the members table), or have a third table (as we do below), with a 'UNIQUE' restriction. Usually, the first option is the best if the design will not change. If it is forseeable that the design will change to allow a one-to-many mapping, create the third table - you can drop the UNIQUE constraint later on.
For this page, we'll focus on many-to-many mappings. For this, you need two tables containing data that needs to be linked - we'll deal with keeping track of who participates in a given sport. If you picture your database rows side by side, imagine lines being drawn between the two as a mapping between the various rows. We'll store the Primary Keys of each table together, in a third table, allowing the mapping to be kept in a simple manner. For example, Member 7 participates in Sport 3. We would have a row tuple of (7, 3), assuming the table has fields (member, sport).
Example Data
members
| memberid | name | phone |
|---|---|---|
| 1 | John | 12345678 |
| 2 | Alice | 38572382 |
sports
| sportid | name |
|---|---|
| 1 | Soccer |
| 2 | Hockey |
member_plays
| member | sport |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
This setup says that John (1) and Alice (2) both play Hockey (2), and John also plays Soccer (1). The two fields in member_plays are known as Foreign Keys, which basically means they reference rows in another table. This is known as a many-to-many relationship, as many members may be mapped to the one sport, and many sports may be mapped to the one member. This is usually the best way to do things, as it is in a normalised form, removing redundancy that would come from compressing it all into a single table.