CREATE & ALTER Tables

Real-World Example: Creating a Products Table

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL,
    stock_quantity INTEGER DEFAULT 0,
    category TEXT,
    created_date TEXT
);

This creates a table to store product information for an e-commerce store!

❌ Bad Practice:

CREATE TABLE customers (
    name TEXT,
    email TEXT
);
-- No way to uniquely identify each customer!
-- What if two people have the same name?

✅ Good Practice:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);
-- Each customer has a unique ID
-- Email is unique and required

❌ Bad Practice:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price TEXT,  -- ❌ Price as TEXT!
    quantity TEXT  -- ❌ Quantity as TEXT!
);

-- Later you try to calculate total:
-- SELECT price * quantity FROM products;  -- ERROR!

✅ Good Practice:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK(price >= 0),
    quantity INTEGER NOT NULL CHECK(quantity >= 0)
);

-- Now calculations work!
-- SELECT price * quantity AS total FROM products;  -- ✓ Works!

❌ Bad Practice:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT,  -- ❌ Could be NULL!
    email TEXT  -- ❌ Could be NULL!
);

-- Later someone inserts a user with no email:
INSERT INTO users (id, username) VALUES (1, 'john');
-- Now you have a user with NULL email - problematic!

✅ Good Practice:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE
);

-- This prevents incomplete data:
INSERT INTO users (id, username) VALUES (1, 'john');  -- ERROR!
-- Must provide email too!

❌ Bad Practice:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,  -- ❌ No validation!
    salary REAL  -- ❌ Could be negative!
);

-- Someone could insert invalid data:
INSERT INTO employees VALUES (1, 'Bob', -5, -50000);  -- ❌ Nonsense data!

✅ Good Practice:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK(age >= 18 AND age <= 100),
    salary REAL CHECK(salary >= 0)
);

-- Constraints prevent bad data:
INSERT INTO employees VALUES (1, 'Bob', -5, -50000);  -- ERROR! Prevented!

❌ Bad Practice:

-- Unclear or misleading names:
CREATE TABLE data (id INTEGER, info TEXT);  -- Too vague!
CREATE TABLE tbl1 (id INTEGER, val TEXT);  -- Meaningless!
CREATE TABLE USER (id INTEGER, name TEXT);  -- Reserved keyword!

✅ Good Practice:

-- Clear, descriptive names:
CREATE TABLE customer_orders (order_id INTEGER, customer_id INTEGER);
CREATE TABLE product_reviews (review_id INTEGER, product_id INTEGER);
CREATE TABLE app_users (user_id INTEGER, username TEXT);  -- 'users' is safer than 'USER'

Error 1: "NOT NULL constraint failed"

What it means: You tried to insert a row without providing a value for a NOT NULL column

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

-- Solution: Provide all required fields
INSERT INTO users (id, username, email) VALUES (1, 'alice', 'alice@email.com');

Error 2: "UNIQUE constraint failed"

What it means: You tried to insert a value that already exists in a UNIQUE column

-- First insert succeeds:
INSERT INTO users (id, username, email) VALUES (1, 'alice', 'alice@email.com');

-- This will fail (duplicate email):
INSERT INTO users (id, username, email) VALUES (2, 'bob', 'alice@email.com');
-- Error: UNIQUE constraint failed: users.email

-- Solution: Use a different, unique value
INSERT INTO users (id, username, email) VALUES (2, 'bob', 'bob@email.com');

Error 3: "CHECK constraint failed"

What it means: The value you're trying to insert doesn't meet the CHECK condition

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL CHECK(price >= 0)
);

-- This will fail:
INSERT INTO products VALUES (1, 'Laptop', -100);
-- Error: CHECK constraint failed: products

-- Solution: Provide a valid value
INSERT INTO products VALUES (1, 'Laptop', 999.99);

Error 4: "FOREIGN KEY constraint failed"

What it means: You're trying to insert a foreign key value that doesn't exist in the referenced table

-- Try to create an order for customer_id=999 (doesn't exist):
INSERT INTO orders (order_id, customer_id) VALUES (1, 999);
-- Error: FOREIGN KEY constraint failed

-- Solution: First create the customer, OR use an existing customer_id
INSERT INTO customers (customer_id, name) VALUES (999, 'New Customer');
INSERT INTO orders (order_id, customer_id) VALUES (1, 999);  -- Now works!

Common Primary Key Patterns:

-- Pattern 1: Simple integer ID
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ...
);

