Module 4 — Creating Tables

1. CREATE TABLE — The Basics

What it is: CREATE TABLE is a DDL command that defines a new table in your database. A table is a structured grid of rows and columns, similar to a spreadsheet, where each column has a specific data type and each row represents a single record.

Why we use it: Tables are the fundamental storage unit in a relational database. Without tables, there's nowhere to store data. The structure you define (columns, types, constraints) determines what data can be stored and how it's validated.

When we use it: At the start of every project when designing the database schema, or when adding new features that require new data storage.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INTEGER NOT NULL,
    description TEXT,
    in_stock BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

Breaking it down

2. PostgreSQL Data Types

What it is: Data types define what kind of data a column can hold — numbers, text, dates, booleans, and more. PostgreSQL has a rich set of native data types optimized for different use cases.

Why we use it: Choosing the right data type ensures data integrity (you can't accidentally store text in a number column), optimizes storage (integers take less space than text), and enables proper operations (you can do math on numbers but not on text).

When we use it: Every time you create a column, you must choose a data type. The choice affects storage, performance, and what operations are valid.

Data Types Overview

3. Constraints — The Rules of the Road

What it is: Constraints are rules enforced by the database that automatically validate data before it's inserted or updated. They act as a safety net, preventing invalid data from entering your system.

Why we use it: Without constraints, your application code would need to validate every piece of data — and bugs in that code would corrupt your database. Constraints provide a last line of defense at the database level.

When we use it: On every table, for every column that has rules about what data is valid. Common constraints include PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT.

PRIMARY KEY

What it is: A PRIMARY KEY is a constraint that uniquely identifies each row in a table. It combines two rules: UNIQUE (no two rows can have the same value) and NOT NULL (the value cannot be empty).

Why we use it: Every table needs a way to uniquely identify rows — for updates, deletes, joins, and relationships. The primary key is the standard way to do this.

When we use it: On every table, typically on an auto-incrementing id column.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

NOT NULL

What it is: The NOT NULL constraint prevents a column from accepting empty (NULL) values. It ensures that every row must have a value for this column.

Why we use it: Some data is essential — a user must have an email, an order must have a total. NOT NULL enforces this at the database level, preventing bugs where critical data is missing.

When we use it: On columns that are required for business logic — names, emails, prices, foreign keys, and other essential data.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total NUMERIC NOT NULL
);

UNIQUE

What it is: The UNIQUE constraint ensures that all values in a column are different — no two rows can have the same value. Unlike PRIMARY KEY, a UNIQUE column can accept NULL values (unless also marked NOT NULL).

Why we use it: Some data must be unique across all rows — emails, usernames, product SKUs, order numbers. UNIQUE prevents duplicate entries that would cause data integrity issues.

When we use it: On columns that represent natural unique identifiers — emails, usernames, slugs, serial numbers, etc.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL
);

CHECK

What it is: The CHECK constraint enforces a custom validation rule on a column. It defines a boolean expression that must be true for every row — otherwise the insert or update is rejected.

Why we use it: Some business rules can't be enforced by simple types or NOT NULL — for example, "price must be positive", "age must be 13+", "status must be one of these values". CHECK lets you enforce these rules at the database level.

When we use it: When you need to validate data against business rules — range checks, positive numbers, valid status values, etc.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC CHECK (price > 0),
    age INTEGER CHECK (age >= 13)
);

DEFAULT

What it is: The DEFAULT constraint provides a fallback value that's automatically used when no value is specified during insertion. It reduces the amount of data you need to provide in INSERT statements.

Why we use it: Many columns have sensible defaults — timestamps default to "now", boolean flags default to false, counters default to 0. Defaults reduce boilerplate and ensure consistency.

When we use it: On columns that have a common starting value — created_at timestamps, is_active flags, status fields, counters, etc.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    published BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

4. Professional Naming Conventions

What it is: Naming conventions are agreed-upon rules for how to name database objects — tables, columns, indexes, and constraints. They're not enforced by PostgreSQL, but they're essential for team collaboration.

Why we use it: Consistent naming makes code predictable and readable. When a new developer joins the team, they can understand the schema without asking questions. It also prevents bugs caused by inconsistent references (e.g., userId vs user_id).

When we use it: Always. Every database you create should follow these conventions from day one.

To code like a senior engineer, follow these industry standards:

Common Mistakes to Avoid

1. Using VARCHAR without a limit — Always specify a max length for data integrity
2. Using REAL or FLOAT for money — Floating-point math causes rounding errors. Use NUMERIC.
3. Forgetting TIMESTAMPTZ — Without timezone info, your timestamps will be ambiguous across regions
4. No PRIMARY KEY — Every table needs a unique identifier for updates and joins

Practice Exercise

1. Connect to your learning_hub database
2. Create a table called courses with these columns:
   • id — auto-incrementing primary key
   • title — required text, max 200 characters
   • instructor — optional text
   • price — required number, must be positive
   • is_published — true/false, defaults to false
   • created_at — timestamp, auto-set to now
3. Verify with \d courses
4. Create a table called students with:
   • id — auto-incrementing primary key
   • name — required text
   • email — required, must be unique
   • enrolled_at — timestamp, auto-set to now