SQL from zero · Chapter 25 of 37

Changing the shape

By the end of this chapter you can change a table that already has rows: add and rename columns, change a column's type, add a rule, and add a column the database computes for you. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Building the schema
  • 5 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera has notes on Circles. The profile page wants a member count. "Private" should become a visibility setting, public or private. And someone made a circle called ' Startup Founders ', with spaces. Then Vikram wants a slug for every circle's URL.

The circles table has rows now: five circles and thirteen memberships. With what you know, the only way to change its shape is to drop it and create it again, which throws away every row. It is not even allowed: cannot drop table circles because other objects depend on it, because circle_members points at it. Copying the rows out and back in by hand would give every circle a new id and break those memberships.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    member-count.sql: a member_count column, filled in

    Add a column member_count to circles: integer, NOT NULL, with DEFAULT 0. Then fill it in: each circle's member_count is its number of rows in circle_members (the owner counts).

    Afterwards every circle has its real count, and the default for new circles is still 0.

  2. Task 2

    rename.sql: description becomes about, member_limit becomes max_members

    Rename two columns of circles: description becomes about, and member_limit becomes max_members.

    Afterwards the columns keep their data, max_members still only allows 2 to 500 and still defaults to 50, and no other column has changed.

  3. Task 3

    visibility.sql: is_private becomes a visibility text column

    Replace circles.is_private (boolean) with a column visibility (text): private where is_private was true, public where it was false.

    visibility is NOT NULL and defaults to public, so a new circle inserted without it is public. There is no is_private column left afterwards.

  4. Task 4

    trim-names.sql: no names with outer spaces, the NOT VALID way

    In three steps: add a CHECK constraint named circles_name_trimmed, saying the name equals btrim(name), as NOT VALID; fix the names that break it by setting them to btrim(name); then VALIDATE CONSTRAINT circles_name_trimmed.

    Afterwards no circle name starts or ends with a space, a new circle named with a leading space is refused, and only the untidy names (not the descriptions or anything else) have changed.

  5. Task 5 · interview question

    Interview: slug.sql, a generated slug column

    Add a column slug (text) to circles that the database computes from the name and stores: a generated column, not one you fill in. The slug is exactly lower(replace(name, ' ', '-')): the name in lower case with each space replaced by a hyphen, so Chai Lovers gives chai-lovers.

    Use exactly that expression, with no trimming: the untidy names keep their hyphens. Afterwards, renaming a circle changes its slug, and a new circle gets its slug by itself.

What you will learn

  • ALTER TABLE ... ADD COLUMN and RENAME COLUMN: a new column, or a new name, with every row kept.
  • ALTER COLUMN ... TYPE ... USING: convert every row to a new type, and SET DEFAULT for the default that comes along.
  • NOT VALID and VALIDATE CONSTRAINT: add a rule to a table whose old rows break it, then fix them.
  • Generated columns: a column the database computes from the others.

More in Building the schema

Build "Changing the shape" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.