SQL Reference ยท Constraints and Data Types
SQL DEFAULT constraint
Reference for the SQL DEFAULT constraint: fill in a value when none is given, constants and expressions, DEFAULT VALUES, NULL behavior and dialect differences.
A DEFAULT clause gives a column a value to use when an INSERT does not mention that column. It saves repetition in application code and makes sure every row gets a sensible starting value, such as a status of 'pending', a quantity of 0 or the current timestamp.
A default applies only when the column is left out of the insert, or when you ask for it with the keyword DEFAULT where the dialect allows it. Writing NULL explicitly does not trigger it: that stores NULL.
CREATE TABLE tasks ( id INTEGER PRIMARY KEY, status TEXT NOT NULL DEFAULT 'open', created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
column_name data_type DEFAULT default_value [constraints] INSERT INTO table_name DEFAULT VALUES; -- SQLite, PostgreSQL, SQL Server INSERT INTO table_name (column_name) VALUES (DEFAULT); -- PostgreSQL, MySQL, SQL Server, Oracle
| Part | Description |
|---|---|
default_value |
A constant (number, string, NULL), a keyword such as CURRENT_TIMESTAMP, or in many systems an expression or function call. SQLite requires non-constant expressions to be wrapped in parentheses, for example DEFAULT (date('now')). |
DEFAULT VALUES |
Inserts one row made only of defaults and generated keys. |
DEFAULT keyword in VALUES |
Asks for the default of one column inside a normal insert list. |
Notes: without a DEFAULT clause, the default of a column is NULL. The default must fit the column's type, and a default for a NOT NULL column must not be NULL.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | No DEFAULT keyword inside VALUES. Expression defaults need parentheses. Cannot change a default with ALTER TABLE. |
| PostgreSQL | ALTER TABLE t ALTER COLUMN c SET DEFAULT expr and DROP DEFAULT. Defaults may call functions such as now() or gen_random_uuid(). |
| MySQL | DEFAULT (expression) since 8.0.13; ON UPDATE CURRENT_TIMESTAMP keeps a timestamp in step with changes. |
| SQL Server | A default is a named constraint object: CONSTRAINT df_status DEFAULT 'open'. Use GETDATE() for the current time. |
| Oracle | DEFAULT ON NULL (12c) also replaces explicit NULLs. Use SYSDATE or CURRENT_TIMESTAMP. |
-- PostgreSQL ALTER TABLE tasks ALTER COLUMN status SET DEFAULT 'open'; -- SQL Server ALTER TABLE tasks ADD CONSTRAINT df_tasks_status DEFAULT 'open' FOR status; -- Oracle 12c and later CREATE TABLE tasks (id NUMBER, status VARCHAR2(10) DEFAULT ON NULL 'open');
Usage notes
- Omitted means default;
NULLmeansNULL. Application code that passesNULLfor "no value" silently bypasses the default. - Evaluated at insert time.
CURRENT_TIMESTAMPis the moment of the insert, not of the table creation. - Pair with
NOT NULL. ANOT NULL DEFAULTcolumn is both required and convenient. - Adding a column with a default to an existing table fills old rows with the default (instantly in modern PostgreSQL, MySQL 8 and SQLite for constant defaults).
- Changing a default affects future inserts only, not existing rows.
- Time zones: in SQLite
CURRENT_TIMESTAMPis UTC text like2025-03-01 12:30:00. - Defaults are not validation. Add a
CHECKto restrict allowed values.
Related
NOT NULL,CHECK,INSERT INTO,CREATE TABLE,CURRENT_TIMESTAMP- Lessons: Constraints, Insert, Create table