Primary Key vs Foreign Key: How Tables Connect

A primary key is a column that pinpoints exactly one row in a table, and a foreign key is a column that holds another table's primary key values to link the two tables. Thanks to these two columns, you can keep members and orders in separate tables and still find out "whose order is this?" at any time. When I started learning SQL, the terms primary key and foreign key were the least familiar to me. In this post, let's look at what the two keys are and how tables relate to each other, using a small stationery shop example. Previous post: Relational Database Structure: Tables, Rows, Columns, Schema

SQL Basics series 2/12 · Full curriculum

Today's concept

If you've used VLOOKUP in Excel, you've already used a key. When you take the "member ID" from an orders sheet and look up the name on a members sheet, that member ID is doing the job of a key. As an analogy, a primary key is like a national ID number that belongs to only one person, and a foreign key is that number written on a shipping label. You can find the recipient from the label alone, but a label with a number that doesn't exist has nowhere to go.

According to the PostgreSQL constraints documentation, a primary key's values must be both unique and not null, and a table can have at most one primary key. The same page describes a foreign key as a constraint that "the values in a column must match the values appearing in some row of another table," and says this maintains referential integrity (the state where every referenced value actually exists) between the two tables.

Learning by example

Let's attach an orders table to the stationery shop from the last post (sample data).

members

orders

orders has no member names, only member_id. If you want to know who placed order 105, take member_id 1 and find row 1 in members, and you get Haneul Kim. If a name changes, you only fix it in one place, members. PostgreSQL calls the pointing side, like orders, the referencing table and the pointed-to side, like members, the referenced table, while the SQLite documentation calls them the child table and the parent table.

Concept figure: the foreign key in the orders table points to the primary key in the members table (figure labels in Korean)

Written as a schema, it looks like this. We'll cover the syntax from part 6, so for now just notice the two words PRIMARY KEY and REFERENCES.

CREATE TABLE members (
    member_id  INTEGER PRIMARY KEY,
    name       VARCHAR(50),
    city       VARCHAR(20)
);
 
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    member_id   INTEGER REFERENCES members (member_id),
    ordered_on  DATE
);

With this in place, if you try to insert an order for member 9, who isn't in members, PostgreSQL rejects it with a foreign key violation error. Trying to add another member with the existing ID 2 is blocked too, by the primary key.

How do tables relate to each other?

The MySQL glossary explains that a database encodes and enforces relationships such as one-to-one, one-to-many and many-to-one. When you're just starting out, splitting them into three types is enough.

The example above is 1:N. Haneul Kim has three orders, Doyun Lee and Seoyeon Park have one each, and Jiwoo Choi has none yet. The foreign key goes on the "many" side because, as we saw in the last post, the number of columns is fixed, so you can't decide how many order-number columns to set aside in the members table.

So what if one order holds several products and one product appears in several orders? The PostgreSQL documentation shows an example of building this kind of many-to-many relationship with a table that has two foreign keys.

order_items (junction table)

product_id points to a products table holding pens (1), notebooks (2) and highlighters (3). Order 101 has two products, and pens appear in orders 101 and 103. This table uses the two columns together, PRIMARY KEY (order_id, product_id), as its primary key, because each column alone repeats but the same combination of the two never appears twice. The SQLite documentation calls a key made of several columns like this a composite key.

Concept figure: the many-to-many relationship between orders and products is bridged by the order_items table in the middle (figure labels in Korean)

How do you choose a primary key?

The MySQL glossary advises considering arbitrary sequence numbers (synthetic keys) rather than meaningful values taken from elsewhere (natural keys) for primary keys, and explains that if a natural key changes, there's a lot to fix.

If I were moving an Excel sheet into a table, I'd try to choose the primary key with these criteria.

1. Does this column's value alone identify exactly one row? "Name" won't work because people can share names.
2. Are there no empty rows? A primary key doesn't accept NULL.
3. Will the value never change? If it might, keep a separate sequence number and put only a UNIQUE constraint on the email so it can't repeat.

The PostgreSQL documentation notes that relational database theory says every table must have a primary key, but PostgreSQL doesn't enforce this rule, though it's usually best to follow it.

Practice problems

1. (Easy) In the orders table above, which column is the primary key and which is the foreign key? Which table does the foreign key point to?

2. (Medium) Which of these rows would be blocked if you tried to insert them into orders? Choose all that apply. (a) (106, 4, 2026-10-25) (b) (107, 9, 2026-10-26) (c) (103, 2, 2026-10-27)

3. (Medium) An online course site has a "students" table and a "courses" table. One student takes many courses, and one course has many students. What kind of relationship is this, and how would you design the tables?

Easy to confuse

• A foreign key column can hold NULL: According to the PostgreSQL documentation, if a foreign key column is NULL, it doesn't violate the constraint even without a matching row. If you want to block "orders without a member," add NOT NULL to the foreign key column as well.
• Deleting a referenced row is blocked by default: Trying to delete member 2, who still has orders, raises an error. When you create the foreign key, you can choose actions such as ON DELETE CASCADE (delete the orders too) or SET NULL (set the foreign key to NULL). The PostgreSQL documentation explains that when two tables represent independent objects, blocking the deletion is the more appropriate choice, and members and orders can be seen as that kind of case.
• How strictly foreign keys are enforced differs by product: In SQLite, foreign key checks are off by default and must be turned on for each connection with PRAGMA foreign_keys = ON;. Don't assume a foreign key is always checked just because you wrote it; I recommend checking the documentation for the product you use.

Concept figure: a foreign key blocks only member IDs that don't exist, not empty values (figure labels in Korean)

Wrapping up

A primary key is the column that tells rows apart, and a foreign key is the column that points to that primary key to link two tables. With these two, you express 1:1, 1:N and N:M relationships. It may help to think of it this way: the member ID you used to look up with VLOOKUP in Excel becomes a rule the database enforces. I'm studying along with this series myself, so please refer to the full curriculum. In the next post, we'll cover normalization: why tables get split into several.

Related posts

• Relational Database Structure: Tables, Rows, Columns, Schema - SQL Basics part 1, tables and schemas
• What Is a Database? How It Differs from an Excel Sheet - SQL Basics part 0
• VLOOKUP vs XLOOKUP: Which Excel Lookup Function to Use - How to pull information from another table using a key value in Excel