SQL Notes
How to use the DEFAULT constraint in SQL to provide automatic fallback values when inserting data, keep columns populated, and reduce boilerplate INSERT statements with practical examples.
When you insert a new row into a table, you might not always have a value for every column. Maybe the order status is always "Pending" at first, or the registration date should automatically be today. This is exactly where the DEFAULT constraint comes in handy.
The DEFAULT constraint automatically assigns a predefined value to a column whenever you insert a row without explicitly providing a value for that column. Think of it as a safety net that catches missing data and fills it in with something sensible, so your table never has unexpected NULL values in important columns.
Basic Syntax
You define a DEFAULT constraint when creating a table by adding the DEFAULT keyword followed by the value after the column definition:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL,
OrderDate DATE DEFAULT CURRENT_DATE,
Status VARCHAR(20) DEFAULT 'Pending',
Priority VARCHAR(10) DEFAULT 'Normal',
TotalAmount DECIMAL(10,2) DEFAULT 0.00
);Now when you insert an order without specifying Status, OrderDate, Priority, or TotalAmount:
INSERT INTO Orders (OrderID, CustomerName)
VALUES (1, 'Rahul Sharma');The database automatically fills in the rest:
| OrderID | CustomerName | OrderDate | Status | Priority | TotalAmount |
|---|---|---|---|---|---|
| 1 | Rahul Sharma | 2026-06-14 | Pending | Normal | 0.00 |
Types of DEFAULT Values
The DEFAULT constraint supports several types of values:
Constant Values
These are fixed literals like strings, numbers, or booleans:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50) NOT NULL,
Role VARCHAR(20) DEFAULT 'User',
IsActive BOOLEAN DEFAULT TRUE,
LoginAttempts INT DEFAULT 0,
Bio TEXT DEFAULT 'No bio provided'
);Function-Based Defaults
You can use built-in functions to generate dynamic default values:
CREATE TABLE AuditLog (
LogID INT PRIMARY KEY,
Action VARCHAR(100),
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
LogDate DATE DEFAULT CURRENT_DATE
);Common functions used with DEFAULT include CURRENT_DATE or CURDATE for today's date, CURRENT_TIMESTAMP or NOW for the current date and time, and UUID for generating a unique identifier.
Adding DEFAULT to an Existing Table
If your table already exists and you want to add a default value, use ALTER TABLE. The syntax varies slightly between databases:
MySQL and PostgreSQL
ALTER TABLE Orders
ALTER COLUMN Status SET DEFAULT 'Pending';SQL Server
ALTER TABLE Orders
ADD CONSTRAINT df_status DEFAULT 'Pending' FOR Status;Removing a DEFAULT Constraint
To drop a default value from a column:
MySQL and PostgreSQL
ALTER TABLE Orders
ALTER COLUMN Status DROP DEFAULT;SQL Server
ALTER TABLE Orders
DROP CONSTRAINT df_status;Real-World Example: Student Registration System
Let us build a practical example. A university registration system needs sensible defaults:
CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL,
EnrollmentDate DATE DEFAULT CURRENT_DATE,
Semester INT DEFAULT 1,
Status VARCHAR(20) DEFAULT 'Active',
CGPA DECIMAL(3,2) DEFAULT 0.00,
LibraryAccess BOOLEAN DEFAULT TRUE
);Now registering a student requires minimal information:
INSERT INTO Students (FullName, Email)
VALUES ('Priya Patel', 'priya.patel@university.edu');Result:
| StudentID | FullName | EnrollmentDate | Semester | Status | CGPA | LibraryAccess | |
|---|---|---|---|---|---|---|---|
| 1 | Priya Patel | priya.patel@university.edu | 2026-06-14 | 1 | Active | 0.00 | TRUE |
Real-World Example: Employee Management
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Department VARCHAR(50) DEFAULT 'Unassigned',
JoinDate DATE DEFAULT CURRENT_DATE,
Salary DECIMAL(10,2) DEFAULT 30000.00,
ShiftType VARCHAR(20) DEFAULT 'Day'
);INSERT INTO Employees (EmpID, Name)
VALUES (101, 'Ankit Verma');| EmpID | Name | Department | JoinDate | Salary | ShiftType |
|---|---|---|---|---|---|
| 101 | Ankit Verma | Unassigned | 2026-06-14 | 30000.00 | Day |
DEFAULT with NOT NULL
Combining DEFAULT with NOT NULL is a powerful pattern. NOT NULL ensures the column always has a value, and DEFAULT provides that value when none is given:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(200) NOT NULL,
Stock INT NOT NULL DEFAULT 0,
Category VARCHAR(50) NOT NULL DEFAULT 'Uncategorized',
IsAvailable BOOLEAN NOT NULL DEFAULT TRUE
);This guarantees that Stock is never NULL. It will either be explicitly set or default to zero.
Overriding the DEFAULT Value
The default only applies when you do not provide a value. If you explicitly pass a value, it overrides the default:
-- Uses default Status ('Pending')
INSERT INTO Orders (OrderID, CustomerName)
VALUES (2, 'Amit Kumar');
-- Overrides default Status
INSERT INTO Orders (OrderID, CustomerName, Status)
VALUES (3, 'Sneha Verma', 'Confirmed');You can also explicitly request the default using the DEFAULT keyword:
INSERT INTO Orders (OrderID, CustomerName, Status)
VALUES (4, 'Vikas Gupta', DEFAULT);Common Mistakes to Avoid
Mistake 1: Assuming DEFAULT works on UPDATE. DEFAULT only applies during INSERT. If you update a row and set a column to NULL where allowed, it does not revert to the default value.
-- This sets Status to NULL, NOT back to 'Pending'
UPDATE Orders SET Status = NULL WHERE OrderID = 1;Mistake 2: Using DEFAULT with incompatible data types. The default value must match the column's data type:
-- Wrong: String default for INT column
Age INT DEFAULT 'unknown' -- ERROR
-- Correct
Age INT DEFAULT 0Mistake 3: Forgetting that explicit NULL overrides DEFAULT. If a column allows NULL and you explicitly insert NULL, the default is not used:
INSERT INTO Orders (OrderID, CustomerName, Status)
VALUES (5, 'Ravi Singh', NULL); -- Status will be NULL, not 'Pending'Best Practices
- Always pair DEFAULT with NOT NULL for columns that must have a value
- Use meaningful defaults that represent the most common initial state
- Use CURRENT_TIMESTAMP for audit columns like created_at and updated_at
- Name your constraints in SQL Server for easier management later
- Test your defaults by inserting rows without specifying those columns
- Document your defaults so other developers know what to expect
Quick Summary
| Feature | Behavior |
|---|---|
| When applied | INSERT only, not UPDATE |
| Can use functions | Yes such as CURRENT_DATE and NOW |
| Works with NOT NULL | Yes, and recommended |
| Can be overridden | Yes, by providing explicit value |
| Supports expressions | Yes in most modern databases |
The DEFAULT constraint is one of the simplest yet most practical tools in SQL. It keeps your data consistent, your code clean, and your application logic centralized where it belongs — in the database.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for DEFAULT Constraint in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, default, constraint
Related SQL Complete Guide Topics