SQL Fundamentals

CREATE TABLE Statement

The CREATE TABLE statement defines a new table's structure — column names, data types, and constraints. It's like designing a spreadsheet template before filling it with data.

Columns — Define what attributes each row will have
Data Types — Specify what kind of data each column stores
Constraints — Enforce rules to maintain data integrity

Design your schema carefully — changes after deployment are costly.

Basic Syntax

DfCREATE TABLE

A DDL (Data Definition Language) statement that creates a new table in the database. You specify the table name, column names, data types, and optional constraints that enforce data integrity.

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

Component	Description	Example
`table_name`	Name of the table	`employees`
`column_name`	Name of the column	`first_name`
`datatype`	Data type for the column	`VARCHAR(100)`
`constraint`	Rules for the data	`NOT NULL`

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

DfConstraints

Rules applied to table columns that enforce data integrity. Constraints prevent invalid data from being inserted and ensure relationships between tables remain consistent.

Constraint	Description	Example	Impact
`NOT NULL`	Column cannot have NULL value	`name TEXT NOT NULL`	Blocks NULL inserts
`UNIQUE`	All values must be different	`email TEXT UNIQUE`	Creates index
`PRIMARY KEY`	Unique identifier for each row	`id INTEGER PRIMARY KEY`	Clustered index
`FOREIGN KEY`	Links to another table's primary key	`REFERENCES customers(id)`	Referential integrity
`CHECK`	Ensures values meet a condition	`CHECK (price > 0)`	Validates on INSERT/UPDATE
`DEFAULT`	Sets default value if none provided	`DEFAULT CURRENT_DATE`	Auto-fill

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

DfComposite Primary Key

A primary key that consists of two or more columns. The combination of values must be unique across all rows. Used when a single column cannot uniquely identify a record.

-- 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
);

Composite primary keys are ideal for junction tables in many-to-many relationships. They automatically create a composite index that speeds up queries filtering on both columns.

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)

DfCTAS (CREATE TABLE AS SELECT)

A shorthand that creates a new table and populates it with the results of a SELECT query. Useful for creating backups, snapshots, or derived tables from existing data.

-- 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
);

DROP TABLE permanently deletes all data and the table structure. Use ALTER TABLE instead when possible — it preserves your data while changing the structure.

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

Key Takeaways

CREATE TABLE defines a new table's structure with columns and constraints
Always define a PRIMARY KEY for each table
Use NOT NULL for required fields and UNIQUE for fields that must be distinct
CHECK constraints enforce custom business rules at the database level
Use IF NOT EXISTS to prevent errors in scripts that may run multiple times

CREATE TABLE Statement

CREATE TABLE Statement

Basic Syntax

DfCREATE TABLE

Simple Example

Adding Constraints

Common Constraints

DfConstraints

NOT NULL

UNIQUE

CHECK

DEFAULT

Composite Primary Key

DfComposite Primary Key

IF NOT EXISTS

CREATE TABLE AS SELECT (CTAS)

DfCTAS (CREATE TABLE AS SELECT)

Table Options (Database Specific)

Dropping and Recreating

Common Mistakes

Summary

Key Takeaways

Premium Content

Need Expert SQL Help?