📋 Creating Tables — Structuring Your Data

Learn how to design tables that organize data effectively.

🎯 What is a Table?

A table is like a spreadsheet with rows (records) and columns (fields):

ID | Name  | Age | Email
---|-------|-----|------------------
1  | Alice | 30  | alice@example.com
2  | Bob   | 25  | bob@example.com
3  | Carol | 28  | carol@example.com

Each column has a data type that defines what kind of data it can hold.

💡 Data Types in SQLite

🏗️ Creating Your First Table

import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

# CREATE TABLE statement
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    )
""")
conn.commit()

Breaking it down:

• `CREATE TABLE users` — Create a new table named "users"

• `id INTEGER PRIMARY KEY` — Auto-incrementing unique ID

• `name TEXT NOT NULL` — Text field that must have a value

• `age INTEGER` — Optional integer field

• `email TEXT` — Optional text field

🔑 Table Constraints

Constraints enforce rules about what data can be stored:

PRIMARY KEY

# Each ID must be unique (no duplicates)
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
)

NOT NULL

# Name is required (can't be empty)
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)

UNIQUE

# Email must be unique (no two users same email)
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
)

DEFAULT

# If no age provided, default to 0
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER DEFAULT 0,
    created_date TEXT DEFAULT CURRENT_TIMESTAMP
)

CHECK

# Age must be positive
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK(age > 0)
)

📊 Practical Example

import sqlite3

conn = sqlite3.connect("school.db")
cursor = conn.cursor()

# Create students table
cursor.execute("""
    CREATE TABLE students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        grade INTEGER CHECK(grade >= 1 AND grade <= 12),
        email TEXT UNIQUE,
        enrollment_date TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")

# Create courses table
cursor.execute("""
    CREATE TABLE courses (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        teacher TEXT NOT NULL,
        credits INTEGER DEFAULT 3
    )
""")

conn.commit()
print("Tables created successfully!")

🔄 Modifying Tables

# Add a new column to existing table
cursor.execute("ALTER TABLE users ADD COLUMN phone TEXT")

# Rename a table
cursor.execute("ALTER TABLE users RENAME TO profiles")

# Check if table exists
cursor.execute("""
    SELECT name FROM sqlite_master
    WHERE type='table' AND name='users'
""")
table_exists = cursor.fetchone() is not None

🗑️ Dropping Tables

# Delete entire table (be careful!)
cursor.execute("DROP TABLE users")
conn.commit()

# Drop only if it exists
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()

🔑 Key Takeaways

• ✅ Tables organize data into rows and columns

• ✅ Each column has a data type (INTEGER, TEXT, REAL, etc.)

• ✅ PRIMARY KEY creates unique identifiers

• ✅ NOT NULL requires a value

• ✅ UNIQUE prevents duplicates

• ✅ DEFAULT provides fallback values

• ✅ CHECK enforces rules on values

Ready to practice? Challenges | Quiz