Creating tables via SQL editor in Supabase - Try It Yourself

Creating tables via SQL editor

📖 Scenario: You are setting up a simple database for a small online bookstore using Supabase. You will create tables to store information about books and authors.

🎯 Goal: Create two tables named authors and books using the Supabase SQL editor. The authors table will store author details, and the books table will store book details including a reference to the author.

📋 What You'll Learn

Create a table called authors with columns author_id (integer, primary key), name (text, not null), and birth_year (integer).

Create a table called books with columns book_id (integer, primary key), title (text, not null), author_id (integer, foreign key referencing authors.author_id), and published_year (integer).

Use appropriate SQL syntax to define primary keys and foreign key constraints.

Ensure the tables are created successfully in the Supabase SQL editor.

💡 Why This Matters

🌍 Real World

Creating tables is the first step in building a database for any application, such as an online bookstore, to store and organize data efficiently.

💼 Career

Database creation and management skills are essential for cloud engineers, backend developers, and data professionals working with cloud databases like Supabase.

1

Create the authors table

Write a SQL statement to create a table called authors with these columns: author_id as an integer primary key, name as text and not null, and birth_year as an integer.

Supabase

-- Write the SQL to create the authors table here
-- Your code here

Need a hint?

Use CREATE TABLE authors (...) and define each column with its type and constraints.

2

Create the books table without foreign key

Write a SQL statement to create a table called books with these columns: book_id as an integer primary key, title as text and not null, author_id as an integer, and published_year as an integer. Do not add the foreign key constraint yet.

Supabase

-- Write the SQL to create the books table here without foreign key
-- Your code here

Need a hint?

Define the books table columns with correct types and primary key, but skip foreign key for now.

3

Add foreign key constraint to books table

Modify the books table creation SQL to add a foreign key constraint on author_id referencing authors.author_id.

Supabase

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    published_year INTEGER
    -- Your code here
);

Need a hint?

Add REFERENCES authors(author_id) after the author_id INTEGER column definition.

4

Finalize and verify table creation

Ensure both authors and books tables are created with correct columns, primary keys, and foreign key constraint in one SQL script.

Supabase

-- Write the full SQL script to create both authors and books tables with all constraints
-- Your code here

Need a hint?

Make sure the full SQL script includes both table creations with all constraints.