DEFAULT - SQL Tutorial

SQL DEFAULT Keyword

The DEFAULT constraint provides a default value for a column. The default value will be added to all new records if no other value is specified.

SQL DEFAULT on CREATE TABLE Syntax

CREATE TABLE table_name (
    column_name datatype DEFAULT default_value
)

SQL DEFAULT on CREATE TABLE Example

MySQL / SQL Server / Oracle / MS Access / SQLite:

This sets a default value of 'General' for the specialty column.

CREATE TABLE Mechanics (
    specialty VARCHAR(255) DEFAULT 'General'
)

Try It Out!

SQL DEFAULT with System Functions Example

SQL Server:

This sets the default value of order_date to the current system date using GETDATE().

CREATE TABLE Orders (
    order_date DATE DEFAULT GETDATE()
)

Note: This example will not work in SQLite including our TechKubo playground. SQLite does not support system functions like GETDATE() as default values.

SQL DEFAULT on ALTER TABLE Examples

MySQL:

ALTER TABLE Mechanics
ALTER specialty SET DEFAULT 'General'

SQL Server:

ALTER TABLE Mechanics
ADD CONSTRAINT df_specialty
DEFAULT 'General' FOR specialty

MS Access:

ALTER TABLE Mechanics
ALTER COLUMN specialty SET DEFAULT 'General'

Oracle:

ALTER TABLE Mechanics
MODIFY specialty DEFAULT 'General'

Note: These commands will not work in SQLite. In SQLite, the default value must be defined when the table is created.

SQL DEFAULT DROP a DEFAULT Constraint Examples