? MySQL DEFAULT Constraint

? Quick Overview

The DEFAULT constraint in MySQL automatically provides a value for a column when no value is specified during an INSERT operation.

Ensures every row has a meaningful value (e.g., default status as 'Active').
Reduces the need to repeat the same values in every insert statement.
Improves data consistency and minimizes errors due to missing values.

⭐ Common use: status, created date, flags

? Key Concepts

DEFAULT value is used only when you omit that column in an INSERT statement.
The default value must match the column’s data type (string, number, date, etc.).
You can define defaults while creating a table or when modifying an existing table.
Explicit values always override the default.

? Syntax

Basic syntax for using the DEFAULT constraint when creating a table:

? View Code Example

-- DEFAULT constraint syntax in MySQL
CREATE TABLE table_name (
Status VARCHAR(100) DEFAULT 'Active'
);

Here, if you insert a row without specifying Status, MySQL automatically sets it to 'Active'.

? Example: Employees Table

Let’s create an Employees table where the Status column defaults to 'Active' when not provided.

? View Code Example

-- Create table with DEFAULT on Status
CREATE TABLE Employees (
ID INT NOT NULL,
Name VARCHAR(100),
Status VARCHAR(100) DEFAULT 'Active',
Salary DECIMAL(10,2)
);

Now insert some data with and without specifying the Status column:

? View Insert Example

-- Insert rows: with and without Status
INSERT INTO Employees (ID, Name, Salary)
VALUES (1, 'Alice', 50000.00);

INSERT INTO Employees (ID, Name, Status, Salary)
VALUES (2, 'Bob', 'Inactive', 45000.00);

? What Happens When You Insert Rows?

Resulting data (conceptual view)

After running the above INSERT statements, the table will look like this:

Row 1: ID = 1, Name = Alice, Status = 'Active' (taken from DEFAULT), Salary = 50000.00
Row 2: ID = 2, Name = Bob, Status = 'Inactive' (explicitly provided), Salary = 45000.00

Because the first insert did not mention Status, MySQL used the default value 'Active'. In the second insert, we explicitly set 'Inactive', so the default is not used.

✅ Tips & Best Practices

Use DEFAULT for columns that should almost always have a standard value (e.g., status, flags, creation time).
Ensure the default value matches the column’s data type (e.g., string for VARCHAR, number for INT).
Remember: the default is applied only when the column is omitted in the INSERT statement.
Combine DEFAULT with NOT NULL to guarantee that the column never stores NULL.

? Try It Yourself

Create a table Projects with a Status column that defaults to 'Pending'.
Insert rows into Projects:
- One row without Status (should use the default).
- One row with Status = 'Completed' (should override the default).
Modify an existing table to add a new column with a DEFAULT value using ALTER TABLE.
Experiment with changing the default value and observe how it affects future inserts.