DEFAULT Constraint
In this page:
Understanding DEFAULT
DEFAULT supplies a fallback value MySQL inserts automatically whenever a row is created without explicitly specifying that column — useful for status flags, counters, or timestamps that should start at a known value.
Example: Understanding DEFAULT
CREATE TABLE users (status VARCHAR(20) DEFAULT 'pending');Using Functions as Defaults
Using CURRENT_TIMESTAMP as a column's default is the standard way to automatically record when a row was created, without requiring the application code to set that value itself on every insert.
Example: Using Functions as Defaults
CREATE TABLE posts (created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);Adding DEFAULT to Existing Tables
Retrofitting a DEFAULT onto an existing column via ALTER TABLE changes what happens for future inserts only — it does not go back and update any rows that already exist in the table.
Example: Adding DEFAULT to Existing Tables
ALTER TABLE users ALTER status SET DEFAULT 'pending';Dropping DEFAULT Constraints
Dropping a DEFAULT constraint means future inserts that omit the column will either store NULL (if the column allows it) or raise an error (if it's also NOT NULL with no fallback).
Example: Dropping DEFAULT Constraints
ALTER TABLE users ALTER status DROP DEFAULT;Combined with NOT NULL
Combining NOT NULL with DEFAULT is a common, safe pattern: the column can never be empty, and if the application doesn't supply a value, MySQL silently fills in a sensible one instead of erroring out.
Example: Combined with NOT NULL
CREATE TABLE users (status VARCHAR(20) NOT NULL DEFAULT 'pending');Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: