SQL Default Constraint

The DEFAULT constraint is used to set a default fallback value for a column. If an INSERT statement is executed without providing an explicit value for that column, the database engine automatically fills it with the predefined default value.

Default values help maintain consistency, reduce redundant code in client applications, and ensure required fields are never left unpopulated.

Basic Syntax & Examples

A DEFAULT constraint can be assigned literal values, system functions (like dates/timestamps), or logical expressions.

1. Specifying Defaults During Table Creation

CREATE TABLE Customers (
    CustomerID     INT PRIMARY KEY,
    FirstName      VARCHAR(50) NOT NULL,
    LastName       VARCHAR(50) NOT NULL,
    Country        VARCHAR(50) DEFAULT 'USA',
    IsActive       BOOLEAN DEFAULT TRUE,
    CreatedDate    DATETIME DEFAULT CURRENT_TIMESTAMP
);

In this table:

• If Country is omitted during an insert, it defaults to 'USA'.
• If IsActive is omitted, it defaults to TRUE.
• If CreatedDate is omitted, it defaults to the system's current date and time (CURRENT_TIMESTAMP).

How DEFAULT Behaves During Inserts

Example 1: Omitting the Column

When you omit the defaulted column from your INSERT target list, the engine applies the default value automatically:

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (101, 'Jane', 'Doe');

Resulting Row:

Example 2: Using the DEFAULT Keyword Explicitly

You can also pass the explicit keyword DEFAULT in place of a value in your INSERT query:

INSERT INTO Customers (CustomerID, FirstName, LastName, Country, IsActive)
VALUES (102, 'Alex', 'Smith', DEFAULT, FALSE);

Example 3: Explicitly Passing NULL (Important Distinction)

Passing NULL explicitly overrides the DEFAULT constraint and inserts NULL into the column (provided the column permits NULL values):

-- This inserts NULL into Country, NOT 'USA'
INSERT INTO Customers (CustomerID, FirstName, LastName, Country)
VALUES (103, 'Sam', 'Wilson', NULL);

Key Rule: A DEFAULT constraint triggers only when a value is omitted or the DEFAULT keyword is supplied. It does not override an explicit NULL value.

Adding or Modifying a DEFAULT Constraint

If a table already exists, you can attach or update a default constraint using ALTER TABLE. Syntax variations exist across RDBMS engines:

-- PostgreSQL
ALTER TABLE Customers
ALTER COLUMN Country SET DEFAULT 'USA';

-- MySQL / MariaDB
ALTER TABLE Customers
ALTER COLUMN Country SET DEFAULT 'USA';

-- SQL Server (Requires a named constraint)
ALTER TABLE Customers
ADD CONSTRAINT DF_Customers_Country DEFAULT 'USA' FOR Country;

Dropping a DEFAULT Constraint

To remove a default constraint from a column:

-- PostgreSQL / MySQL
ALTER TABLE Customers
ALTER COLUMN Country DROP DEFAULT;

-- SQL Server
ALTER TABLE Customers
DROP CONSTRAINT DF_Customers_Country;

Common Uses for DEFAULT Constraints

1. Audit Timestamps: Automatically logging record creation dates using CURRENT_TIMESTAMP or GETDATE().
2. Status Flags: Setting initial boolean status flags (e.g., IsActive DEFAULT TRUE, IsVerified DEFAULT FALSE).
3. Numeric Counters: Initializing tracking counters or balances to zero (e.g., LoginAttempts INT DEFAULT 0).
4. Geographic Fallbacks: Providing default country, currency, or language settings (e.g., CurrencyCode VARCHAR(3) DEFAULT 'USD').