MySQL DEFAULT Constraint

  1. Overview
  2. MYSQL
  3. MySQL Database
  4. MySQL DEFAULT Constraint

The DEFAULT constraint is used to assign a default value to a column when a new record is inserted, provided no value is explicitly specified for that column. It ensures that columns have a predefined value when data is inserted, making data entry more efficient and helping enforce consistency.

Key Characteristics of the DEFAULT Constraint:

  • Automatic Value Assignment: If no value is provided for the column during an INSERT operation, the default value will be used.
  • Use of System Functions: You can use system functions like CURRENT_DATE() or NOW() to set default values based on the current date or time.
  • Optional Value: If a value is provided during an insert, it will override the default value.

Using the DEFAULT Constraint

1. DEFAULT Constraint on Table Creation

You can define the DEFAULT constraint when creating a table. This will automatically assign the default value to the column if no value is provided.

Example: DEFAULT Constraint on a Column
CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT,
    City VARCHAR(255) DEFAULT 'Sandnes'
);

In this example:

  • The City column has a default value of 'Sandnes'. If no value is provided for the City column during an insert, the value 'Sandnes' will be used.
Example: Using System Functions with DEFAULT
CREATE TABLE Orders (
    ID INT NOT NULL,
    OrderNumber INT NOT NULL,
    OrderDate DATE DEFAULT CURRENT_DATE()
);

In this example:

  • The OrderDate column has a default value set to the current date using the CURRENT_DATE() function. This ensures that if no OrderDate is provided, the current date will be inserted automatically.

2. DEFAULT Constraint on Existing Table

You can add a DEFAULT constraint to an existing column using the ALTER TABLE statement.

Example: Add a DEFAULT Constraint on a Column
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';

In this example:

  • The City column is given a default value of 'Sandnes' after the table has already been created. If no value is provided for City, 'Sandnes' will be used by default.

3. Dropping a DEFAULT Constraint

If you no longer want a column to have a default value, you can remove the DEFAULT constraint using the ALTER TABLE statement.

Example: Drop a DEFAULT Constraint
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

In this example:

  • The DEFAULT constraint on the City column is removed, and the column will no longer automatically populate with a default value.

Important Notes:

  • Only One Default: A column can only have one DEFAULT value. If you attempt to define more than one default value for the same column, an error will occur.
  • NULL Values: If you define a column with a DEFAULT value, and the column is nullable, the default value will be used only when the column is not provided a value during INSERT. If the column is explicitly set to NULL, the default value will not be applied.
  • Automatic Value: The DEFAULT value is only applied if no value is provided. If a value is supplied during an INSERT statement, that value will be used instead of the default.

Summary of DEFAULT Constraint Usage:

  • On Table Creation: The DEFAULT constraint is defined when creating the table to assign default values to columns.
  • On Table Modification: You can add or modify a DEFAULT constraint using the ALTER TABLE statement after the table is created.
  • Dropping a DEFAULT Constraint: If you want to remove a DEFAULT constraint from a column, you can do so using the ALTER TABLE statement.

The DEFAULT constraint is particularly useful for ensuring that columns always have a value, even if the user does not explicitly provide one, and can be especially helpful for dates, statuses, or other commonly used values.

Was this article helpful?