Bird
Raised Fist0
Supabasecloud~5 mins

Data types and constraints in Supabase

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction

Data types tell the database what kind of information to expect. Constraints make sure the data follows rules to keep it correct and useful.

When creating a new table to store user information like names and ages.
When you want to make sure an email address is always unique in your database.
When you need to prevent empty values in important fields like passwords.
When you want to limit a number to a certain range, like age between 0 and 120.
When you want to link data between tables, like orders linked to customers.
Syntax
Supabase
CREATE TABLE table_name (
  column_name data_type CONSTRAINTS,
  ...
);

Data types define the kind of data (e.g., text, number, date).

Constraints add rules like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY.

Examples
This creates a users table with an auto-increment id, unique email that cannot be empty, and age that must be zero or more.
Supabase
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT CHECK (age >= 0)
);
Defines products with a unique ID, a name that cannot be empty, and a price that must be positive with two decimals.
Supabase
CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10,2) CHECK (price > 0)
);
Sample Program

This table stores employee data with rules: each employee has a unique ID, names and email cannot be empty, emails must be unique, hire date is required, and salary cannot be negative.

Supabase
CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  hire_date DATE NOT NULL,
  salary NUMERIC(8,2) CHECK (salary >= 0)
);
OutputSuccess
Important Notes

Use the right data type to save space and improve speed.

Constraints help keep your data clean and prevent mistakes.

Always test your constraints by trying to insert bad data to see if they block it.

Summary

Data types define what kind of data each column holds.

Constraints enforce rules to keep data accurate and consistent.

Combining both helps build reliable and safe databases.

Practice

(1/5)
1. What is the main purpose of data types in a Supabase database table?
easy
A. To specify what kind of data each column can store
B. To control user access to the database
C. To create visual reports from data
D. To backup the database automatically

Solution

  1. Step 1: Understand data types role

    Data types define the kind of data a column can hold, like numbers or text.

  2. Step 2: Differentiate from other features

    Access control, reports, and backups are unrelated to data types.

  3. Final Answer:

    To specify what kind of data each column can store -> Option A

  4. Quick Check:

    Data types = specify data kind [OK]
Hint: Data types tell what data fits in each column [OK]
Common Mistakes:
  • Confusing data types with user permissions
  • Thinking data types create reports
  • Assuming data types handle backups
2. Which of the following is the correct way to define a column with a NOT NULL constraint in Supabase SQL?
easy
A. username TEXT NOT NULL
B. username NOT NULL TEXT
C. NOT NULL username TEXT
D. TEXT username NOT NULL

Solution

  1. Step 1: Recall SQL column definition order

    The correct order is: column name, data type, then constraints.

  2. Step 2: Match the syntax

    username TEXT NOT NULL follows this order: username TEXT NOT NULL.

  3. Final Answer:

    username TEXT NOT NULL -> Option A

  4. Quick Check:

    Column name + type + constraint = correct syntax [OK]
Hint: Column name first, then type, then constraints [OK]
Common Mistakes:
  • Placing NOT NULL before the column name
  • Swapping data type and constraint order
  • Using incorrect keyword order
3. Given the table definition:
CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT);

What happens if you try to insert two rows with the same email?
medium
A. The second insert overwrites the first row
B. Both rows are inserted successfully
C. The second insert fails due to UNIQUE constraint violation
D. The database ignores the UNIQUE constraint

Solution

  1. Step 1: Understand UNIQUE constraint effect

    The UNIQUE constraint prevents duplicate values in the email column.

  2. Step 2: Predict insert behavior

    Inserting a duplicate email causes the second insert to fail with an error.

  3. Final Answer:

    The second insert fails due to UNIQUE constraint violation -> Option C

  4. Quick Check:

    UNIQUE prevents duplicates = second insert fails [OK]
Hint: UNIQUE means no duplicate values allowed [OK]
Common Mistakes:
  • Assuming duplicates are allowed
  • Thinking second insert overwrites first
  • Ignoring constraint errors
4. You wrote this table definition:
CREATE TABLE products (id INT PRIMARY KEY, name TEXT NOT NULL, price TEXT);

What is the main issue with this definition?
medium
A. The id column cannot be INT
B. The price column should not be TEXT for numeric values
C. The name column cannot have NOT NULL constraint
D. PRIMARY KEY cannot be on id column

Solution

  1. Step 1: Check data type appropriateness

    Price should be a numeric type (like INT or NUMERIC), not TEXT, to store numbers properly.

  2. Step 2: Validate other columns

    id as INT with PRIMARY KEY and name as TEXT NOT NULL are valid.

  3. Final Answer:

    The price column should not be TEXT for numeric values -> Option B

  4. Quick Check:

    Price needs numeric type, not text [OK]
Hint: Use numeric types for numbers, not TEXT [OK]
Common Mistakes:
  • Using TEXT for numeric columns
  • Thinking PRIMARY KEY can't be INT
  • Believing NOT NULL is invalid on text
5. You want to create a table to store user profiles with these rules:
- Each user has a unique ID
- Email must be unique and not empty
- Age must be a positive integer or NULL if unknown

Which table definition correctly applies data types and constraints?
hard
A. 
CREATE TABLE profiles (user_id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT CHECK (age > 0) OR age IS NULL);
B. 
CREATE TABLE profiles (user_id INT, email TEXT NOT NULL, age INT NOT NULL CHECK (age >= 0));
C. 
CREATE TABLE profiles (user_id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT CHECK (age > 0) NOT NULL);
D. 
CREATE TABLE profiles (user_id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT CHECK (age > 0));

Solution

  1. Step 1: Check unique ID and email constraints

    CREATE TABLE profiles (user_id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT CHECK (age > 0));
    uses UUID PRIMARY KEY for unique ID and email TEXT UNIQUE NOT NULL, which is correct.

  2. Step 2: Validate age constraint

    CREATE TABLE profiles (user_id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT CHECK (age > 0));
    uses CHECK (age > 0) allowing NULL by default, which fits the requirement.

  3. Step 3: Review other options

    CREATE TABLE profiles (user_id INT, email TEXT NOT NULL, age INT NOT NULL CHECK (age >= 0));
    lacks PRIMARY KEY and UNIQUE on email, makes age NOT NULL; C adds NOT NULL to age preventing NULL values; D has syntax error in CHECK constraint.

  4. Final Answer:

    CREATE TABLE profiles (user_id UUID PRIMARY KEY, email TEXT UNIQUE NOT NULL, age INT CHECK (age > 0)); -> Option D

  5. Quick Check:

    UUID PK, UNIQUE email, age > 0 or NULL = correct [OK]
Hint: Use CHECK for age and UNIQUE + NOT NULL for email [OK]
Common Mistakes:
  • Forgetting PRIMARY KEY on user_id
  • Making age NOT NULL when it can be NULL
  • Writing invalid CHECK constraints
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard