SQL DEFAULT Constraint

The DEFAULT constraint provides a default value for a column.

The default value will be set to all new records if no other value is specified.

SQL DEFAULT on CREATE TABLE

The following SQL statement will set a DEFAULT value for the "country" column during the creation of the "Employee" table.

• For MySQL, MariaDB, Oracle, SQL Server

  CREATE TABLE employee (
      id INT NOT NULL, 
      first_name VARCHAR(40),
      last_name VARCHAR(40),
      birth_date DATE,
      hire_date DATE,
      country VARCHAR(150) DEFAULT 'United States'
  );
  

SQL DEFAULT on ALTER TABLE

The following SQL statement will create a DEFAULT constraint on the "country" column on an already existed "Employee" table.

• For MySQL, MariaDB :

  ALTER TABLE employee
    ALTER country SET DEFAULT 'United States';
  

• For Oracle :

  ALTER TABLE employee
    MODIFY country DEFAULT 'United States';
  

• For SQL Server :

  ALTER TABLE employee
    ADD CONSTRAINT country_df DEFAULT 'United States';
  

DROP a DEFAULT Constraint

You can drop a DEFAULT constraint using the ALTER TABLE statement.

The following SQL statement will drop the DEFAULT constraint from the "Employee" table.

• For MySQL, MariaDB :

  ALTER TABLE employee
    ALTER country DROP DEFAULT;
  

• For Oracle, SQL Server :

  ALTER TABLE employee
    ALTER COLUMN country DROP DEFAULT;