Data types and constraints in Supabase

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)
);

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.