Relational data: primary and foreign keys

Earlier on in the course we talked about relational data, spread amongst multiple tables.

We mentioned that often it is a good idea to use unique identifiers for each row in each table to relate them easily.

We could make our users and accounts table have a unique id field for each column, like so:

users

id	first_name	surname
1	John	Smith
2	Rolf	Smith
3	Anne	Pun
4	Robert	Baratheon

accounts

id	holder_id	number
1	3	8375841
2	4	1343545
3	1	9586859
4	2	1234567

Let's focus on data for a particular person, let say "John Smith":

users

id	first_name	surname
1	John	Smith

accounts

id	holder_id	number
3	1	9586859

We can tell his account is that with id=3 because the holder_id=1.

We would say that:

• users.id and accounts.id are primary keys
• accounts.holder_id is a foreign key to the users table.

A foreign key is a column on a table that matches a primary key in another table. This means that if we define these tables like that, our SQL engine would be able to understand that these two tables are related.

Creating the table with this information

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    surname TEXT
);

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    holder_id INTEGER,
    number TEXT,
    FOREIGN KEY(holder_id) REFERENCES users(id)
);

Something important to remember

Now that every account has a holder_id field, we must remember to not put in it invalid values.

An invalid value for holder_id would be any value that doesn't exist in the users table. For example, if we said the value was 5, but there was no such user with id=5.

In addition, if we delete a row from our users table, we must remember to delete the corresponding row(s) from the accounts table. Otherwise we might end up with accounts that nobody holds, or, even worse, new users being created and assigned accounts of old users.

TIP

Most RDBMs's (such as PostgreSQL) will automatically prevent you from inserting invalid data, and also prevent you from deleting rows that depend on other tables.

SQLite can also do this for you,^[1] but only if both of these are true:

• The Foreign Key support setting is on.
• SQLite has been compiled with support for foreign keys.

Another way of defining primary keys

Instead of defining a primary key inline, like this:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    surname TEXT
);

You can define it at the end of the CREATE TABLE query. This does the same:

CREATE TABLE users (
    id INTEGER,
    first_name TEXT,
    surname TEXT,
    PRIMARY KEY(id)
);

The benefit to defining the primary key using that syntax is that it makes it easier to define composite primary keys. We won't be using those yet, so we'll learn more about them later on in the course!

---

1. Enabling Foreign Key Support in SQLite (official documentation) (opens new window) ↩︎