How to Make a Composite Key in Supabase

Creating a composite key in Supabase involves defining two or more columns as the primary key for a table, ensuring the combined values of these columns are unique for each row. This method is fundamental for accurately modeling complex relationships and ensuring data integrity within your PostgreSQL database, which powers Supabase.

What is a Composite Key?

A composite key is a primary key that consists of two or more columns. Instead of a single column uniquely identifying each record, the combination of values from multiple columns provides that uniqueness. This is particularly useful when no single column naturally serves as a unique identifier on its own.

Key characteristics:

Uniqueness: The combined values of all columns in the composite key must be unique across all rows in the table.
Non-null: Each column participating in the composite key cannot contain NULL values.
Data Integrity: It enforces specific business rules by ensuring related data is correctly linked and uniquely identified.

How to Create a Composite Key in Supabase

Supabase, being built on PostgreSQL, allows you to create composite keys using standard SQL commands, which can be executed through the Supabase Studio's SQL Editor or via database migrations. You can define multiple columns as primary keys when creating or modifying a table.

Using Supabase Studio's SQL Editor (Recommended Method)

The most direct and flexible way to create composite keys is by executing SQL commands in the Supabase Studio's SQL Editor.

1. When Creating a New Table

When you're designing a new table, you can specify a composite primary key directly within the CREATE TABLE statement.

Example:
Imagine you're tracking book_authors, where a book can have multiple authors, and an author can write multiple books. The combination of book_id and author_id uniquely identifies each relationship.

CREATE TABLE book_authors (
    book_id UUID REFERENCES books(id),
    author_id UUID REFERENCES authors(id),
    royalties_percentage DECIMAL(5,2),
    PRIMARY KEY (book_id, author_id)
);

In this example:

book_id and author_id are both foreign keys referencing books and authors tables, respectively.
PRIMARY KEY (book_id, author_id) declares that the combination of these two columns forms the unique identifier for each row in the book_authors table.

2. When Modifying an Existing Table

If you have an existing table and need to add a composite primary key, you can use the ALTER TABLE statement. This is often necessary when refactoring schemas or applying changes to a production database.

Example:
Let's use the common scenario of an order_details table where order_id and product_id together uniquely identify a line item in an order.

ALTER TABLE order_details
ADD CONSTRAINT pk_orderdetails PRIMARY KEY (order_id, product_id);

Explanation:

ALTER TABLE order_details: Specifies the table to be modified.
ADD CONSTRAINT pk_orderdetails: Adds a new constraint named pk_orderdetails. Choosing a descriptive name helps with database management.
PRIMARY KEY (order_id, product_id): Defines the composite primary key using the order_id and product_id columns. This ensures that no two rows will have the same combination of order_id and product_id.

Using Supabase Studio's Table Editor (UI)

While the SQL editor offers maximum control, you can also define composite keys through the graphical user interface (UI) in Supabase Studio when creating or modifying a table.

Steps:

Navigate to the Table Editor in Supabase Studio.
Select the table you want to modify or click New Table to create one.
Add the necessary columns.
For each column that you want to be part of the composite primary key, you typically select the Primary Key checkbox. When you select multiple columns as primary keys, Supabase (PostgreSQL) automatically recognizes them as forming a composite primary key. The UI handles the underlying SQL generation for you.
Save your table changes.

Why Use Composite Keys?

Composite keys are powerful for various database design patterns:

Natural Keys: When a set of existing attributes naturally and uniquely identifies an entity (e.g., (flight_number, flight_date) for a flight schedule).
Junction Tables (Many-to-Many Relationships): They are essential for resolving many-to-many relationships, where an intermediary table links two other tables. The primary key of the junction table is often a composite of the foreign keys from the linked tables (e.g., book_authors example above).
Data Granularity: They help define uniqueness at a finer level, ensuring that specific combinations of data are not duplicated.

Practical Considerations

Automatic Indexing

When you define a primary key (whether single or composite) in PostgreSQL, an index is automatically created on those columns. This index significantly speeds up queries that filter or join on the primary key columns, which is a major performance benefit.

Foreign Key Relationships with Composite Keys

You can define foreign keys that reference a composite primary key in another table. The foreign key in the referencing table must also consist of multiple columns that match the order and data types of the composite primary key.

Example:
If book_authors has PRIMARY KEY (book_id, author_id), another table, say author_royalties_payments, might reference it:

CREATE TABLE author_royalties_payments (
    payment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    book_id UUID,
    author_id UUID,
    payment_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (book_id, author_id) REFERENCES book_authors (book_id, author_id)
);

Performance

While composite keys are great for data integrity, very wide composite keys (many columns) can have a minor impact on performance due to larger index sizes and more data to compare during lookups. However, for most common scenarios, this impact is negligible and outweighed by the benefits of correct data modeling.

Example Scenario: Order Details

Let's summarize the order_details example from earlier to highlight its utility:

Feature	Description
Table	`order_details`
Columns	`order_id`, `product_id`, `quantity`, `price`
Composite Primary Key	`(order_id, product_id)`
Purpose	Ensures that for a given `order_id`, each `product_id` appears only once, preventing duplicate line items.
Constraint Name	`pk_orderdetails` (as used in the `ALTER TABLE` example)

This structure guarantees that each specific product within a specific order is uniquely identified, which is crucial for inventory management, billing, and order processing.

Ova