CREATE TABLE Statement

The CREATE TABLE Statement

The CREATE TABLE statement defines a new table's structure — column names, data types, and constraints.

💡 CREATE TABLE is like designing a spreadsheet template before filling in the data.

Basic Syntax

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

Simple Example

CREATE TABLE employees (
    id INTEGER,
    first_name TEXT,
    last_name TEXT,
    salary REAL,
    department TEXT
);

Adding Constraints

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,           -- Unique identifier
    first_name TEXT NOT NULL,          -- Required field
    last_name TEXT NOT NULL,           -- Required field
    email TEXT UNIQUE,                 -- Must be unique
    salary REAL DEFAULT 0,             -- Default value
    department TEXT DEFAULT 'General', -- Default value
    hire_date DATE DEFAULT CURRENT_DATE -- Auto-set to today
);

Common Constraints

CHECK Constraint Example

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

Composite Primary Key

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)  -- Both form the unique ID
);

✏️ Exercise: Create a table called 'books' with columns: id (INTEGER PRIMARY KEY), title (TEXT NOT NULL), author (TEXT NOT NULL), price (REAL with CHECK price >= 0), and isbn (TEXT UNIQUE)

See Solution

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    price REAL CHECK (price >= 0),
    isbn TEXT UNIQUE
);

✅ Key Takeaways

1. CREATE TABLE defines a new table's structure
2. Each column needs a name, type, and optional constraints
3. Constraints enforce data integrity rules
4. PRIMARY KEY uniquely identifies each row
5. Use IF NOT EXISTS to avoid errors on re-run