Description

The SQL keyword DEFAULT is a constraint used to define a default value for a specific column.

  • It can be used while creating or altering a table structure.
  • The default value is added to all the new records if the value is not provided during the INSERT operation.

CREATE TABLE Using DEFAULT

The keyword CREATE TABLE can include the keyword DEFAULT to set a default constraint on a table column.

The below SQL creates a new table with a default constraint on column city, which uses 'London' as a default value.

CREATE TABLE users (
    id INT,
    name VARCHAR(255),
    city VARCHAR(255) DEFAULT 'London'
);

Similarly, the below SQL sets a default constraint on column order_date, which uses the system date as a default value using the function GETDATE().

CREATE TABLE orders (
    id INT,
    order_date DATE DEFAULT GETDATE()
);

ALTER TABLE Using DEFAULT

The keyword ALTER TABLE can include the keyword DEFAULT to either add or delete a DEFAULT constraint on a column.

The syntax is different across the database systems as mentioned below.

Add DEFAULT Constraint

The below SQLs add a DEFAULT constraint on a column.

-- Syntax for MySQL Database
ALTER TABLE users
ALTER city SET DEFAULT 'London';
-- Syntax for SQL Server
ALTER TABLE users
ADD CONSTRAINT default_city
DEFAULT 'London' FOR city;
-- Syntax for MS Access
ALTER TABLE users
ALTER COLUMN city SET DEFAULT 'London';
-- Syntax for Oracle
ALTER TABLE users
MODIFY city DEFAULT 'London';

Delete DEFAULT Constraint

The below SQLs delete a DEFAULT constraint from a column.

-- Syntax for MySQL Database
ALTER TABLE users
ALTER city DROP DEFAULT;
-- Syntax for SQL Server, Oracle, and MS Access
ALTER TABLE users
ALTER COLUMN city DROP DEFAULT;

Related Links