Primary and Foreign Keys

In Module 3, we discussed the concept of Primary and Foreign Keys and how they allow us to perform JOIN queries. Now, we will look at how to actually define them in our schema when building tables.

Primary and Foreign Keys are technically just special types of Constraints.

Defining a PRIMARY KEY

A Primary Key is essentially a combination of the NOT NULL and UNIQUE constraints, but with special significance: it represents the definitive row identifier.

There are two ways to define it. The easiest way is inline with the column:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

By adding PRIMARY KEY, you guarantee that the id column will never be empty, will never have duplicates, and will be heavily indexed for fast lookups.

Defining a FOREIGN KEY

A Foreign Key constraint guarantees Referential Integrity.

If the orders table has a user_id column, you want to guarantee that you cannot insert an order with user_id = 999 if user 999 does not actually exist in the users table! The Foreign Key constraint enforces this rule.

You define a Foreign Key at the end of your CREATE TABLE statement:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  total_amount DECIMAL(10,2),
  
  -- Define the relationship:
  FOREIGN KEY(user_id) REFERENCES users(id)
);

Let’s break down FOREIGN KEY(user_id) REFERENCES users(id):

• FOREIGN KEY(user_id): Look at the user_id column in this (orders) table…
• REFERENCES users(id): …and verify that any number placed here exists in the id column of the users table.

[!IMPORTANT] If you have a Foreign Key constraint in place, the database will also prevent you from deleting a user if they still have orders attached to them! This prevents “orphaned” records. To delete the user, you must first delete or reassign their orders.

Try establishing a secure relational link between tables in the interactive lab!