MySQL DEFAULT

Key DEFAULT Concepts

status VARCHAR(20) DEFAULT 'active'

created_at TIMESTAMP DEFAULT NOW()

quantity INT DEFAULT 0

country VARCHAR(50) DEFAULT 'USA'

🔹 Creating DEFAULT Constraint

Define DEFAULT values when creating tables to provide fallback values for columns. This reduces the need for explicit values during insertion and ensures consistency. DEFAULT works with all data types including strings, numbers, dates, and even expressions like CURRENT_TIMESTAMP.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2) DEFAULT 0.00,
    stock_quantity INT DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE
);

🔹 DEFAULT with CURRENT_TIMESTAMP

Use CURRENT_TIMESTAMP or NOW() as DEFAULT for timestamp columns to automatically record when records are created or modified. This is essential for audit trails and tracking data changes without manual timestamp management in your application code.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

🔹 DEFAULT with String Values

String DEFAULT values are useful for status fields, categories, or any text column that should have a standard initial value. Enclose string defaults in quotes and choose values that make sense for your business logic, like 'pending' for orders or 'active' for users.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50) DEFAULT 'General',
    status VARCHAR(20) DEFAULT 'active',
    role VARCHAR(50) DEFAULT 'employee'
);

🔹 DEFAULT with Numeric Values

Numeric DEFAULT values are perfect for counters, quantities, or any numeric field that should start with a specific value. Common examples include setting stock quantities to zero, initializing counters, or setting default prices for products when not specified during insertion.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    username VARCHAR(50),
    balance DECIMAL(10,2) DEFAULT 0.00,
    login_attempts INT DEFAULT 0,
    discount_percentage DECIMAL(5,2) DEFAULT 0.00
);

🔹 Inserting with DEFAULT Values

When inserting data, you can omit columns with DEFAULT values and they'll automatically receive their default. You can also explicitly use the DEFAULT keyword to insert the default value even when specifying the column, giving you flexibility in data insertion operations.

-- Omit columns with defaults
INSERT INTO products (product_id, product_name)
VALUES (1, 'Laptop');
-- price, stock_quantity, and is_available get default values

-- Explicitly use DEFAULT keyword
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES (2, 'Mouse', DEFAULT, 100);

🔹 Adding DEFAULT to Existing Column

Add DEFAULT values to existing columns using ALTER TABLE with MODIFY or ALTER COLUMN. This updates the column definition to include a default value for future insertions, but doesn't change existing records. Existing NULL values remain NULL unless you update them separately.

-- Add DEFAULT to existing column
ALTER TABLE employees 
ALTER COLUMN status SET DEFAULT 'active';

-- Or using MODIFY (includes full column definition)
ALTER TABLE products 
MODIFY COLUMN stock_quantity INT DEFAULT 0;

🔹 Removing DEFAULT Constraint

Remove DEFAULT values from columns when you want to require explicit values during insertion or change the default behavior. After dropping the default, the column will be NULL if not specified during insert, unless it has a NOT NULL constraint.

-- Remove DEFAULT from column
ALTER TABLE employees 
ALTER COLUMN department DROP DEFAULT;

🔹 DEFAULT with Expressions

MySQL allows expressions as DEFAULT values in newer versions, enabling dynamic defaults based on functions or calculations. This includes mathematical operations, string functions, or date calculations, providing powerful automatic value generation beyond simple static defaults.

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(100),
    created_date DATE DEFAULT (CURRENT_DATE),
    event_code VARCHAR(20) DEFAULT (UUID())
);