MYSQL Tutorial

MySQL DEFAULT Constraint

The DEFAULT constraint specifies a default value for a column when no value is provided. It helps ensure meaningful data and can simplify INSERT statements.

Syntax

column_name data_type DEFAULT default_value
  

Defining DEFAULT in CREATE TABLE

CREATE TABLE products (
  product_id   INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  price        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  in_stock     BOOLEAN         NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMP       DEFAULT CURRENT_TIMESTAMP
);
  

Adding DEFAULT to an Existing Column

ALTER TABLE users
MODIFY COLUMN signup_date DATE NOT NULL DEFAULT '2025-01-01';
  

Removing a DEFAULT Constraint

ALTER TABLE products
ALTER COLUMN price DROP DEFAULT;
  

Common Use Cases

• Numeric fields with logical defaults (e.g., quantity DEFAULT 0).
• Boolean flags (e.g., is_active DEFAULT TRUE).
• Timestamps with CURRENT_TIMESTAMP for automatic creation times.
• Enum or status fields with a common default value.