DEFAULT values in SQL

Introduction

DEFAULT values let you set a starting value for a column when no value is given. This helps keep data complete and consistent.

When you want a column to have a common starting value if no value is provided.

When you want to avoid errors from missing data during inserts.

When you want to simplify data entry by not always typing the same value.

When you want to ensure a column never stays empty or NULL unintentionally.

Syntax

SQL

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value
);

DEFAULT value can be a number, string, or function like CURRENT_DATE.

DEFAULT is used during INSERT when no value is specified for that column.

Examples

This creates a table where the status is 'active' if not given.

SQL

CREATE TABLE users (
  id INT,
  name VARCHAR(50),
  status VARCHAR(10) DEFAULT 'active'
);

Here, order_date defaults to the current date if not provided.

SQL

CREATE TABLE orders (
  order_id INT,
  order_date DATE DEFAULT CURRENT_DATE
);

Quantity starts at 0 if no value is inserted.

SQL

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

Sample Program

This creates an employees table with a default role 'staff'. Alice gets the default role because none was given. Bob's role is set explicitly.

SQL

CREATE TABLE employees (
  emp_id INT,
  emp_name VARCHAR(50),
  role VARCHAR(20) DEFAULT 'staff'
);

INSERT INTO employees (emp_id, emp_name) VALUES (1, 'Alice');
INSERT INTO employees (emp_id, emp_name, role) VALUES (2, 'Bob', 'manager');

SELECT * FROM employees;

Important Notes

DEFAULT values only apply when you omit the column in INSERT statements.

If you insert NULL explicitly, DEFAULT does not apply; NULL is stored.

Summary

DEFAULT sets a value automatically when none is provided.

It helps keep data consistent and reduces errors.

Use it for common or safe starting values in columns.