Let’s return to our example of the magazine subscriptions. Recall that we had three tables:
Each of these tables has a column that uniquely identifies each row of that table:
These special columns are called primary keys.
Primary keys have a few requirements:
- None of the values can be
- Each value must be unique (i.e., you can’t have two customers with the same
- A table can not have more than one primary key column.
Let’s reexamine the
customer_id (the primary key for
subscription_id (the primary key for
subscriptions) both appear in this.
When the primary key for one table appears in a different table, it is called a foreign key.
customer_id is a primary key when it appears in
customers, but a foreign key when it appears in
In this example, our primary keys all had somewhat descriptive names. Generally, the primary key will just be called
id. Foreign keys will have more descriptive names.
Why is this important? The most common types of joins will be joining a foreign key from one table with the primary key from another table. For instance, when we join
customers, we join on
customer_id, which is a foreign key in
orders and the primary key in
Suppose Columbia University has two tables in their database:
classestable contains information on the classes that the school offers. Its primary key is
studentstable contains information on all students in the school. Its primary key is
id. It contains the foreign key
class_id, which corresponds to the primary key of
Perform an inner join of
students using the primary and foreign keys described above, and select all the columns.