Execute Program
Log InStart Now

SQL: Defaults

Welcome to the Defaults lesson!

This lesson is shown as static text below. However, it's designed to be used interactively. Click the button below to start!

  • Normally, we have to specify all of the columns when inserting a row. But that quickly becomes tedious because many real-world tables have dozens of columns. We can eliminate some of that tedium by defining default values for columns.

  • For example, if our users have a login_count, then 0 is a good default value. When a user is created, they've never logged in. Defining that default value during table creation saves us from specifying a login_count when INSERTing a user.

  • >
    exec(`
      CREATE TABLE users (
        name TEXT,
        login_count INTEGER NOT NULL DEFAULT 0
      );
    `);
    Result:
    []Pass Icon

  • When we insert a user and query them back, we'll see that their login_count has the default value. (This query returns a row with both user columns: name and login count.)

  • Note: this code example reuses elements (variables, etc.) defined in earlier examples.
    >
    exec(`INSERT INTO users (name) VALUES ('Amir')`);
    exec(`SELECT * FROM users`);
    Result:
    [{name: 'Amir', login_count: 0}]Pass Icon

  • Our column is INTEGER, NOT NULL, and DEFAULT 0. The type (INTEGER in this case) always has to come directly after the column name. However, we can switch the order of the NOT NULL and the DEFAULT 0 if we want to. Here, we put them in the opposite order, with the DEFAULT before the NOT NULL:

  • >
    exec(`
      CREATE TABLE users (
        name TEXT,
        login_count INTEGER DEFAULT 0 NOT NULL
      );
    `);
    exec(`INSERT INTO users (name) VALUES ('Amir')`);
    exec(`SELECT * FROM users`);
    Result:
    [{name: 'Amir', login_count: 0}]Pass Icon

  • If we specify a value when inserting, it will override the default.

  • >
    exec(`
      CREATE TABLE users (
        name TEXT,
        login_count INTEGER NOT NULL DEFAULT 0
      );
    `);
    exec(`INSERT INTO users (name, login_count) VALUES ('Amir', 1)`);
    exec(`SELECT * FROM users`);
    Result:
    [{name: 'Amir', login_count: 1}]Pass Icon

  • Here's a code problem:

    Create a "cats" table. It should have a TEXT NOT NULL column "name" and an INTEGER NOT NULL column "vaccinated" that defaults to 0 (meaning "false").

    exec(`
    CREATE TABLE cats (
    name TEXT NOT NULL,
    vaccinated INTEGER NOT NULL DEFAULT 0
    )
    `);
    exec(`INSERT INTO cats (name) VALUES ('Ms. Fluff')`);
    exec(`SELECT * FROM cats`);
    Goal:
    [{name: 'Ms. Fluff', vaccinated: 0}]
    Yours:
    [{name: 'Ms. Fluff', vaccinated: 0}]Pass Icon

  • Here's a code problem:

    Imagine that we're writing a system where users can write comments to each other. Unfortunately, comments can get ugly. Define a comments table:

    • The comment_text column has type TEXT.
    • The flag column has type INTEGER, but is used as a boolean: it's set to 1 if a moderator "flags" the comment as inappropriate.
    • The flag column should default to 0: we assume that a comment is nice by default.

    When we SELECT comments to show on the site, we'll only select the ones where flag is 0. That way, comments that were flagged by a moderator are hidden.

    exec(`
    CREATE TABLE comments (comment_text TEXT, flag INTEGER DEFAULT 0)
    `);
    exec(`
    INSERT INTO comments (comment_text)
    VALUES ('A cat ate that entire watermelon?!')
    `);
    exec(`
    INSERT INTO comments (comment_text, flag)
    VALUES ('UMM THE VIDEO WAS EDITED', 1)
    `);
    exec(`SELECT * FROM comments`);
    Goal:
    [{comment_text: 'A cat ate that entire watermelon?!', flag: 0}, {comment_text: 'UMM THE VIDEO WAS EDITED', flag: 1}]
    Yours:
    [{comment_text: 'A cat ate that entire watermelon?!', flag: 0}, {comment_text: 'UMM THE VIDEO WAS EDITED', flag: 1}]Pass Icon