Data types and constraints in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Data Types in Supabase

Which data type in Supabase is best suited to store a user's email address to ensure it is stored as text and can be indexed efficiently?

A TEXT without length limit

B VARCHAR with a length limit

C INTEGER

D BOOLEAN

💡 Hint

Think about storing strings with a maximum length for emails.

VARCHAR with a length limit is ideal for emails because it stores text efficiently and supports indexing, unlike TEXT which has no length limit and may be less efficient for indexing.

❓ Configuration

intermediate

Applying NOT NULL Constraint

Given a Supabase table for users, which SQL statement correctly adds a NOT NULL constraint to the 'username' column?

A ALTER TABLE users MODIFY username NOT NULL;

B ALTER TABLE users CHANGE username username NOT NULL;

C ALTER TABLE users ALTER COLUMN username SET NOT NULL;

D ALTER TABLE users ADD CONSTRAINT username NOT NULL;

💡 Hint

Focus on the correct syntax for altering a column constraint in PostgreSQL (used by Supabase).

In PostgreSQL, the correct syntax to add a NOT NULL constraint is ALTER TABLE ... ALTER COLUMN ... SET NOT NULL.

❓ Architecture

advanced

Choosing Data Types for Performance

You are designing a Supabase table to store product prices with high precision. Which data type should you choose to avoid rounding errors and maintain accuracy?

A FLOAT

B TEXT

C INTEGER

D DECIMAL or NUMERIC

💡 Hint

Consider data types that store exact numeric values rather than approximate.

DECIMAL or NUMERIC types store exact values with fixed precision, avoiding rounding errors common with FLOAT.

❓ security

advanced

Enforcing Unique Constraints

Which SQL command correctly creates a unique constraint on the 'email' column in a Supabase table to prevent duplicate emails?

A ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

B ALTER TABLE users MODIFY email UNIQUE;

C ALTER TABLE users ADD UNIQUE (email);

D CREATE UNIQUE INDEX email ON users(email);

💡 Hint

Look for the syntax that names the constraint explicitly.

Adding a named unique constraint uses ADD CONSTRAINT with UNIQUE keyword in PostgreSQL (Supabase).

❓ service_behavior

expert

Behavior of CHECK Constraints on Insert

Consider a Supabase table with a CHECK constraint: CHECK (age >= 18). What happens if you try to insert a row with age = 16?

A The insert fails and raises an error.

B The insert succeeds but sets age to NULL.

C The insert succeeds and ignores the constraint.

D The insert succeeds but logs a warning.

💡 Hint

Think about how constraints enforce data validity strictly.

CHECK constraints prevent invalid data; inserting a row violating the constraint causes an error and the insert fails.