113 SQL DEFAULT

The current page explains the SQL DEFAULT constraint. Here’s a summary:

The DEFAULT constraint is used to set a default value for a column in an SQL table.
When inserting new records, if no value is specified for a column with a DEFAULT constraint, the default value will be used.

Usage during table creation:

Example:

CREATE TABLE Persons (
  ID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Age int,
  City varchar(255) DEFAULT 'Sandnes'
);

You can also use functions like GETDATE() to set system values as defaults.

Usage after table creation:
- The syntax to add a DEFAULT to an existing column varies by database:
  - MySQL:
```
ALTER TABLE Persons ALTER City SET DEFAULT 'Sandnes';
```
  - SQL Server:
```
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
```
  - Other databases have similar statements.
Dropping a DEFAULT constraint:
- MySQL and other databases provide ALTER TABLE statements to remove the DEFAULT from a column.
The primary purpose of the DEFAULT constraint is to set a default value for a column when no value is specified in an insert operation.[1]

This page includes example code for each main SQL flavor and explains both how to set and how to drop DEFAULT constraints.

SQL DEFAULT Constraint

The DEFAULT constraint is used to set 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

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (  
    ID int NOT NULL,  
    LastName varchar(255) NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    City varchar(255) DEFAULT 'Sandnes'  
);

The DEFAULT constraint can also be used to insert system values, by using functions like [GETDATE()](https://www.w3schools.com/sql/func_sqlserver_getdate.asp):

CREATE TABLE Orders (  
    ID int NOT NULL,  
    OrderNumber int NOT NULL,  
    OrderDate date DEFAULT GETDATE()  
);

SQL DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, 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';

DROP a DEFAULT Constraint

To drop a 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;