MySQL DEFAULT Constraint

The DEFAULT constraint specifies a value that MySQL can automatically use when a value is not provided.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'Active'
);

If status is omitted during INSERT, MySQL uses Active.

DEFAULT values are useful when a column commonly has the same starting value.

• Student status
• Payment status
• Country
• Quantity
• Created date
• Active/inactive status

The basic syntax is:

column_name data_type DEFAULT default_value

Example:

status VARCHAR(20) DEFAULT 'Active'

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

The default status for a new student is Active when status is not supplied.

INSERT INTO students
(name)
VALUES
('Amit');

Because status is omitted, MySQL uses the default value:

Active

You can provide your own value instead of using the default.

INSERT INTO students
(name, status)
VALUES
('Priya', 'Inactive');

Here, the default value is not used because a status value was explicitly supplied.

DEFAULT can be combined with NOT NULL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

If status is omitted, MySQL uses Active. If an explicit NULL is supplied, the NOT NULL constraint prevents it.

DEFAULT can be used with numeric columns.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    quantity INT DEFAULT 0
);

If quantity is omitted, MySQL uses 0.

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    amount DECIMAL(10,2) DEFAULT 0.00
);

If amount is omitted, the default amount is 0.00.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100) DEFAULT 'General'
);

If department is not supplied, MySQL uses General.

MySQL supports appropriate date/time expressions as default values. For example, a DATETIME column can use the current timestamp.

CREATE TABLE admissions (
    admission_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

If created_at is omitted, MySQL uses the current date and time.

CURRENT_TIMESTAMP is commonly used for automatically storing the current date and time.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

When a new user is inserted without created_at, MySQL records the current timestamp.

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10,2),
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The payment_date is automatically filled when a payment record is inserted without specifying it.

Consider this table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

Insert without status:

INSERT INTO students (name)
VALUES ('Amit');

The resulting status is Active.

If you provide a value, MySQL uses that value instead of the DEFAULT.

INSERT INTO students
(name, status)
VALUES
('Rahul', 'Inactive');

The status will be Inactive, not Active.

You can explicitly request the default value using the DEFAULT keyword.

INSERT INTO students
(name, status)
VALUES
('Neha', DEFAULT);

MySQL uses the defined default value for status.

A table can contain multiple columns with DEFAULT values.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Active',
    fee DECIMAL(10,2) DEFAULT 0.00,
    country VARCHAR(50) DEFAULT 'India'
);

Each column has its own default value.

You can change a column definition using ALTER TABLE.

ALTER TABLE students
ALTER COLUMN status SET DEFAULT 'Inactive';

In MySQL, another common approach is to redefine the column:

ALTER TABLE students
MODIFY status VARCHAR(20) DEFAULT 'Inactive';

The exact statement should preserve the existing column attributes that you want to keep.

You can remove a default value using ALTER TABLE.

ALTER TABLE students
ALTER COLUMN status DROP DEFAULT;

You can also redefine the column without a DEFAULT clause:

ALTER TABLE students
MODIFY status VARCHAR(20) NOT NULL;

Use DESCRIBE to inspect column definitions.

DESCRIBE students;

The Default column displays the default value where one is defined.

SHOW CREATE TABLE displays the complete definition of a table.

SHOW CREATE TABLE students;

This can show DEFAULT, NOT NULL, PRIMARY KEY, UNIQUE, and other definitions.

Status columns commonly use DEFAULT values.

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

New users automatically receive the Active status unless another valid status is supplied.

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

A new payment can automatically start with a Pending status.

• Confusing DEFAULT with NOT NULL
• Assuming DEFAULT prevents NULL
• Assuming DEFAULT prevents duplicate values
• Forgetting that an explicitly supplied value overrides the default
• Using an inappropriate default value for the data type
• Changing a column definition without preserving required attributes
• Assuming DEFAULT changes existing rows automatically

A DEFAULT value is primarily used for future INSERT operations where the column value is omitted.

ALTER TABLE students
MODIFY status VARCHAR(20) DEFAULT 'Active';

This does not automatically change every existing row's status to Active.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

INSERT INTO students
(student_code, name, course)
VALUES
('STU001', 'Amit', 'Python');

The fee becomes 0.00 and status becomes Active.

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO payments
(student_id, amount)
VALUES
(1, 5000.00);

The payment status becomes Pending, and payment_date is automatically set to the current timestamp.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    admission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO students
(student_code, name, course)
VALUES
('STU001', 'Amit Kumar', 'Python');

INSERT INTO students
(student_code, name, course, fee, status)
VALUES
('STU002', 'Priya Singh', 'Java', 15000.00, 'Active');

SELECT *
FROM students;

DESCRIBE students;

SHOW CREATE TABLE students;

In this example, fee, status, and admission_date have useful default values. Values can still be explicitly supplied when required.