Creating tables via SQL editor in Supabase - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding SQL Table Creation Syntax

What will be the result of running this SQL command in Supabase SQL editor?

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);

Supabase

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);

A A table named 'users' is created but 'email' column allows duplicate values.

B A new table named 'users' is created with three columns: 'id' as auto-incrementing primary key, 'name' as required text, and 'email' as unique text.

C An error occurs because 'SERIAL' is not supported in Supabase SQL editor.

D The table is created but 'id' column is not set as primary key.

💡 Hint

Check the meaning of 'SERIAL', 'PRIMARY KEY', 'NOT NULL', and 'UNIQUE' in SQL.

This command creates a table 'users' with 'id' as an auto-incrementing primary key, 'name' as a required text field, and 'email' as a unique text field. 'SERIAL' is supported in PostgreSQL, which Supabase uses.

❓ Configuration

intermediate

Identifying Error in Table Creation

What error will this SQL command produce when run in Supabase SQL editor?

CREATE TABLE products (product_id INT PRIMARY KEY, name VARCHAR, price DECIMAL(10));

Supabase

CREATE TABLE products (product_id INT PRIMARY KEY, name VARCHAR, price DECIMAL(10));

A Error because PRIMARY KEY cannot be assigned to INT type.

B Error because DECIMAL requires two parameters for precision and scale.

C Syntax error because VARCHAR requires a length specification.

D Table created successfully with columns 'product_id', 'name', and 'price'.

💡 Hint

In PostgreSQL, VARCHAR without a length specifier is valid and equivalent to TEXT.

In PostgreSQL, VARCHAR without length is valid (unlimited length, equivalent to TEXT). DECIMAL(10) is valid (precision 10, scale 0). The table is created successfully.

❓ Architecture

advanced

Choosing Best Primary Key Design

You want to create a table 'orders' in Supabase to store order data. Which option is the best design for the primary key column?

A Use a column 'order_id' with type UUID generated by default with gen_random_uuid() function.

B Use a composite primary key of 'user_id' and 'order_date' columns.

C Use a text column 'order_code' where users manually enter unique codes.

D Use an integer column 'order_number' with auto-increment (SERIAL) as primary key.

💡 Hint

Consider uniqueness, scalability, and ease of use for primary keys.

UUIDs generated by gen_random_uuid() provide globally unique identifiers, avoid collisions, and are recommended for distributed systems like Supabase. Auto-increment integers can cause issues in distributed environments. Manual text keys risk duplicates. Composite keys add complexity.

❓ security

advanced

Preventing SQL Injection in Table Creation Scripts

Which practice helps prevent SQL injection when creating tables via Supabase SQL editor?

A Never accept user input directly in SQL commands; use parameterized queries or predefined scripts.

B Escape all single quotes in user input manually before inserting into SQL commands.

C Use dynamic SQL concatenation with user input but sanitize inputs with regex.

D Allow users to write their own SQL commands to create tables for flexibility.

💡 Hint

Think about how SQL injection happens and how to avoid it.

SQL injection occurs when user input is directly included in SQL commands. Using parameterized queries or predefined scripts avoids this risk. Manual escaping or regex sanitization is error-prone. Allowing users to write SQL is unsafe.

❓ service_behavior

expert

Effect of Dropping a Table with Dependent Foreign Keys

Given two tables in Supabase:

CREATE TABLE customers (id UUID PRIMARY KEY, name TEXT); CREATE TABLE orders (id UUID PRIMARY KEY, customer_id UUID REFERENCES customers(id));

What happens if you run DROP TABLE customers; without any additional options?

Supabase

DROP TABLE customers;

A The 'customers' table is dropped and 'orders' table remains unchanged with broken foreign key references.

B The 'customers' table is dropped and foreign key constraints in 'orders' are automatically removed.

C The command fails with an error because 'orders' table depends on 'customers' via foreign key.

D The 'customers' table is dropped and all rows in 'orders' with matching customer_id are automatically deleted.

💡 Hint

Consider how foreign key constraints affect dropping tables in PostgreSQL (used by Supabase).

PostgreSQL prevents dropping a table if other tables have foreign key constraints referencing it, unless you use CASCADE option. Without CASCADE, DROP TABLE fails with an error.