DEFAULT Constraint in SQL

đŸŽ¯ The DEFAULT constraint in SQL is used to automatically assign a predefined value to a column when no value is provided during an INSERT operation. It helps ensure consistency, reduces manual data entry, and prevents unnecessary NULL values.

📖 What is the DEFAULT Constraint?

A DEFAULT constraint specifies a value that the database should use automatically whenever an INSERT statement omits that column. If a value is explicitly provided, the supplied value is stored instead of the default.

Information

The DEFAULT constraint is applied only when no value is supplied for the column. It does not replace explicitly provided values.

đŸŽ¯ Why Use DEFAULT?

The DEFAULT constraint simplifies data entry and enforces consistent default values across records.

  • 📌 Automatically populate common values.
  • 📌 Reduce repetitive data entry.
  • 📌 Improve data consistency.
  • 📌 Minimize unnecessary NULL values.
  • 📌 Simplify application development.

📝 Basic Syntax

DEFAULT Constraint Syntax

CREATE TABLE table_name
(
    column_name data_type DEFAULT default_value
);

💡 Create a Table with DEFAULT

Create a Students table where the Status column automatically stores 'Active' if no value is provided.

DEFAULT Example

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(100),
    Status VARCHAR(20) DEFAULT 'Active'
);

✅ INSERT Without Providing the Default Column

Since no value is supplied for Status, the default value is used automatically.

Using the Default Value

INSERT INTO Students
(StudentID, Name, Department)
VALUES
(101, 'Alice', 'Computer Science');

Result:

StudentIDNameDepartmentStatus
101AliceComputer ScienceActive

✏ī¸ INSERT with an Explicit Value

If a value is explicitly provided, it overrides the default.

Override the Default

INSERT INTO Students
(StudentID, Name, Department, Status)
VALUES
(102, 'Bob', 'Mathematics', 'Inactive');

The Status column stores 'Inactive' instead of the default value.

📅 Using Built-in Functions as DEFAULT Values

Many database systems allow built-in functions as default values, such as the current date or current timestamp.

DEFAULT CURRENT_DATE

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE DEFAULT CURRENT_DATE
);

DEFAULT CURRENT_TIMESTAMP

CREATE TABLE LoginHistory
(
    LoginID INT PRIMARY KEY,
    Username VARCHAR(100),
    LoginTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Important

Built-in date and time functions supported as default values vary between database systems. For example, SQL Server commonly uses GETDATE(), while PostgreSQL and MySQL commonly support CURRENT_TIMESTAMP.

➕ Add a DEFAULT Constraint to an Existing Table

You can add a default value after a table has already been created. The syntax differs among database systems.

SQL Server Example

ALTER TABLE Students
ADD CONSTRAINT DF_Students_Status
DEFAULT 'Active' FOR Status;

MySQL Example

ALTER TABLE Students
ALTER Status
SET DEFAULT 'Active';

➖ Remove a DEFAULT Constraint

Removing a default constraint is database-specific.

MySQL Example

ALTER TABLE Students
ALTER Status
DROP DEFAULT;

Warning

In SQL Server, a default constraint must typically be dropped by its constraint name before another default can be assigned.

📊 Common DEFAULT Examples

ColumnDefault ValuePurpose
Status 'Active'Automatically assign an active status.
Quantity 0Initialize numeric values.
Country 'India'Provide a common default location.
CreatedDate CURRENT_DATEStore the creation date automatically.
CreatedAt CURRENT_TIMESTAMPRecord the creation timestamp.

⚖ī¸ DEFAULT vs NOT NULL

FeatureDEFAULTNOT NULL
Automatically Provides a Value✅ Yes❌ No
Requires a Value❌ Not necessarily✅ Yes
Prevents NULLOnly if the default is used.✅ Always
Main PurposeAssign a default value.Require a value.

đŸ’ŧ Real-World Example

In an e-commerce application, every newly created order should initially have the status 'Pending' unless another status is explicitly specified.

Orders Table

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderStatus VARCHAR(20) DEFAULT 'Pending',
    OrderDate DATE DEFAULT CURRENT_DATE
);

This design automatically assigns a default order status and records the order date when a new order is created.

⚠ī¸ Common Mistakes

  • ❌ Assuming the default value replaces explicitly supplied values.
  • ❌ Expecting existing rows to automatically receive a newly added default.
  • ❌ Using invalid expressions as default values.
  • ❌ Assuming every database supports the same default functions and syntax.

Warning

A DEFAULT constraint affects only future inserts where the column is omitted. Existing rows are generally not updated automatically.

⚠ī¸ Best Practices

Best Practice

Use meaningful default values that match business requirements, combine DEFAULT with NOT NULL for mandatory fields when appropriate, use built-in date and time functions for audit columns, assign descriptive names to default constraints where supported, and verify syntax for your specific database system.

🚀 Key Points to Remember

  • 📌 DEFAULT automatically supplies a value when one is not provided.
  • 📌 Explicitly provided values always override the default.
  • 📌 Built-in functions such as CURRENT_DATE or CURRENT_TIMESTAMP are commonly used as defaults.
  • 📌 Default syntax varies slightly across SQL database systems.
  • 📌 Existing rows are generally unaffected when a new default is added.
  • 📌 DEFAULT improves consistency and reduces repetitive data entry.
>>"Good defaults make databases easier to use by automatically providing sensible values when users omit them."

Summary

✅ The DEFAULT constraint is a valuable SQL feature that automatically assigns predefined values during data insertion. It improves consistency, reduces manual input, and works well alongside constraints such as NOT NULL, CHECK, and PRIMARY KEY to build reliable and maintainable database applications.