Usage and examples of the SQL DEFAULT keyword

SQL Keywords Reference

DEFAULT

DEFAULT constraints provide default values for columns.

If no other value is specified, the default value will be automatically added to all new records.

DEFAULT value in SQL CREATE TABLE

When creating the table 'Persons', the following SQL sets a DEFAULT value for the 'City' column:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(　　　
　　City varchar(255) DEFAULT 'Sandnes'
);

DEFAULT constraints can also be used to insert system values using functions like GETDATE():

CREATE TABLE Orders
(　
　　OrderDate date DEFAULT GETDATE()
);

DEFAULT value in SQL ALTER TABLE

To create a DEFAULT constraint on the 'City' column after the table has been created, please use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

SQL Server:

ALTER TABLE Persons
ADD CONSTRAINT df_City　
DEFAULT 'Sandnes' FOR City;

MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';

Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';

Delete DEFAULT Constraint

To delete the DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

SQL Keywords Reference