SQL — Default

What is DEFAULT?

A DEFAULT value is automatically inserted when a row is added without specifying a value for that column.

Basic Syntax

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  status VARCHAR(20) DEFAULT 'active'
);

Examples

INSERT INTO users (id, name) VALUES (1, 'Alice');
-- status will be 'active'

SELECT * FROM users;
-- | id | name  | status |
-- | 1  | Alice | active |

Common Default Values

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) DEFAULT 0.00,
  stock INT DEFAULT 0,
  is_available BOOLEAN DEFAULT TRUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DEFAULT with Expressions

Some databases allow expressions as defaults:

-- MySQL: current timestamp
created_at DATETIME DEFAULT CURRENT_TIMESTAMP

-- PostgreSQL: generated values
uuid_col UUID DEFAULT gen_random_uuid()
full_name VARCHAR(200) DEFAULT (first_name || ' ' || last_name)

Adding DEFAULT to Existing Table

ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';

Removing a DEFAULT

ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;

DEFAULT with NULL

By default, columns allow NULL if no DEFAULT is specified. To explicitly set NULL as default:

CREATE TABLE tasks (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  completed_at DATETIME DEFAULT NULL
);

DEFAULT in INSERT

Override the default by providing a value:

INSERT INTO users (id, name, status) VALUES (2, 'Bob', 'inactive');
-- status is 'inactive', not the default 'active'

DEFAULT with NOT NULL

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  quantity INT NOT NULL DEFAULT 1,
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
);

Practical Example

CREATE TABLE blog_posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  author_id INT NOT NULL,
  status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
  view_count INT DEFAULT 0,
  featured BOOLEAN DEFAULT FALSE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Best Practices

• Always set DEFAULT for columns that have a common value
• Use DEFAULT CURRENT_TIMESTAMP for audit columns
• Combine DEFAULT with NOT NULL to prevent empty values
• Document non-obvious defaults in comments

Practice

1. Create a settings table with sensible defaults for each column
2. Add a DEFAULT to an existing column using ALTER TABLE
3. Create a table where created_at auto-fills with the current timestamp