Primary keys and foreign keys in Supabase - Deep Dive

Overview - Primary keys and foreign keys

What is it?

Primary keys and foreign keys are ways to organize and connect data in databases. A primary key is a unique identifier for each record in a table, like a name tag that no one else has. A foreign key is a link from one table to another, showing how records relate, like a reference to a friend's name in your contact list. These keys help keep data organized and connected.

Why it matters

Without primary and foreign keys, data would be messy and hard to find or connect. Imagine a phone book without unique names or addresses; you wouldn't know who is who or how people are related. These keys make sure data stays accurate, easy to search, and connected across different tables, which is essential for apps and websites to work correctly.

Where it fits

Before learning about primary and foreign keys, you should understand what a database and tables are. After this, you can learn about more advanced database concepts like indexing, joins, and normalization, which build on these keys to make data handling faster and more efficient.

Mental Model

Core Idea

Primary keys uniquely identify records, and foreign keys create connections between tables to relate data.

Think of it like...

Think of a primary key as a unique ID card for each person in a school, and a foreign key as a note in one student's file that points to their teacher's ID card, linking them together.

┌─────────────┐       ┌─────────────┐
│ Students    │       │ Teachers    │
│─────────────│       │─────────────│
│ Student_ID  │──────►│ Teacher_ID  │
│ Name        │       │ Name        │
│ Teacher_ID  │◄──────│             │
└─────────────┘       └─────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Tables and Records

Concept: Learn what tables and records are in a database.

A table is like a spreadsheet with rows and columns. Each row is a record, representing one item or person. Columns are fields describing the record, like name or age.

Result

You can see how data is stored in rows and columns, ready to be organized.

Knowing tables and records is essential because keys work by identifying and linking these rows.

2

Foundation

What is a Primary Key?

Concept: Introduce the idea of a unique identifier for each record.

A primary key is a column or set of columns that uniquely identifies each row in a table. No two rows can have the same primary key value. For example, a student ID number that no other student shares.

Result

You understand how to pick a unique value to find any record quickly.

Understanding uniqueness prevents confusion and errors when searching or updating data.

3

Intermediate

What is a Foreign Key?

Concept: Learn how tables connect using foreign keys.

A foreign key is a column in one table that points to a primary key in another table. It creates a relationship between the two tables. For example, a student's record might have a teacher's ID as a foreign key to show who teaches them.

Result

You see how data in different tables can be linked to represent real-world relationships.

Knowing foreign keys helps you design databases that reflect connected information naturally.

4

Intermediate

Enforcing Data Integrity with Keys

🤔Before reading on: Do you think foreign keys allow any value or only values that exist in the linked table? Commit to your answer.

Concept: Understand how keys keep data accurate and consistent.

Primary keys must be unique and not empty. Foreign keys must match an existing primary key or be empty (if allowed). This prevents mistakes like linking to a non-existent record.

Result

You learn how databases prevent errors by enforcing rules on keys.

Understanding these rules helps avoid data mistakes that can cause bugs or wrong information.

5

Advanced

Using Keys in Supabase SQL

🤔Before reading on: Do you think you can create primary and foreign keys directly in Supabase SQL commands? Commit to your answer.

Concept: Learn how to define primary and foreign keys using Supabase SQL syntax.

In Supabase, you create tables with primary keys using 'PRIMARY KEY' in the column definition. Foreign keys are added with 'REFERENCES' pointing to another table's primary key. Example: CREATE TABLE teachers ( teacher_id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE students ( student_id SERIAL PRIMARY KEY, name TEXT NOT NULL, teacher_id INT REFERENCES teachers(teacher_id) );

Result

You can write SQL commands to create tables with keys in Supabase.

Knowing the exact syntax lets you build reliable databases that Supabase understands and enforces.

6

Expert

Handling Key Constraints in Production

🤔Before reading on: Do you think removing a record referenced by a foreign key is allowed by default? Commit to your answer.

Concept: Explore how key constraints affect data changes and how to manage them safely.

Foreign key constraints prevent deleting or changing a primary key if related foreign keys exist, to avoid broken links. You can set actions like CASCADE to delete related records automatically or RESTRICT to block deletion. Managing these rules carefully avoids data loss or orphaned records.

Result

You understand how to control data integrity during updates and deletions in real systems.

Knowing constraint behaviors prevents accidental data corruption and supports safe database operations.

Under the Hood

Databases store tables as structured files or memory blocks. Primary keys create an index, a fast lookup system, to find records quickly. Foreign keys are enforced by the database engine checking that any value in the foreign key column matches a primary key in the linked table. This checking happens during data insertion, update, or deletion to maintain consistency.

