liveBook · Manning

Default value

As explained in Grokking Relational Database Design

A DEFAULT constraint supplies a specified value for a column when an inserted row omits that column.

A DEFAULT constraint supplies a value when an inserted row omits a column. It helps ensure the column receives either a user-provided value or the specified default. section 6.15

Timestamped purchases

A purchase table can assign purchase_time the default CURRENT_TIMESTAMP, so an omitted timestamp records when the row is added. Pairing DEFAULT with NOT NULL prevents missing values, although DEFAULT alone does not stop users from explicitly inserting NULL. section 6.15

Use default values when a column commonly needs:

  • A creation or modification timestamp. section 6.15
  • A numeric value such as quantity or price that may be aggregated. section 6.15
  • A placeholder value for an optional column. section 6.15
 CREATE TABLE purchase (
    purchase_id INT PRIMARY KEY,
    total_price DECIMAL(13,2) NOT NULL,
    purchase_time TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP,
    payment_id INT NOT NULL,
    CONSTRAINT fk_payment_method_purchase
      FOREIGN KEY (payment_id) 
      REFERENCES payment_method(payment_id)
  );

Inspect the purchase-table definition to see DEFAULT CURRENT_TIMESTAMP placed alongside the column type and NOT NULL constraint. section 6.15

How Grokking Relational Database Design covers it

How deep it goes
Explains purpose, use cases, SQL placement, CURRENT_TIMESTAMP, and the limit that DEFAULT does not block NULL.
Examples
Demonstrates purchase_time DEFAULT CURRENT_TIMESTAMP in a purchase table and discusses timestamp, numeric, and placeholder defaults.
What you can do after
Apply it via SQL by adding DEFAULT values to column definitions and combining them with NOT NULL when NULL is unacceptable.
How it connects
Connects DEFAULT constraints with NOT NULL constraints, NULL values, timestamps, UTC, and other data-integrity constraints.
Cover of Grokking Relational Database Design
liveBookmanning.com
Read Grokking Relational Database Design on liveBook Get the book at manning.com

Also explained in

Compare how 20 books explain default value →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Grokking Relational Database Design. The section links point to the parts of the book it draws on.