Primary keys and foreign keys in Supabase

Introduction

Primary keys and foreign keys help keep data organized and connected in databases. They make sure each piece of data is unique and related to the right information.

When you want to make sure each user in your app has a unique ID.

When you need to link orders to the customers who made them.

When you want to connect comments to the posts they belong to.

When you want to avoid duplicate entries in a table.

When you want to easily find related data across tables.

Syntax

Supabase

CREATE TABLE table_name (
  column_name data_type PRIMARY KEY,
  other_column data_type,
  foreign_key_column data_type REFERENCES other_table(primary_key_column)
);

The PRIMARY KEY ensures each row is unique.

The REFERENCES keyword creates a foreign key linking to another table's primary key.

Examples

This creates a users table with a unique id for each user.

Supabase

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

This creates an orders table where each order links to a user by user_id.

Supabase

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  amount NUMERIC
);

This links comments to posts using a foreign key.

Supabase

CREATE TABLE comments (
  comment_id SERIAL PRIMARY KEY,
  post_id INTEGER REFERENCES posts(id),
  content TEXT
);

Even with one column, primary key ensures uniqueness.

Supabase

CREATE TABLE empty_table (
  id SERIAL PRIMARY KEY
);

Sample Program

This example creates two tables: customers and purchases. Each customer has a unique ID. Each purchase links to a customer using a foreign key. Then it inserts sample data and shows how to get purchase info with customer names.

Supabase

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE purchases (
  purchase_id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(customer_id),
  purchase_amount NUMERIC NOT NULL
);

-- Insert sample data
INSERT INTO customers (customer_name) VALUES ('Alice'), ('Bob');
INSERT INTO purchases (customer_id, purchase_amount) VALUES (1, 100.50), (2, 200.75);

-- Query to show purchases with customer names
SELECT purchases.purchase_id, customers.customer_name, purchases.purchase_amount
FROM purchases
JOIN customers ON purchases.customer_id = customers.customer_id;

Important Notes

Primary key lookup is very fast because it uniquely identifies rows.

Foreign keys help keep data consistent by preventing invalid links.

Common mistake: forgetting to add a primary key, which can cause duplicate rows.

Use primary keys to identify rows; use foreign keys to connect tables.

Summary

Primary keys make each row unique in a table.

Foreign keys link rows in one table to rows in another.

They help keep your data organized and connected.