-- Pattern 2: Composite primary key (multiple columns)
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    PRIMARY KEY (student_id, course_id)
);

-- Pattern 3: UUID/GUID (advanced)
CREATE TABLE sessions (
    session_id TEXT PRIMARY KEY,  -- e.g., "a1b2c3d4-..."
    ...
);

Workaround: Recreating Table to Remove Column

-- Steps to remove a column in SQLite:

-- 1. Create new table without the unwanted column
CREATE TABLE employees_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
    -- removed 'age' column
);

-- 2. Copy data from old table
INSERT INTO employees_new SELECT id, name FROM employees_old;

-- 3. Drop old table
DROP TABLE employees_old;

-- 4. Rename new table
ALTER TABLE employees_new RENAME TO employees_old;

Safe Dropping Pattern:

-- Check if table exists before dropping
DROP TABLE IF EXISTS old_data;

-- Create fresh table
CREATE TABLE new_data (
    id INTEGER PRIMARY KEY,
    info TEXT
);

-- This won't error even if old_data didn't exist

Example: Well-Designed E-Commerce Schema

-- Customers table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL CHECK(price >= 0),
    stock_quantity INTEGER DEFAULT 0
);

-- Orders table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    order_date TEXT DEFAULT CURRENT_TIMESTAMP,
    total_amount REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Order Items (junction table for many-to-many)
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    price_at_purchase REAL NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

❌ Bad Design: Not Normalized (Data Duplication)

Problem: Customer information repeated in every order!

CREATE TABLE orders_bad (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_address TEXT,
    product_name TEXT,
    product_price REAL,
    quantity INTEGER
);

-- Sample data showing duplication:
-- Order 1: John Doe, john@email.com, 123 Main St, Laptop, 999.99, 1
-- Order 2: John Doe, john@email.com, 123 Main St, Mouse, 29.99, 2
-- Order 3: John Doe, john@email.com, 123 Main St, Keyboard, 79.99, 1

-- Problems:
-- 1. John's info repeated 3 times (waste of space!)
-- 2. If John changes email, must update 3 rows (error-prone!)
-- 3. Misspelling John's name in one order causes inconsistency

✅ Good Design: Normalized (No Duplication)

Solution: Separate tables with relationships!

-- Customer info stored ONCE
CREATE TABLE customers_normalized (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    address TEXT
);

-- Product info stored ONCE
CREATE TABLE products_normalized (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

-- Orders only store IDs (references to other tables)
CREATE TABLE orders_normalized (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers_normalized(customer_id),
    FOREIGN KEY (product_id) REFERENCES products_normalized(product_id)
);

-- Benefits:
-- 1. Customer info stored ONCE - no duplication!
-- 2. Update John's email in ONE place - affects all orders automatically!
-- 3. Consistent data - can't have typos in repeated data!

Example 1: Student Enrollment System

❌ Bad Design (Denormalized):

CREATE TABLE enrollments_bad (
    enrollment_id INTEGER PRIMARY KEY,
    student_name TEXT,
    student_email TEXT,
    student_major TEXT,
    course_name TEXT,
    course_instructor TEXT,
    course_credits INTEGER
);

-- If student takes 5 courses, their info is repeated 5 times!
-- If instructor name changes, must update many rows!

✅ Good Design (Normalized):

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    instructor TEXT NOT NULL,
    credits INTEGER
);

CREATE TABLE enrollments_good (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course_id INTEGER,
    grade TEXT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- Student info stored once, course info stored once!
-- Enrollments just link them together

Example 2: Library System

❌ Bad Design:

CREATE TABLE checkouts_bad (
    checkout_id INTEGER PRIMARY KEY,
    member_name TEXT,
    member_phone TEXT,
    book_title TEXT,
    book_author TEXT,
    book_isbn TEXT,
    checkout_date TEXT,
    due_date TEXT
);

-- Book details repeated every time someone checks it out!
-- Member info repeated for every book they borrow!

✅ Good Design:

CREATE TABLE members (
    member_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT,
    email TEXT UNIQUE
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    isbn TEXT UNIQUE
);

CREATE TABLE checkouts_good (
    checkout_id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    checkout_date TEXT DEFAULT CURRENT_TIMESTAMP,
    due_date TEXT NOT NULL,
    return_date TEXT,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Clean, organized, no duplication!