DEFAULT

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
)

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'
)

CREATE TABLE Mechanics (

    specialty VARCHAR(255) DEFAULT 'General'

)

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()
)

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'

ALTER TABLE Mechanics

ALTER specialty SET DEFAULT 'General'

SQL Server:

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

ALTER TABLE Mechanics

ADD CONSTRAINT df_specialty

DEFAULT 'General' FOR specialty

MS Access:

ALTER TABLE Mechanics
ALTER COLUMN specialty SET DEFAULT 'General'

ALTER TABLE Mechanics

ALTER COLUMN specialty SET DEFAULT 'General'

Oracle:

ALTER TABLE Mechanics
MODIFY specialty DEFAULT 'General'

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

MySQL:

ALTER TABLE Mechanics
ALTER specialty DROP DEFAULT

ALTER TABLE Mechanics

ALTER specialty DROP DEFAULT

SQL Server / Oracle / MS Access:

ALTER TABLE Mechanics
ALTER COLUMN specialty DROP DEFAULT

ALTER TABLE Mechanics

ALTER COLUMN specialty DROP DEFAULT

Note: Dropping a DEFAULT constraint is not supported in SQLite. To change a default value in SQLite, you must recreate the table with the new definition.

SQL DEFAULT Labs