Part 33: Creating Tables: CREATE TABLE Syntax & Best Practices

A clean, normalized table definition is the bedrock of database stability.

1. Production Table Example

CREATE TABLE IF NOT EXISTS users (
  id BIGSERIAL PRIMARY KEY,
  uuid UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. Table Creation Best Practices

1. Explicit Nullability: Always declare NOT NULL unless null is explicitly meaningful.
2. Singular vs Plural Naming: Standardize across your project (e.g. users or user).
3. Always Include Audit Columns: created_at and updated_at.

Key Takeaways

• Use explicit data types and non-null constraints.
• Provide sensible default values.

In Part 34, we explore Primary Keys: Natural vs Surrogate Keys and UUIDs vs Auto-Increment!