DEFAULT Constraint in SQL
Introduction
The SQL DEFAULT clause specifies the value a database should use when an INSERT statement omits a column. It gives a missing input a deliberate initial meaning and reduces repeated insert logic across applications, scripts, imports, and administrative tools.
CREATE TABLE employees (
employee_id BIGINT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
status VARCHAR(20) DEFAULT 'ACTIVE'
);
INSERT INTO employees (employee_id, employee_name)
VALUES (1, 'John');
Because status is omitted, the row receives ACTIVE. A default is applied by insertion semantics; it is not a validation rule, a mandatory-value rule, or an automatic update mechanism.
What a DEFAULT Means
A DEFAULT answers one precise question: if this column is not supplied during insert, what value should the database assign? The answer may be a constant such as PENDING or zero, or a supported expression such as the current timestamp.
The value becomes ordinary stored data after insertion. It can usually be overridden by an explicit valid value unless other constraints prohibit that value.
Basic Syntax
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
stock_quantity INTEGER DEFAULT 0
);
When stock_quantity is omitted, zero is stored. Whether zero is correct depends on the domain: it may mean confirmed empty inventory, while NULL could mean inventory has not yet been counted.
Why Defaults Are Useful
If every new order genuinely begins as PENDING, repeating that literal in every writer creates duplication and drift. A database default centralizes the initial state and gives old or simple clients a consistent behavior.
Convenience is not enough. The default must be the one correct meaning whenever the field is omitted, regardless of which valid writer performs the insert.
DEFAULT Is a Fallback, Not Validation
A default does not restrict other values. A column with DEFAULT 'ACTIVE' may still accept UNKNOWN, BROKEN, or any other type-compatible string unless a CHECK, foreign key, or application rule limits the domain.
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
CONSTRAINT chk_employee_status
CHECK (status IN ('ACTIVE', 'INACTIVE'))
DEFAULT supplies an omitted value; CHECK validates the selected value. They have separate responsibilities and often work together.
DEFAULT Does Not Mean NOT NULL
A default can coexist with a nullable column. If omission should produce a value but explicit unknown is still meaningful, this can be intentional. If the column must always contain a value, add NOT NULL explicitly.
A strong required status definition therefore combines the data type, NOT NULL, DEFAULT, and an allowed-domain constraint rather than expecting DEFAULT to perform every role.
Omitted Column Versus Explicit NULL
This distinction is fundamental. Omitting a column asks the database to use its default. Explicitly inserting NULL normally asks the database to store NULL. The default does not replace an explicit NULL.
-- Uses the default:
INSERT INTO accounts (account_id) VALUES (101);
-- Supplies NULL explicitly:
INSERT INTO accounts (account_id, status)
VALUES (102, NULL);
If status is NOT NULL, the second statement fails. API and ORM layers must preserve the difference between absent, NULL, and an explicit value.
Using the DEFAULT Keyword in INSERT
Many database systems let an INSERT explicitly request the defined default.
INSERT INTO orders (order_id, status)
VALUES (5001, DEFAULT);
This can be clearer when a generated statement includes the column but intentionally delegates its value to the schema. Syntax support differs across databases.
Always Prefer Explicit Column Lists
An INSERT without a column list depends on physical column order and must provide a value for every expected position. Schema evolution can break it or place values in the wrong columns.
Explicit column lists work naturally with defaults: supply values the operation owns and omit fields whose initial values are owned by the schema.
Numeric and Decimal Defaults
Zero can be appropriate for confirmed counts, counters, monetary accumulators, and discount rates when zero has genuine meaning. It is dangerous when zero merely hides missing source data.
retry_count INTEGER NOT NULL DEFAULT 0,
discount_rate DECIMAL(5,4) NOT NULL DEFAULT 0
The default must fit the type and satisfy every CHECK constraint. Units, precision, and scale should be documented.
String and Status Defaults
Status is a common candidate because many workflows have one universal initial state. A new order may be PENDING, a support ticket OPEN, or a draft document DRAFT.
Do not use vague values such as UNKNOWN merely to satisfy NOT NULL. Unknown, not yet classified, and a legitimate starting state are different business facts.
Boolean Defaults and Three-State Logic
A Boolean default of false is valid only when omission truly means false. For consent, review, verification, or eligibility, missing information may be unknown rather than false.
Choose among NOT NULL with a Boolean default, nullable Boolean, or an explicit multi-state status based on meaning. Multiple flags can create contradictory states that one well-designed status avoids.
Date and Timestamp Defaults
Creation timestamps commonly use a database expression representing the current time.
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Define whether the value represents database insertion time, business event time, ingestion time, or source-system time. One timestamp cannot safely represent all of them.
created_at and updated_at Are Different
A DEFAULT primarily acts during INSERT. It can initialize created_at and may initialize updated_at, but it does not normally refresh the latter on every update.
Automatic update behavior needs DBMS-specific column features, a trigger, generated update SQL, or application logic. Name and document the owner so different layers do not overwrite each other unpredictably.
Constant Defaults and Expression Defaults
Constants are simple and portable: zero, true, PENDING, or a fixed code. Expression defaults can generate timestamps, UUIDs, sequence values, or context-dependent values where supported.
Expression rules vary substantially by DBMS. Prefer deterministic, understandable behavior and verify restore, replication, time zone, and session semantics.
Fake Defaults Hide Missing Data
Values such as 1900-01-01, -1, N/A, UNKNOWN, or an arbitrary parent ID are often used to avoid NULL. They blur the difference between a real value and absent information, distort aggregates, and leak into reports and APIs.
Use NULL when unknown is a valid state, require input when it is not, or create an explicit governed domain member only when it has genuine semantics.
Currency, Country, and Locale
A universal currency or country default may be correct for a single-market system but dangerous in a global application. Guessing USD or USA because a writer omitted context can create financially wrong records that still look valid.
If the correct value depends on tenant, customer, account, region, or contract, derive it through context-aware business logic rather than a static table default.
Tenant and Security Context
Defaulting a tenant ID, owner ID, role, approval flag, or permission can cross security boundaries. Connection pooling makes session-based identity especially risky if context is stale or missing.
Security-sensitive fields should fail closed. Require authenticated context, validate ownership, and do not rely on a convenient default as the authorization decision.
Current User and Audit Fields
Some systems support defaults based on the database user or session context. That may record a shared service account rather than the human actor, particularly behind connection pools.
Define whether audit identity means database principal, application user, API client, job, or source system. Pass and validate the correct identity rather than assuming the connection user provides the needed evidence.
DEFAULT and CHECK
The default must satisfy every CHECK constraint for the row. A default of 0 conflicts with CHECK (quantity > 0), so omission causes every insert to fail.
Review defaults and checks as one contract. Test omission explicitly after any change to the allowed domain.
DEFAULT and FOREIGN KEY
A default foreign-key value must reference a valid parent when foreign-key enforcement applies. Setting department_id DEFAULT 0 does not bypass referential integrity.
A default parent should exist only when it represents a real, governed state such as an intentionally defined unassigned department. Otherwise, use NULL for an optional relationship or require the caller to choose a parent.
DEFAULT and UNIQUE
A static default on a UNIQUE column is suspicious because the second omitted insert may conflict with the first. Defaults for unique identifiers should generate distinct values through an identity, sequence, UUID expression, or another safe mechanism.
Do not assume every generated expression is collision-free or portable. Test concurrency and database-specific generation behavior.
DEFAULT and PRIMARY KEY Generation
Sequences and UUID functions can appear as defaults for key columns in supported databases. Identity or auto-increment features may use different syntax and ownership semantics.
Generation and primary-key enforcement remain separate concepts. The default creates a candidate value; the PRIMARY KEY guarantees unique, non-null row identity.
DEFAULT and Soft Deletes
A new row can default deleted_at to NULL because NULL truthfully means not deleted. A Boolean is_deleted can default false, but keeping both fields risks contradiction unless a CHECK aligns them.
Prefer the smallest model that preserves required history and querying behavior. DEFAULT establishes the initial state; delete or restore logic changes it later.
Version Columns and Counters
An optimistic-lock version can default to zero or one when a row is created. The default does not increment it after updates. Application SQL, an ORM, or a trigger must own that behavior.
Likewise, a retry count default of zero does not count retries automatically. DEFAULT initializes; it does not maintain derived state.
DEFAULT Is Not a Computed Column
A default is evaluated for omitted input during insertion and then stored. A computed or generated column derives its value from other data according to DBMS rules. A trigger can react to events, while application logic can use external context.
Choose the mechanism that matches the lifecycle. Do not expect DEFAULT to recalculate totals, ages, balances, or timestamps after related values change.
Defaults Do Not Backfill Existing Rows
Changing or adding a default normally controls future inserts. It does not automatically mean that existing NULLs or historical values should be rewritten.
Some ADD COLUMN operations combine schema change and population behavior, and modern engines may optimize constant defaults through metadata. Exact results and storage behavior vary. Treat historical backfill as an explicit data migration.
Historical Data May Need Different Values
A current default describes today's omitted-value rule. Old rows may have been created under different policies or require values derived from historical evidence. Applying today's value to all history can fabricate facts.
Backfill from trustworthy source data, use NULL when history is unknown and allowed, or record a migration-specific state whose meaning is documented.
Adding a Default to an Existing Column
DBMS syntax differs. PostgreSQL commonly uses ALTER COLUMN ... SET DEFAULT; SQL Server often creates a named DEFAULT constraint; Oracle, MySQL, and SQLite have their own capabilities and limitations.
Use versioned migrations and inspect the resulting schema metadata. Naming standalone default constraints where applicable makes future changes easier to manage.
Adding a New NOT NULL Column
Adding a required column to a populated table needs a plan for old rows and active writers. A default can help old application versions insert during rollout, but it must not invent an incorrect business fact.
A safe approach may add the column as nullable, deploy writers, backfill history, add the default for future rows, validate, and finally enforce NOT NULL. The exact order depends on the engine and compatibility needs.
Large-Table Migration Cost
A simple ALTER TABLE can rewrite data, scan rows, acquire locks, generate transaction logs, increase storage, and delay replication. Modern metadata-only optimizations are conditional on database, version, expression, and table state.
Test with production-like volume, monitor locks and replicas, and prepare rollback or fix-forward steps. Syntax simplicity does not guarantee operational simplicity.
Changing a Default
Changing a default silently changes future rows while old rows retain earlier values. That can be correct, but reports must understand that records created in different periods followed different initialization policies.
Version the change, communicate it to writers and consumers, and test omission after deployment. Do not combine a default change with an unreviewed historical rewrite.
Removing a Default
Dropping a default means omitted inserts may produce NULL or fail under NOT NULL. Remove it only after every writer supplies an appropriate value or accepts the new behavior.
In rolling deployments, older versions may still depend on database initialization. Treat removal as a contract change with dependency analysis.
Application Defaults and Database Defaults
An application default provides immediate object or UI behavior, while a database default protects all valid insertion paths. Using both can be useful when they express exactly the same meaning.
Conflicting defaults create records that vary by writer. Prefer one authoritative semantic definition, document duplication where necessary, and test consistency across services.
ORM Behavior Can Defeat a Database Default
Some ORMs include every mapped column and send NULL, which prevents an omission-based database default from running. Others omit unset generated columns. Configuration, insert generation, and entity state all matter.
Inspect the actual SQL and test omitted, NULL, and explicit values. Do not infer database behavior from an in-memory object alone.
API Omission Versus JSON NULL
An absent JSON property and a property explicitly set to null can represent different client intent. The API mapping should preserve that difference when the database schema does.
Define request semantics in the API contract. Silently converting null to omission can hide mistakes, while converting omission to null can bypass a valid default.
Imports, ETL, and Staging
Missing source data does not automatically equal the destination default. An absent currency, country, tenant, or event time may require rejection, mapping, or NULL rather than a guess.
Use staging tables to retain raw meaning, validate and enrich records, then load constrained core tables. Report how often defaults are used so source-quality problems do not remain invisible.
Microservices and Ownership
A database default belongs to the service that owns the schema. Other services should not depend on undocumented insertion behavior across private database boundaries.
Expose initial-state semantics through API and event contracts where consumers need them. Keep service-local defaults aligned with those contracts and version changes deliberately.
Data Warehouse Defaults
Warehouses sometimes use governed unknown dimension members, load timestamps, batch IDs, or source-system codes as defaults. These values must distinguish unknown, not applicable, late arriving, and processing failure where reporting depends on the difference.
A convenient zero key or current timestamp can destroy lineage if it does not represent the source fact. Preserve event time separately from ingestion and processing time.
Real-World Order Example
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
currency_code CHAR(3) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
version_no INTEGER NOT NULL DEFAULT 1,
CONSTRAINT chk_order_status
CHECK (status IN ('PENDING', 'PAID', 'CANCELLED')),
CONSTRAINT fk_order_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
PENDING, creation time, and version one have clear initialization meanings. Currency is deliberately required because its correct value depends on transaction context.
Ask What Omission Means
Before defining a default, describe the omitted case without SQL. If the answer is "every new record begins in this one state," a default is promising. If the answer is "we do not know," NULL may be more accurate. If omission is an error, require the caller to provide a value.
This decision prevents convenience from replacing semantics. A good default is not merely common; it is correct whenever the column is legitimately omitted.
Legitimate Default, NULL, or Rejection
These three designs express different contracts. A legitimate default supplies a known initial fact. NULL preserves unknown or not-applicable meaning. NOT NULL without a default rejects incomplete input.
A middle name is often nullable because absence may be meaningful and no universal value exists. A required customer ID should reject omission. A draft document status may have one natural default. Choose the contract column by column.
Explicit Values Override the Default
Unless another constraint rejects it, an explicitly supplied value wins over the default. A product whose stock defaults to zero can be inserted with an opening quantity of 25. An order can be inserted with a valid imported status instead of PENDING when the workflow permits it.
This is why DEFAULT is not mandatory business logic. Use CHECK or a reference table to define allowed values and authorization or workflow rules to control who may choose them.
Time Zones and Timestamp Semantics
CURRENT_TIMESTAMP is useful only when its clock and time-zone behavior are understood. The database session, server configuration, column type, and driver conversion can affect representation. Prefer an agreed instant representation, often UTC, while retaining source-zone information when business interpretation needs it.
Test daylight-saving transitions and cross-region writes. A default timestamp may reliably record database receipt time but cannot reconstruct the user's original local event time unless that context is supplied separately.
CURRENT_DATE Versus CURRENT_TIMESTAMP
A date default records a calendar date without time-of-day precision. A timestamp records a more precise moment, though its time-zone semantics depend on type and DBMS. Choose based on the business fact, not display preference.
For reporting cutoffs, accounting dates, or service days, the correct date may come from a business calendar rather than the database clock. Require that context when a universal current date is not correct.
Sequence and Identity Defaults
PostgreSQL sequences commonly appear through default expressions, while other products use identity or auto-increment syntax. The mechanism may allocate numbers with gaps because of rollbacks, caching, retries, and failover.
Generated primary keys provide identity, not legal document numbering or chronology. Preserve sequence ownership and state through backup, restore, bulk import, and replication, and monitor capacity before the numeric type is exhausted.
UUID and Public-ID Defaults
A database-supported UUID function can generate decentralized identifiers when the column is omitted. Verify function availability, UUID version, randomness or ordering characteristics, index impact, and whether the application also generates IDs.
A generated public ID can reduce simple enumeration but does not authorize access. UNIQUE and NOT NULL constraints still protect the stored value, while every API request must enforce ownership and permissions.
Password and Secret Defaults Are Unsafe
Never provide a shared plaintext password, API key, recovery token, or encryption secret as a static default. Repeated secrets create immediate compromise and can make incomplete account creation look valid.
Security credentials should be generated with an appropriate cryptographic mechanism, stored according to their security model, rotated, and delivered through controlled workflows. If secure context is missing, reject the operation rather than filling a convenient value.
Real-World Product Example
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
stock_quantity INTEGER NOT NULL DEFAULT 0,
discount_rate DECIMAL(5,4) NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT chk_product_status
CHECK (status IN ('DRAFT', 'ACTIVE', 'RETIRED')),
CONSTRAINT chk_product_stock CHECK (stock_quantity >= 0),
CONSTRAINT chk_product_discount
CHECK (discount_rate BETWEEN 0 AND 1)
);
These defaults create a non-sellable draft with known zero inventory and discount. Activation remains a deliberate workflow action rather than an unsafe default.
Safe Migration Sequence
- Define the omission meaning and expected historical treatment.
- Inventory applications, ORMs, APIs, jobs, imports, and ETL writers.
- Verify compatibility with types and all existing constraints.
- Measure table size, locks, rewrite risk, logs, and replication impact.
- Deploy compatible readers and writers in the required order.
- Add or change the default through a versioned migration.
- Backfill historical rows separately when evidence supports it.
- Add NOT NULL only after data and writers satisfy the requirement.
- Test omission, NULL, explicit values, and rollback behavior.
- Monitor usage and violations after release.
Database Portability
PostgreSQL, SQL Server, Oracle, MySQL, and SQLite differ in ALTER syntax, named default objects, allowed expressions, identity behavior, timestamp semantics, and whether adding a column rewrites stored rows. Version differences matter too.
Test migrations and insertion behavior on the exact production engine. An in-memory substitute can confirm application flow without proving production DDL, locking, metadata, or expression behavior.
Defaults Are Part of the Data Contract
Consumers may infer that omitted status means PENDING or that omitted creation time uses database receipt time. Changing those assumptions can alter APIs, events, analytics, and operational reports without changing their schemas.
Document defaults alongside column meaning, publish relevant behavior in service contracts, and review changes with data producers and consumers. Silent initialization is still observable business behavior.
Monitor Default Usage
Track how often important defaults are applied and which writers omit the fields. Unexpected growth can reveal a client regression, missing mapping, source-quality issue, or rollout problem. For ETL, distinguish rows intentionally defaulted from rows repaired after rejection.
Monitor migration duration, locks, transaction logs, replication lag, constraint failures, and NULL rates. A default that hides a deteriorating source is not improving data quality.
Defaults and Transaction Behavior
A default is evaluated as part of the INSERT statement under the database transaction. If another constraint rejects the resulting row, the row is not partially stored merely because a default was calculated. Transaction rollback also removes inserted rows and their assigned default values, although sequence allocation may still leave gaps.
Expression defaults can be evaluated once per statement or once per row depending on the function and DBMS. When exact consistency matters across a batch, test the target engine and consider supplying one explicit transaction timestamp rather than assuming every generated value is identical.
Inspect Defaults Through Schema Metadata
After deployment, query database metadata or schema tooling to confirm the expected expression, data type, constraint name, and NULLability. Text representations may include casts, parentheses, generated names, or vendor-specific functions.
Automated schema-drift checks can detect missing, changed, or inconsistent defaults across environments. Verification matters because a migration file succeeding does not prove that every database reached the intended final state.
Testing Defaults
Test insertion with the column omitted, an explicit valid value, explicit NULL, and the DEFAULT keyword where supported. Verify interactions with NOT NULL, CHECK, UNIQUE, and FOREIGN KEY constraints.
After changing a default, test both new rows and historical rows. Inspect generated ORM SQL and every major import path. Use the production DBMS because syntax and expression semantics differ.
Common DEFAULT Mistakes
- Assuming DEFAULT makes a column mandatory.
- Expecting DEFAULT to replace an explicit NULL.
- Expecting it to backfill history or rerun on UPDATE.
- Using fake dates, zeros, text, or parent IDs to hide missing facts.
- Assuming one country, currency, tenant, or time zone.
- Creating defaults that conflict with CHECK, FOREIGN KEY, or UNIQUE.
- Defining different defaults in applications and the database.
- Ignoring ORM insert behavior and API null semantics.
- Adding defaults to large tables without migration analysis.
- Defaulting security-sensitive ownership or permissions.
Production Design Checklist
- State what omission means in plain business language.
- Confirm there is exactly one universally correct omitted value.
- Distinguish omission, NULL, unknown, and not applicable.
- Verify the default fits the type and all other constraints.
- Reject context-dependent tenant, currency, country, or security guesses.
- Define timestamp source, time zone, and event semantics.
- Inspect application, ORM, API, batch, import, and ETL behavior.
- Separate future initialization from historical backfill.
- Assess table rewrite, locking, logging, and replication cost.
- Version, test, document, and monitor every default change.
Interview Questions and Answers
What is a DEFAULT constraint? It defines the value used when an INSERT omits a column.
Does DEFAULT prevent other values? No. Use CHECK, FOREIGN KEY, or other constraints to restrict values.
Does it imply NOT NULL? No. NOT NULL must be declared separately.
Omitted column versus explicit NULL? Omission invokes the default; explicit NULL normally stores NULL or fails under NOT NULL.
Does a new default update old rows? Not inherently. Historical backfill is a separate migration decision.
Does it rerun on UPDATE? Normally no; update automation needs another mechanism.
Can a default be an expression? Often yes, but allowed expressions and semantics vary by DBMS.
Why can ORM behavior matter? An ORM that sends NULL instead of omitting the column prevents the database default from applying.
Key Takeaway
A DEFAULT is a database-owned answer to omitted insert input. It is reliable only when omission has one legitimate meaning that is valid across writers and consistent with every other constraint.
RELIABLE DEFAULT = CLEAR OMISSION MEANING
+ CORRECT DATA TYPE
+ EXPLICIT NULL POLICY
+ VALID CONSTRAINT INTERACTION
+ CONSISTENT APPLICATION BEHAVIOR
+ SEPARATE HISTORICAL BACKFILL
+ SAFE VERSIONED MIGRATION
+ TESTED PRODUCTION SEMANTICS
Use defaults to express natural initialization, not to hide missing required information. Require context when the correct value depends on customer, tenant, security, geography, currency, or history.