DEFAULT Constraint in SQL

The DEFAULT constraint automatically supplies a value when an INSERT statement does not provide a value for that column.

CREATE TABLE students (
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

If no status is provided, Active can be used automatically.

DEFAULT reduces the need to repeatedly provide common values.

For example, a new student can automatically receive:

• Active status
• Default city
• Default country
• Default quantity

The basic syntax is:

column_name data_type DEFAULT value

Example:

status VARCHAR(20) DEFAULT 'Active'

DEFAULT can be used with text columns.

CREATE TABLE students (
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active'
);

If status is omitted during INSERT, the default value can be used.

DEFAULT can also be used with numeric columns.

CREATE TABLE products (
    product_name VARCHAR(100),
    quantity INT DEFAULT 0
);

If quantity is not supplied, its default value is 0.

Decimal columns can have default numeric values.

CREATE TABLE products (
    product_name VARCHAR(100),
    discount DECIMAL(5,2) DEFAULT 0.00
);

If no discount is provided, 0.00 can be used.

In MySQL, a date column can use a suitable default expression depending on the column definition and server version.

CREATE TABLE admissions (
    student_id INT,
    admission_date DATE DEFAULT (CURRENT_DATE)
);

This can automatically use the current date when the column is omitted.

DATETIME columns can use an appropriate default expression in supported MySQL versions.

CREATE TABLE logs (
    message VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

The timestamp can be generated automatically when a row is inserted.

When a column has a DEFAULT value and you omit that column from INSERT, the database can use the default value.

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

INSERT INTO students (id, name)
VALUES (1, 'Rahul');

The status can automatically become Active.

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

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

Here, Inactive is stored instead of the default Active.

DEFAULT and NOT NULL can be used together.

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

The column requires a value, while the default provides one when the column is omitted from INSERT.

A DEFAULT constraint alone does not necessarily prevent NULL values.

CREATE TABLE students (
    status VARCHAR(20) DEFAULT 'Active'
);

If NULL is explicitly supplied, the behavior differs from simply omitting the column.

INSERT INTO students (status)
VALUES (NULL);

An empty string is an actual string value and is different from omitting a column.

INSERT INTO students (status)
VALUES ('');

This explicitly supplies an empty string, so the DEFAULT value is not automatically selected.

DEFAULT is generally applied when the column is omitted from INSERT. Explicitly inserting NULL is a separate operation.

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

If status has a default, the default can be used.

A table can contain multiple columns with default values.

CREATE TABLE students (
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'Active',
    country VARCHAR(50) DEFAULT 'India',
    fee_paid INT DEFAULT 0
);

Each column has its own default value.

Student management systems commonly use a default status.

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

New students can automatically receive an Active status.

A payment table can use a default payment status.

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

New payment records can start with a Pending status.

Inventory systems often use zero as the default quantity.

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

If quantity is omitted, it can start at 0.

A default country can be useful when most records belong to the same country.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50) DEFAULT 'India'
);

The country can be automatically set when it is omitted.

In MySQL, you can modify an existing column using ALTER TABLE.

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

This changes the column definition to include a default value.

You can change the default value of a column.

ALTER TABLE students
ALTER status SET DEFAULT 'Inactive';

Syntax can vary between database systems, so always check the SQL dialect you are using.

In MySQL, you can remove a default value using ALTER TABLE.

ALTER TABLE students
ALTER status DROP DEFAULT;

After removing the default, the column no longer has that default value.

A primary key usually identifies records using generated or explicitly supplied values. DEFAULT can be used only when the database and column definition support the chosen default.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

In practice, auto-generated keys are commonly handled with AUTO_INCREMENT in MySQL.

In MySQL, AUTO_INCREMENT is commonly used for automatically generated integer IDs.

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

Here, the ID is generated automatically and status has its own default.

DEFAULT is mainly used when inserting rows. UPDATE does not automatically replace an existing value with the column's default.

UPDATE students
SET status = 'Inactive'
WHERE student_id = 1;

This explicitly changes the status to Inactive.

SQL supports the DEFAULT keyword for explicitly requesting a column's default value in systems that support it.

INSERT INTO students (student_id, name, status)
VALUES (1, 'Rahul', DEFAULT);

The database uses the defined default for status.

A common mistake is expecting DEFAULT to replace every invalid or missing value automatically.

DEFAULT is used according to the INSERT operation and column definition. It does not automatically fix every NULL, empty string, or invalid value.

DEFAULT values can help maintain consistent data.

For example, using Active as the default student status ensures that newly inserted students have a predictable starting status when the status column is omitted.

Let's create a practical student table using DEFAULT.

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

INSERT INTO students (name, course)
VALUES ('Rahul', 'Python');

The student can automatically receive Active status and 0.00 fee paid.

Here is a complete example combining DEFAULT with NOT NULL and AUTO_INCREMENT.

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

INSERT INTO students
(name, mobile, course)
VALUES
('Rahul', '9876543210', 'Python'),
('Priya', '9876543211', 'SQL');

The IDs are generated automatically, status defaults to Active, and fee paid defaults to 0.00 when those columns are omitted.