Code has been added to clipboard!

Using SQL DEFAULT Value Constraint

Reading time 1 min
Published Aug 9, 2017
Updated Oct 11, 2019

SQL DEFAULT Constraint: Main Tips

  • This constraint is used to write a value in a column which is default.
  • When no different values are defined, by default it will be inserted for all new data records.

DEFAULT Constraint on CREATE TABLE

In the code example below we produce the constraint DEFAULT in the table "Person" on the column "CityID":

MS Access / Oracle / My SQL / SQL Server:

Example
CREATE TABLE Person (
    ID int NOT NULL,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int,
    CityID varchar(255) DEFAULT 'ben'
);

This constraint is useful when you need to write in a system value. You can do this with a function like GETDATE():

Example
CREATE TABLE Order (
    ID int NOT NULL,
    OrdersNumber int NOT NULL,
    OrdersDate date DEFAULT GETDATE()
);
Theory is great, but we recommend digging deeper!

DEFAULT Constraint on ALTER TABLE

In the code example below we produce the constraint DEFAULT after the table is already created on the column "CityID":

MySQL:

Example
ALTER TABLE Person
ALTER CityID SET DEFAULT 'Ben';

SQL Server / MS Access:

Example
ALTER TABLE Person
ALTER COLUMN CityID SET DEFAULT 'Ben';

Oracle:

Example
ALTER TABLE Person
MODIFY CityID DEFAULT 'Ben';

DROP a DEFAULT Constraint

When dropping the constraint:

MySQL:

Example
ALTER TABLE Person
ALTER CityID DROP DEFAULT

SQL Server / Oracle / MS Access:

Example
ALTER TABLE Person
ALTER COLUMN CityID DROP DEFAULT;