Keys¶
Primary and Foreign Keys¶
Relational Databases are composed of tables and ways to connect, or relate, those table to each other (we've used the term joins for these relations, so let's keep doing that).
Each row in each table in a relational database must have a value (or combination of values) that can uniquely identify that row. Most of the time in real life, and all of the time in this set of lessons, the unique identifier for the rows will be in a column called 'id', and it will be an integer.
That unique identifier is the primary key for each record in your table. It's the first, easiest and most robust way for you and your database to find that record.
Note
A computer's first language is math. This means that computers process numbers very quickly and efficiently. When we use an integer (a whole positive number) as a primary key, we're making it easy on the computer.
The database software that you use (sqlite in our examples) will very likely let you automatically increment the 'id' field every time you create a new row. This means that in most cases, the primary keys just represent the order of entry of records into the table.
If we were to insert a new row into the 'originators' table, for example, it would be automatically assigned an 'id' of '4'.
| id | originator |
|---|---|
| 1 | Audra Vincent |
| 2 | Michelle Clark |
| 3 | Lawrence Nicodemus |
The 'originator' table above contains a column of primary keys, but the 'words' table below contains both a primary key column (the one called 'id'), and a foreign key column (the one called 'originator_id').
A foreign key is a column in a table that contains the primary key from another table.
| id | root | nicodemus | english | originator_id |
|---|---|---|---|---|
| 1 | a | a | hello. | 3 |
| 2 | bc | buts | boots. | 3 |
| 3 | bs | tbilso | dragonfly. | 1 |
While every table in a relational database must contain a primary key, not every table needs to contain a foreign key. Figuring out how to parse the information you have out into separate tables, and then join those tables to each other based on primary and foreign keys is one of the big challenges of designing a relational database!
And if we have tables that are joined in this way, we can produce queries that return information from both tables, like this one:
| root | nicodemus | english | originator |
|---|---|---|---|
| a | a | hello. | Lawrence Nicodemus |
| bc | buts | boots. | Lawrence Nicodemus |
| bs | tbilso | dragonfly. | Audra Vincent |
How'd we do?¶
Do you feel like you understand primary and foreign keys? If so, great! If not, we hope you'll ask more questions.
You can contact John, Gus, or Amy and we'll do the best we can to help!
At this point, you might want to go back to our our Day 1 materials 🚀 or to the CoLang Workshop Homepage 🚀