Every other constraint in this module rejects something, a missing value, a duplicate, an invalid expression. DEFAULT is the one exception: instead of rejecting a missing value, it quietly supplies one.
Learning Objectives
- Declare a DEFAULT value, and combine it correctly with NOT NULL.
- Change an existing column's default value across every database in this course.
- Recognize when a DEFAULT referencing a function, not just a literal, is the right call.
Declaring a DEFAULT
An INSERT that omits salary gets 0 automatically, instead of being rejected the way a bare NOT NULL column without a default would be. status works the same way with a text default, 'active'. DEFAULT and NOT NULL combine cleanly, as salary shows here, a column can require a value to never be NULL while still filling in a sensible one automatically when the caller doesn't supply it.
CREATE TABLE employees ( id INT PRIMARY KEY, salary DECIMAL(10,2) NOT NULL DEFAULT 0, status VARCHAR(20) DEFAULT 'active' );
Changing an Existing Column's Default
| Database | Change an Existing Column's Default |
|---|---|
| MySQL / MariaDB | ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active'; |
| PostgreSQL | ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active'; |
| SQL Server | ALTER TABLE employees ADD CONSTRAINT df_status DEFAULT 'active' FOR status; |
| Oracle | ALTER TABLE employees MODIFY status DEFAULT 'active'; |
| SQLite | Not supported directly, recreate the table instead |
SQL Server is the real outlier here: it treats a default as its own named constraint object, not a property you can just SET on the column directly like the other four. Changing it means dropping the old named default constraint first (ALTER TABLE employees DROP CONSTRAINT df_status;) and adding a new one, rather than one simple ALTER COLUMN.
DEFAULT With a Function, Not Just a Literal
DEFAULT isn't limited to fixed literals like 0 or 'active', it can call a function instead, computed fresh at insert time. CURRENT_DATE here means every new row records the actual date it was created, automatically, without the inserting application needing to compute or pass that value itself. The exact function names for "right now" vary by database, covered properly in Module 9's SQL Dates lesson.
-- A forward look at Module 9's SQL Dates lesson CREATE TABLE audit_log ( id INT PRIMARY KEY, created_at DATE DEFAULT CURRENT_DATE );
Try It Yourself
Write a statement giving project_assignments a new column status VARCHAR(20) with a default value of 'active', on MySQL.
ALTER TABLE project_assignments ADD COLUMN status VARCHAR(20) DEFAULT 'active';
Frequently Asked Questions
Does passing NULL explicitly in an INSERT still trigger the DEFAULT value?
No, on every database in this course, DEFAULT only applies when the column is omitted from the INSERT entirely. Explicitly passing NULL stores NULL, the default is bypassed, it only fires for a genuinely missing value, which is exactly why DEFAULT alone doesn't prevent NULL, combining it with NOT NULL does.
Can a DEFAULT value be based on another column in the same row?
Standard DEFAULT expressions generally can't reference other columns of the same row, unlike CHECK, which can. A value computed from other columns usually needs a trigger, a computed/generated column (database-specific), or application logic instead.
Common Beginner Mistakes
Assuming DEFAULT alone prevents a column from ever being NULL
It doesn't, DEFAULT only fills in a value when the column is left out of the INSERT entirely. An INSERT that explicitly passes NULL still stores NULL, bypassing the default completely. NOT NULL DEFAULT ... together is what actually guarantees a non-NULL value either way.
Trying a plain ALTER COLUMN SET DEFAULT on SQL Server
SQL Server doesn't support that form, a default there is its own named constraint object. Changing one means dropping the existing named default constraint and adding a new one, not a single SET DEFAULT statement like MySQL or PostgreSQL.
Best Practices
- Pair DEFAULT with NOT NULL whenever a column should genuinely never be NULL, a default alone doesn't guarantee that.
- Use a function-based default (like CURRENT_DATE) for timestamp-style columns instead of trusting every caller to supply one correctly.
- Remember SQL Server's defaults are named constraint objects, not a simple column property, when writing cross-database migration code.
Interview Questions
Why doesn't DEFAULT alone guarantee a column is never NULL?
DEFAULT only fires when a column is omitted from the INSERT statement entirely. An INSERT that explicitly supplies NULL for that column stores NULL directly, the default value never comes into play. Only combining DEFAULT with NOT NULL actually forces every row to end up with a real, non-NULL value either way.
How does changing an existing column's default value on SQL Server differ from PostgreSQL?
PostgreSQL treats a default as a simple property of the column: ALTER TABLE t ALTER COLUMN c SET DEFAULT 'x'; changes it directly. SQL Server implements a default as its own named constraint object, changing it requires ALTER TABLE t DROP CONSTRAINT df_name; followed by ALTER TABLE t ADD CONSTRAINT df_name DEFAULT 'x' FOR c; two statements instead of one.
Summary
DEFAULT supplies a value automatically when an INSERT omits a column, it doesn't reject anything on its own, that's NOT NULL's job, and the two are commonly combined. A default can be a fixed literal or a function call like CURRENT_DATE, and SQL Server's approach to changing one (a named constraint object) is a genuine, worth-remembering split from the other five databases.
What's Next?
CREATE INDEX is next, technically not a constraint, but closely related: the structure UNIQUE and PRIMARY KEY were quietly relying on this whole module to make their checks fast.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.