# 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
accounts table have a unique
id field for each column, like so:
Let's focus on data for a particular person, let say "John Smith":
We can tell his account is that with
id=3 because the
We would say that:
accounts.idare primary keys
accounts.holder_idis a foreign key to the
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
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.
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, 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!