• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Default

← Previous Next →

The MySQL DEFAULT constraint is used to automatically assign a predefined value to a column when no value is specified during an insert. This ensures that the database remains consistent, reliable, and aligned with business rules.

Default values are particularly useful in applications where new records need initial values for status, quantities, flags, or timestamps, without requiring the application to provide them explicitly.

By using DEFAULT, you reduce the risk of NULL or inconsistent data and simplify data entry and management.

What Is a DEFAULT Constraint?

A DEFAULT constraint specifies a fallback value for a column. If a value is not provided during insertion, the database automatically assigns the default.

Key Points:

  • Can be applied to numeric, string, date, or boolean columns.

  • Works seamlessly with NOT NULL constraints to ensure every row has meaningful data.

  • Does not overwrite explicitly provided values.

  • Simplifies database operations and reduces manual data entry errors.

For example, in a student management system, a status column can default to 'active' so that all new students are automatically considered active unless specified otherwise.

Syntax for DEFAULT

Column-Level Syntax:

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

Table-Level Syntax (using ALTER TABLE):

ALTER TABLE table_name
MODIFY column_name datatype DEFAULT default_value;

Explanation:

  • column_name: The column to which the default applies.

  • default_value: The value automatically assigned if no value is provided.

  • Default values must match the column data type.

Creating a Table with DEFAULT Values

Example 1 – Basic DEFAULT usage:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    status VARCHAR(10) DEFAULT 'active',
    enrollment_date DATE DEFAULT CURRENT_DATE
);

Explanation:

  • status defaults to 'active'.

  • enrollment_date defaults to the current date.

  • student_id uses AUTO_INCREMENT for unique identification.

Example 2 – Numeric and Boolean Defaults:

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

Explanation:

  • price defaults to 0.00.

  • stock defaults to 0.

  • is_available defaults to TRUE.

  • Ensures new product records always have meaningful initial values.

Inserting Data with DEFAULT Values

Example Insert Without Specifying Defaults:

INSERT INTO students (student_name) VALUES ('Meera');

Result:

  • student_name = 'Meera'

  • status = 'active' (default applied)

  • enrollment_date = current date

Insert With Some Columns Specified:

INSERT INTO students (student_name, status) VALUES ('Riya', 'inactive');

Result:

  • status = 'inactive' (explicit value overrides default)

  • enrollment_date = current date (default applied)

This demonstrates how DEFAULT values are only applied when a column is omitted.

Modifying Existing Columns to Add Defaults

You can modify an existing table to add or change default values:

ALTER TABLE students
MODIFY status VARCHAR(10) DEFAULT 'active';

Explanation:

  • Sets a default value for future inserts.

  • Existing rows are not affected; only new rows without a specified value use the default.

Combining DEFAULT with Other Constraints

Defaults are often combined with NOT NULL, CHECK, or UNIQUE constraints to strengthen data integrity:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    quantity INT DEFAULT 1 CHECK (quantity > 0)
);

Explanation:

  • order_date automatically records the current date.

  • status defaults to 'pending'.

  • quantity defaults to 1 and must be greater than 0.

  • Combining constraints ensures data remains valid and meaningful.

Practical Use Cases

  1. Status Columns: Default 'active' for new users or students.

  2. Timestamps: Automatically assign CURRENT_DATE or CURRENT_TIMESTAMP.

  3. Boolean Flags: Default is_verified to FALSE.

  4. Inventory Management: Default stock to 0 for new products.

  5. Order Processing: Default payment or order status to 'pending'.

  6. Application Logic Simplification: Reduces the need to explicitly provide repeated values.

  7. Ensures Consistency: Guarantees uniform initial values across all records.

Best Practices

  1. Use Meaningful Defaults: Choose defaults that make sense for the business logic.

  2. Combine with NOT NULL: Ensures columns always have valid values.

  3. Avoid Overusing Defaults: Only assign defaults where it adds value.

  4. Test Insertions Thoroughly: Verify that default values apply correctly in all scenarios.

  5. Use System Functions for Dates: CURRENT_DATE or CURRENT_TIMESTAMP is ideal for tracking creation times.

Common Mistakes to Avoid

  1. Assuming Defaults Apply to Updates: DEFAULT only works on inserts, not updates.

  2. Type Mismatch: Default value type must match column type.

  3. Ignoring NULL: Columns without NOT NULL can still store NULL despite a default.

  4. Overwriting Defaults: Explicitly providing empty strings or zeros can override intended defaults.

  5. Overuse of Defaults: Unnecessary defaults can hide logical errors in application data handling.

Summary of the Tutorial

The MySQL DEFAULT constraint is a powerful feature for automatically assigning initial values to columns:

  • Ensures columns always have meaningful values.

  • Supports numeric, string, date, and boolean types.

  • Works effectively with NOT NULL, CHECK, and UNIQUE constraints.

  • Simplifies data insertion and improves consistency.

  • Helps maintain reliable, predictable, and accurate databases.

Using DEFAULT values wisely reduces errors and makes your database more robust and easier to manage.

Practice Questions

  1. Create a students table with status column defaulting to 'active' and enrollment_date defaulting to the current date.

  2. Create a products table with stock defaulting to 0 and is_available defaulting to TRUE.

  3. Insert a new student into the students table without specifying the status or enrollment_date and observe the default values applied.

  4. Insert a new product into the products table specifying only product_name and price, leaving stock and is_available to use defaults.

  5. Modify an existing orders table to set a default status of 'pending'.

  6. Create an employees table with salary defaulting to 30000 and is_active defaulting to TRUE.

  7. Insert multiple rows into the employees table omitting the salary column and verify that defaults are applied.

  8. Update the status of a student explicitly and check that the default value is not applied.

  9. Create an orders table with quantity defaulting to 1 and a CHECK constraint ensuring quantity > 0.

  10. Add a DEFAULT value to an existing column last_login in the users table using CURRENT_TIMESTAMP.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top