Topic 6 of 52
CREATE TABLE
Overview
CREATE TABLE defines the structure of a new table including column names, data types, constraints, and default values. Getting table design right from the start prevents costly migrations later.
Syntax
sql
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
table_constraints
);
-- Full example with constraints:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
department VARCHAR(100),
salary DECIMAL(12,2) CHECK (salary > 0),
manager_id INT REFERENCES employees(id), -- self-ref
hired_at DATE NOT NULL DEFAULT CURRENT_DATE,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);Common Pitfalls
- Always define a PRIMARY KEY — tables without one are difficult to update, replicate, and maintain.
- Use ON DELETE RESTRICT (not CASCADE) for critical foreign keys — cascading deletes can wipe out large swaths of data unintentionally.
- Interview tip: Create indexes separately after the table — CREATE TABLE with many indexes slows down the initial creation.
Real-World Example
Creating a complete orders table with proper constraints:
example
sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
order_number VARCHAR(20) NOT NULL UNIQUE,
user_id INT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','processing','shipped','delivered','cancelled','refunded')),
subtotal DECIMAL(12,2) NOT NULL CHECK (subtotal >= 0),
tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (discount_amount >= 0),
total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
shipping_addr JSONB,
notes TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Add index after table creation
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'delivered';