Basic Syntax

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint
);

Simple Example

-- Basic table creation without constraints
CREATE TABLE employees (
    id INTEGER,
    first_name TEXT,
    last_name TEXT,
    salary REAL,
    department TEXT
);

-- This works but has several issues:
-- 1. No primary key defined
-- 2. No NOT NULL constraints
-- 3. Can insert duplicate or incomplete rows

Adding Constraints

-- Complete table with all constraints
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE,
    salary REAL DEFAULT 0,
    department TEXT DEFAULT 'General',
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT TRUE
);

Common Constraints

NOT NULL

-- NOT NULL prevents NULL values
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    bio TEXT                       -- Optional (can be NULL)
);

-- This will fail:
INSERT INTO users (id, username, email) VALUES (1, NULL, 'alice@email.com');
-- Error: NOT NULL constraint failed: users.username

UNIQUE

-- UNIQUE ensures no duplicate values
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    sku VARCHAR(20) UNIQUE NOT NULL,
    barcode VARCHAR(50) UNIQUE
);

CHECK

-- CHECK validates custom conditions
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    discount_percent REAL CHECK (discount_percent >= 0 AND discount_percent <= 100)
);

-- CHECK with complex conditions
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    salary REAL CHECK (salary > 0),
    hire_date DATE CHECK (hire_date <= CURRENT_DATE),
    termination_date DATE,
    CHECK (termination_date IS NULL OR termination_date > hire_date)
);

DEFAULT

-- DEFAULT provides automatic values
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    order_time TIME DEFAULT CURRENT_TIME,
    status TEXT DEFAULT 'pending',
    priority INTEGER DEFAULT 0,
    notes TEXT
);

-- DEFAULT with functions
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    uuid TEXT DEFAULT (hex(randomblob(16))),  -- SQLite
    created_at DATETIME DEFAULT (datetime('now'))
);

Composite Primary Key

-- When a single column isn't enough for uniqueness
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (order_id, product_id),
    
    FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT
);

IF NOT EXISTS

-- Prevent errors when table already exists
CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

-- Safe to run multiple times
-- First run: creates table
-- Subsequent runs: does nothing (no error)

CREATE TABLE AS SELECT (CTAS)

-- Create table from existing data
CREATE TABLE customer_archive AS
SELECT id, name, email, created_at
FROM customers
WHERE created_at < '2020-01-01';

-- Creates a new table with:
-- 1. Same column structure as SELECT result
-- 2. All matching rows copied
-- 3. No constraints (just data)

Table Options (Database Specific)

-- MySQL table options
CREATE TABLE products (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- PostgreSQL with schemas
CREATE TABLE public.employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- SQL Server with filegroups
CREATE TABLE large_data (
    id INTEGER PRIMARY KEY,
    data TEXT
) ON PRIMARY;

Dropping and Recreating

-- Drop table if it exists (removes all data!)
DROP TABLE IF EXISTS old_table;

-- Recreate with new structure
CREATE TABLE old_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    new_column TEXT
);

Common Mistakes

-- BAD: Missing primary key
CREATE TABLE bad_table (
    name TEXT,
    email TEXT
);

-- GOOD: Proper primary key
CREATE TABLE good_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

-- BAD: Using TEXT for everything
CREATE TABLE bad_types (
    age TEXT,
    price TEXT,
    is_active TEXT
);

-- GOOD: Appropriate types
CREATE TABLE good_types (
    age SMALLINT CHECK (age >= 0),
    price DECIMAL(10,2) CHECK (price >= 0),
    is_active BOOLEAN DEFAULT TRUE
);

Summary