Default Values

Objective

Create tables with DEFAULT values that automatically populate columns when no value is provided during INSERT. Defaults simplify data entry and ensure consistent initial values.

Step 1: Basic Default Values

Create a table with various default values.

CREATE TABLE user_profiles (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    status VARCHAR(20) DEFAULT 'active',
    role VARCHAR(30) DEFAULT 'member',
    login_count INTEGER DEFAULT 0,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Without Specifying Defaults

Insert records without specifying columns that have defaults.

-- Only specify required columns - defaults fill the rest
INSERT INTO user_profiles (id, username, email) VALUES
    (1, 'alice', 'alice@example.com'),
    (2, 'bob', 'bob@example.com'),
    (3, 'charlie', 'charlie@example.com');

Step 3: Verify Default Values Applied

Check that default values were automatically applied.

SELECT
    username,
    status,
    role,
    login_count,
    is_verified,
    created_at
FROM user_profiles;

Step 4: Override Defaults When Needed

Explicitly provide values to override defaults.

INSERT INTO user_profiles (id, username, email, status, role, is_verified) VALUES
    (4, 'admin_user', 'admin@example.com', 'active', 'admin', TRUE),
    (5, 'suspended_user', 'suspended@example.com', 'suspended', 'member', FALSE);

Step 5: Numeric and Calculated Defaults

Create a table with numeric defaults.

CREATE TABLE inventory_items (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    quantity INTEGER DEFAULT 0,
    reorder_level INTEGER DEFAULT 10,
    unit_price DECIMAL(10, 2) DEFAULT 0.00,
    discount_percent DECIMAL(5, 2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO inventory_items (id, sku, name, unit_price) VALUES
    (1, 'SKU-001', 'Widget Standard', 29.99),
    (2, 'SKU-002', 'Widget Premium', 49.99),
    (3, 'SKU-003', 'Widget Basic', 19.99);

SELECT sku, name, quantity, reorder_level, unit_price, is_active
FROM inventory_items;

Step 6: Status and State Defaults

Create a table for tracking orders with default states.

CREATE TABLE support_tickets (
    id INTEGER PRIMARY KEY,
    customer_email VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT,
    priority VARCHAR(20) DEFAULT 'medium',
    status VARCHAR(20) DEFAULT 'open',
    assigned_to VARCHAR(100) DEFAULT 'unassigned',
    response_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO support_tickets (id, customer_email, subject, description) VALUES
    (1, 'customer1@example.com', 'Login Issue', 'Cannot log into my account'),
    (2, 'customer2@example.com', 'Billing Question', 'Need invoice for last month');

INSERT INTO support_tickets (id, customer_email, subject, priority) VALUES
    (3, 'vip@example.com', 'Urgent: System Down', 'high');

SELECT id, subject, priority, status, assigned_to, response_count
FROM support_tickets;

Step 7: Configuration Table with Defaults

Create a settings table with sensible defaults.

CREATE TABLE app_settings (
    id INTEGER PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    is_enabled BOOLEAN DEFAULT TRUE,
    max_retries INTEGER DEFAULT 3,
    timeout_seconds INTEGER DEFAULT 30,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO app_settings (id, setting_key, setting_value) VALUES
    (1, 'email_notifications', 'enabled'),
    (2, 'dark_mode', 'auto'),
    (3, 'language', 'en-US');

INSERT INTO app_settings (id, setting_key, setting_value, timeout_seconds) VALUES
    (4, 'api_endpoint', 'https://api.example.com', 60);

SELECT setting_key, setting_value, is_enabled, max_retries, timeout_seconds
FROM app_settings;

Cleanup (Optional)

DROP TABLE IF EXISTS user_profiles;
DROP TABLE IF EXISTS inventory_items;
DROP TABLE IF EXISTS support_tickets;
DROP TABLE IF EXISTS app_settings;

Expected Outcomes

• Default values auto-populate on INSERT
• CURRENT_TIMESTAMP captures insert time
• Numeric defaults initialize counters
• Status defaults establish initial states
• Explicit values override defaults

Common Default Patterns

Key Concepts Learned

• DEFAULT clause syntax
• CURRENT_TIMESTAMP for auto-timestamps
• Defaults for various data types
• Overriding defaults with explicit values
• Common default value patterns