Why designed this way?

Primary and foreign keys were designed to organize data efficiently and prevent errors. Before keys, databases were slow and prone to mistakes like duplicate or broken data links. The design balances speed, accuracy, and ease of use, allowing complex data relationships to be managed reliably.

┌───────────────┐       ┌───────────────┐
│ Primary Key   │       │ Foreign Key   │
│ Index Table   │◄──────│ Validation    │
│ (Fast Search) │       │ Checks        │
└──────┬────────┘       └──────┬────────┘
       │                       │
       │                       │
┌──────▼────────┐       ┌──────▼────────┐
│ Data Storage  │       │ Related Table │
│ (Records)     │       │ (Referenced)  │
└───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does a foreign key column always have to contain a value? Commit yes or no.

Common Belief: Foreign keys must always have a value pointing to another table.

Reality: Foreign keys can be empty (NULL) if the database allows it, meaning no link is set.

Why it matters: Assuming foreign keys must always have values can lead to forcing incorrect data or losing flexibility in representing optional relationships.

Quick: Can two different tables have the same primary key values? Commit yes or no.

Common Belief: Primary key values must be unique across the entire database.

Reality: Primary keys only need to be unique within their own table, not across all tables.

Why it matters: Believing primary keys must be globally unique can cause unnecessary complexity and confusion in database design.

Quick: If you delete a record with a primary key, do all related foreign key records get deleted automatically? Commit yes or no.

Common Belief: Deleting a primary key record automatically deletes all related foreign key records.

Reality: By default, deletion is blocked unless you set rules like CASCADE to delete related records automatically.

Why it matters: Misunderstanding this can cause accidental data loss or broken references if constraints are not properly configured.

Quick: Are primary keys always numeric IDs? Commit yes or no.

Common Belief: Primary keys must be numeric IDs like serial numbers.

Reality: Primary keys can be any data type that uniquely identifies a record, such as text or UUIDs.

Why it matters: Limiting primary keys to numbers restricts design options and can reduce database flexibility.

Expert Zone

1

Composite primary keys use multiple columns together to uniquely identify a record, which is useful when no single column is unique alone.

2

Foreign key constraints can be deferred, meaning the database checks them only at transaction end, allowing complex multi-step updates.

3

Using UUIDs as primary keys improves uniqueness across distributed systems but can impact index performance compared to integers.

When NOT to use

Avoid foreign keys in very high-scale distributed databases where latency and partition tolerance matter more than strict consistency. Instead, use application-level joins or eventual consistency patterns.

Production Patterns

In production, keys are combined with indexing strategies and caching to optimize performance. Soft deletes with flags instead of hard deletes are common to preserve data integrity while allowing recovery.

Connections

Graph Theory

Builds-on

Understanding primary and foreign keys helps grasp graph edges and nodes, where tables are nodes and foreign keys are edges connecting them.

Object-Oriented Programming (OOP)

Same pattern

Primary keys and foreign keys relate like objects and references in OOP, where objects have unique IDs and hold references to other objects.

Social Networks

Builds-on

Keys in databases mirror how social networks connect people with unique profiles (primary keys) and friendships or follows (foreign keys).

Common Pitfalls

#1 Trying to insert a record with a foreign key value that does not exist in the referenced table.

Wrong approach:

INSERT INTO students (student_id, name, teacher_id) VALUES (1, 'Alice', 999);

Correct approach:

INSERT INTO students (student_id, name, teacher_id) VALUES (1, 'Alice', 1);

Root cause:

Not checking that the foreign key value exists in the referenced table before inserting.

#2 Defining a primary key column that allows duplicate or NULL values.

Wrong approach:

CREATE TABLE books (id INT, title TEXT, PRIMARY KEY (id)); -- but id column allows NULL or duplicates

Correct approach:

CREATE TABLE books (id INT NOT NULL UNIQUE, title TEXT, PRIMARY KEY (id));

Root cause:

Misunderstanding that primary keys must be unique and not null.

#3 Deleting a record referenced by foreign keys without handling constraints.

Wrong approach:

DELETE FROM teachers WHERE teacher_id = 1;

Correct approach:

DELETE FROM teachers WHERE teacher_id = 1 CASCADE;

Root cause:

Ignoring foreign key constraints that prevent deletion of referenced records.

Key Takeaways

Primary keys uniquely identify each record in a table and must be unique and not null.

Foreign keys link records between tables, creating relationships that reflect real-world connections.

Databases enforce rules on keys to keep data accurate and prevent broken links or duplicates.

Proper use of keys improves data organization, search speed, and integrity in applications.

Understanding key constraints and behaviors is essential for safe and efficient database design and operation.