Primary keys and foreign keys

A primary key identifies each row in a table.

Because a primary key is used to identify a particular row, its values must be:

• unique

• not NULL

For example, customer 3 can be identified by id = 3, and no other row in customers can have the same id.

If we try to insert another customer with an id of 3:

PostgreSQL rejects the insert because a row with that primary-key value already exists.

The primary key therefore doesn't just help us identify rows. PostgreSQL also enforces the rule that every primary-key value uniquely identifies one row.

Now look at the orders table:

You should see:

Each order contains a customer_id.

For example, order 1 has customer_id = 2. This tells us that the order belongs to customer 2.

The customer_id column in orders is a foreign key that references customers.id.

The reference is:

A simplified view looks like this:

The primary key identifies the customer.

The foreign key stores the ID of the customer associated with an order.

A foreign key doesn't just describe a relationship between tables. PostgreSQL also enforces it.

For example, there is no customer with an id of 999999.

If we try to create an order for that customer:

PostgreSQL rejects the insert because customer_id = 999999 doesn't match a valid referenced row in customers.

Without the foreign key, the database could contain an order that refers to a customer that doesn't exist. The foreign key prevents this and keeps the relationship between the tables consistent. This is called referential integrity.

In this relationship:

orders contains the foreign key, so it is the referencing table.

customers contains the key being referenced, so it is the referenced table.

You may also hear these described informally as the child and parent tables.

In our database, orders.customer_id references the primary key customers.id.

That is the most common pattern, but a foreign key can also reference columns whose values are guaranteed to be unique through an appropriate unique constraint.

The important point is that the referenced value must identify a valid row.

When you create a primary key, PostgreSQL automatically creates a unique B-tree index for it. That is why customers.id already has an index even though we didn't create one separately.

The primary key and the index serve different purposes.

The primary key expresses and enforces the rule that each customer must have a unique, non-NULL identifier. The index gives PostgreSQL an efficient structure for enforcing that uniqueness and looking up rows by the key.

A foreign key is different. PostgreSQL does not automatically create an index on the column containing the foreign key. For example, orders.customer_id is a foreign key, but it doesn't automatically receive an index just because of that relationship.

You saw why indexing such a column can sometimes be useful in the indexes and joins chapters.