MySQL Default Values

When creating tables in MySQL, you often need to specify what happens when a user inserts a record without providing values for all columns. Default values provide a solution to this common requirement. They help maintain data integrity and simplify your INSERT statements.

What Are Default Values?

Default values are predefined values that MySQL assigns to columns when no explicit value is provided during an INSERT operation. They serve as fallback values, ensuring that columns always contain valid data even when not explicitly specified.

Why Use Default Values?

Default values serve several important purposes in database design:

1. Data Integrity: Ensures columns always contain meaningful values
2. Simplifies Data Entry: Reduces the need to specify common values repeatedly
3. Timestamps: Automatically tracks record creation and modification times
4. Consistency: Maintains uniform values for similar data entries
5. Usability: Makes database interactions more intuitive

Syntax for Defining Default Values

You can define default values when creating a new table or when altering an existing one.

In CREATE TABLE Statement

sql

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value,
    ...
);

In ALTER TABLE Statement

sql

ALTER TABLE table_name
MODIFY column_name data_type DEFAULT default_value;

Common Types of Default Values

MySQL supports various types of default values for different data types:

Literal Values

You can use literal values like numbers, strings, or dates:

sql

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) DEFAULT 0.00,
    in_stock BOOLEAN DEFAULT TRUE,
    description TEXT DEFAULT 'No description available'
);

Current Date and Time

MySQL provides functions for automatically setting date/time values:

sql

CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

NULL Value

You can explicitly set NULL as a default value (this is also the implicit default if no DEFAULT clause is specified and the column allows NULL):

sql

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50) DEFAULT NULL,
    notes TEXT DEFAULT NULL
);

Practical Examples

Let's explore some practical examples of using default values in real-world scenarios.

Example 1: User Registration System

When creating a user registration system, you might want to set default values for user status, registration date, and certain preferences:

sql

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    registration_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME DEFAULT NULL,
    account_status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    email_notifications BOOLEAN DEFAULT TRUE,
    theme_preference VARCHAR(20) DEFAULT 'light'
);

Now when inserting a new user, we only need to provide the essential information:

sql

INSERT INTO users (username, email, password_hash) 
VALUES ('john_doe', '[email protected]', 'hashed_password_here');

MySQL will automatically:

• Set the current timestamp for registration_date
• Set account_status to 'active'
• Enable email notifications
• Set theme preference to 'light'
• Set last_login to NULL

Example 2: E-commerce Order System

For an order tracking system, you might want to set default values for order status and timestamps:

sql

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_method VARCHAR(50) DEFAULT 'standard',
    notes TEXT DEFAULT '',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

When a customer places an order:

sql

INSERT INTO orders (customer_id) VALUES (42);

The system will automatically:

• Record the current time as the order date
• Set the initial status to 'pending'
• Set the shipping method to 'standard'

Default Values and Data Types

Different data types have different considerations for default values:

Numeric Types

For numeric types like INT, DECIMAL, or FLOAT, you can use any valid number as a default:

sql

CREATE TABLE product_metrics (
    metric_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    views INT DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 5.00,
    priority TINYINT DEFAULT 3
);

String Types

For CHAR, VARCHAR, TEXT, etc., you can use any string as a default:

sql

CREATE TABLE configuration (
    config_key VARCHAR(50) PRIMARY KEY,
    config_value VARCHAR(255) DEFAULT '',
    config_description TEXT DEFAULT 'No description provided'
);

Date and Time Types

For date and time types, you can use literals or functions:

sql

CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    start_date DATE DEFAULT (CURRENT_DATE + INTERVAL 7 DAY),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ENUM Types

For ENUM types, you typically default to one of the enumerated values:

sql

CREATE TABLE tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(100) NOT NULL,
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open'
);

Limitations and Considerations

When working with default values, keep these points in mind:

1. BLOB and TEXT Limitations: Before MySQL 8.0.13, you couldn't set non-NULL default values for BLOB or TEXT columns.

2. Dynamic Defaults: Default values must be constants or deterministic expressions. You can't use random functions like RAND() or most subqueries.

3. AUTO_INCREMENT: Columns with AUTO_INCREMENT can't have default values.

4. Expression Defaults: MySQL 8.0+ allows expressions as default values:

sql

CREATE TABLE sample (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_date DATE DEFAULT (CURRENT_DATE),
    expiry_date DATE DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR)
);

5. Default with NOT NULL: Combining NOT NULL with DEFAULT ensures a column always has a meaningful value:

sql

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

Modifying Default Values

You can change default values for existing columns using the ALTER TABLE statement:

sql

ALTER TABLE products
MODIFY COLUMN price DECIMAL(10,2) DEFAULT 9.99;

To remove a default value:

sql

ALTER TABLE products
ALTER COLUMN price DROP DEFAULT;

Viewing Default Values

You can check the default values for your table columns using the DESCRIBE statement or INFORMATION_SCHEMA:

sql

DESCRIBE table_name;

-- OR

SELECT column_name, column_default 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = 'your_database' AND table_name = 'your_table';

Performance Impact

Using default values generally has minimal performance impact. However, there are some considerations:

1. Disk Space: DEFAULT NULL typically uses less disk space than DEFAULT '' for string columns

2. Indexes: Default values don't affect index performance directly, but can influence cardinality

3. TIMESTAMP Columns: Multiple TIMESTAMP columns with CURRENT_TIMESTAMP defaults can slightly impact INSERT performance

Summary

Default values in MySQL provide a powerful mechanism for:

• Ensuring data integrity by avoiding null or invalid values
• Simplifying database operations by reducing the need for explicit values
• Automating common tasks like timestamp tracking
• Maintaining consistency across database records

When designing MySQL tables, carefully consider appropriate default values for your columns to create more robust and user-friendly database systems.

Exercises

1. Create a table for a blog post system with appropriate default values for creation date, status, and view count.
2. Modify an existing table to add a "last_updated" column with a default value that updates on each record change.
3. Write INSERT statements that take advantage of default values to minimize the required input.
4. Design a table schema for a user preference system where most preferences have sensible defaults.

Additional Resources

• MySQL Documentation on Default Values
• MySQL 8.0+ Expression Default Values
• MySQL Timestamp Properties