The Complete SQL CREATE TABLE Guide with Real-World Examples

SQL DDL and CREATE TABLE

SQL is divided into DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language). The most fundamental DDL command is CREATE TABLE, used to define database tables.

Mastering CREATE TABLE lets you build clear database structures and enforce data integrity from the start.

Basic Syntax

CREATE TABLE table_name (
  column_name data_type [constraints],
  column_name data_type [constraints],
  ...
  [table-level constraints]
);

Common Data Types

Text

• VARCHAR(n): Variable-length string (max n chars). Use for emails, names
• CHAR(n): Fixed-length string. Use for country codes (CHAR(2)), zip codes
• TEXT: Large text. Use for post content, descriptions

Numeric

• INTEGER / INT: Integers. Use for ages, quantities
• BIGINT: Large integers. Use for IDs, large counters
• DECIMAL(p, s): Precise decimals. Use for money (DECIMAL(15, 2))
• FLOAT / DOUBLE: Floating point (when precision isn't critical)

Date/Time

• DATE: Date only (2025-01-01)
• TIME: Time only (14:30:00)
• TIMESTAMP: Date + time (2025-01-01 14:30:00)
• TIMESTAMPTZ: Timestamp with timezone (PostgreSQL)

Other

• BOOLEAN: True/false
• UUID: Universally unique identifier
• JSONB: JSON data with indexing (PostgreSQL)

Key Constraints

Column-Level Constraints

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  age INTEGER CHECK (age >= 0 AND age <= 150),
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• PRIMARY KEY: Unique + NOT NULL identifier
• NOT NULL: Rejects NULL values
• UNIQUE: Rejects duplicate values
• DEFAULT: Sets default value
• CHECK: Validates value against a condition

Foreign Keys

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  total_amount DECIMAL(15, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

Naming your foreign key constraint (e.g., CONSTRAINT fk_orders_user) makes it easier to modify or drop later.

Composite Primary Keys

Common in junction tables for many-to-many relationships.

CREATE TABLE user_roles (
  user_id UUID NOT NULL,
  role_id INTEGER NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
);

Real-World Example: Blog System

Let's write CREATE TABLE statements for a real blog system.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  avatar_url TEXT,
  bio TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(120) NOT NULL UNIQUE,
  description TEXT
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID NOT NULL,
  category_id INTEGER,
  title VARCHAR(300) NOT NULL,
  slug VARCHAR(350) NOT NULL UNIQUE,
  content TEXT NOT NULL,
  status VARCHAR(20) DEFAULT 'draft'
    CHECK (status IN ('draft', 'published', 'archived')),
  view_count INTEGER DEFAULT 0,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ,

  CONSTRAINT fk_posts_author
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_posts_category
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL,
  author_id UUID NOT NULL,
  parent_id UUID,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT,
  FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

-- Add indexes
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX idx_comments_post ON comments(post_id);

CREATE TABLE IF NOT EXISTS

To prevent errors when the table already exists:

CREATE TABLE IF NOT EXISTS settings (
  key VARCHAR(100) PRIMARY KEY,
  value TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Auto-Generate SQL from ERDs

With Erdmates, you can design your ERD visually and then auto-generate CREATE TABLE SQL for PostgreSQL, MySQL, SQLite, and other databases with a single click.

This eliminates SQL syntax typos and maintains consistency across your codebase.

Conclusion

CREATE TABLE is the first step in database development. Leverage constraints to enforce data integrity and maintain consistent naming conventions — these are the hallmarks of a well-designed schema.