Creating Tables

Introduction

Tables are the heart of relational databases. A table has columns (fields with data types) and rows (the data). Designing tables well is the most important skill in SQL.

Beginner analogy: a table is a spreadsheet — but with strict rules about what each column can contain.

Understanding the topic

Core concepts to understand:

• CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT NOT NULL);
• Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.
• Always set a primary key — usually id.
• Use TIMESTAMPTZ for created_at / updated_at in Postgres.
• Modify with ALTER TABLE; remove with DROP TABLE.

Syntax reference

Visual workflow / architecture:

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

users table
┌────┬──────────────┬──────┬─────────────┐
│ id │ email        │ name │ created_at  │
└────┴──────────────┴──────┴─────────────┘

Real-world use

Every user-account, order, invoice and event in production is a row in a carefully designed table. Bad table design haunts companies for years.

Best practices

• Always set a primary key.
• Use NOT NULL by default; allow NULL only when meaningful.
• Add timestamps (created_at, updated_at) to almost every table.

Common mistakes

• Storing dates as TEXT instead of DATE/TIMESTAMP.
• Using floating-point for money — use NUMERIC.
• Allowing NULL on every column 'just in case'.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is a primary key?
• Q2. Difference between UNIQUE and PRIMARY KEY.
• Q3. Why avoid NULL where possible?
• Q4. Best data type for money?
• Q5. ALTER TABLE syntax to add a column.