SQL Primary Keys and Foreign Keys
The two concepts that make every join in this series possible — a unique identifier on one side, and a reference to it on the other.
What & Why
A primary key (PK) uniquely identifies each row in a table — no two rows can share the same value, and it can't be NULL. A foreign key (FK) is a column in one table holding a primary key value from another table, creating the actual link a join relies on.
See How It Works
BUSINESS QUESTION
Marketing uses campaigns.id as the parent key and leads.campaign_id as its declared foreign key.
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
SELECT
l.id AS lead_id,
l.campaign_id,
c.name AS campaign_name
FROM marketing.leads l
JOIN marketing.campaigns c
ON c.id = l.campaign_id
ORDER BY lead_id;RESULT — exact output from the displayed Queryflo rows
| lead_id | campaign_id | campaign_name |
|---|---|---|
| 301 | 1 | Spring Launch |
| 302 | 1 | Spring Launch |
| 303 | 2 | Retention Webinar |
| 304 | 3 | Finance Retargeting |
Every displayed foreign key resolves to its campaign primary key.
Now You Try
Practice this concept
Marketing wants each lead id and campaign foreign key beside the referenced campaign name, ordered by lead id.
Available schema
marketingPrefix tables with marketing.table_name.
lead_idcampaign_idcampaign_namemarketing.campaigns| Column | Type |
|---|---|
| id | integer |
| name | text |
| channel | text |
| spend | numeric |
| start_date | date |
| end_date | date |
| status | text |
| target_segment | text |
| legacy_id | text |
marketing.leads| Column | Type |
|---|---|
| id | integer |
| campaign_id | integer |
| text | |
| created_at | timestamp with time zone |
| qualified_at | timestamp with time zone |
| converted_at | timestamp with time zone |
| lead_score | integer |
| source | text |
| country | text |
| archive_status | text |
query.sql
Sign up free to try it on a real business scenario