CREATE TABLE and ALTER TABLE

CREATE TABLE defines a new table — its name, its columns, their types and rules — and ALTER TABLE changes an existing table, for example by adding a column.

Why it matters

Before data can be stored, the database needs to know its shape. Choosing the columns, types and keys well at the start saves painful fixes later, when the table holds millions of rows.

The idea

CREATE TABLE lists each column with its type and any rules (constraints): name TEXT NOT NULL. One column, or a group of columns, is the PRIMARY KEY. DEFAULT gives a value to use when an INSERT doesn't mention the column.

ALTER TABLE changes the table afterwards: ADD COLUMN, RENAME COLUMN, DROP COLUMN. These commands change the structure (the schema), not the rows, and they belong to the part of SQL called DDL.

Designing a paper form before printing it: you decide the boxes (columns), what may be written in each (types), which ones must be filled in (NOT NULL), and which are pre-filled (DEFAULT). ALTER TABLE is printing a new version of the form with an extra box.

A reprinted form doesn't change the forms already filled in. ALTER TABLE does: adding a column adds it to every existing row (with its default or NULL), and on a huge table that can take a long time or lock it.

Syntax

CREATE TABLE table_name (
  id     INTEGER PRIMARY KEY,
  column TYPE NOT NULL,
  other  TYPE DEFAULT value
);
ALTER TABLE table_name ADD COLUMN new_column TYPE;

How it works

  1. CREATE TABLE suppliers makes an empty table with three columns.
  2. supplier_id is the primary key: unique and never NULL.
  3. country has DEFAULT 'UK', so an INSERT that leaves it out gets 'UK'.
  4. ALTER TABLE products ADD COLUMN in_stock … DEFAULT 1 adds the column to all 7 existing rows, each with 1.
  5. Neither command returns rows; a SELECT afterwards shows the effect.

Key points

Common mistakes

Not true: A NOT NULL column with no DEFAULT can always be added to a table that already has rows.
In fact: The existing rows would have no value for the new column, so most databases refuse. Give the column a DEFAULT, or add it without NOT NULL and fill it in first.

In the interview

CREATE TABLE defines a table's columns, data types and constraints — a primary key, NOT NULL, defaults, foreign keys — and ALTER TABLE evolves it later, such as adding a column. I always give tables a primary key, and I choose types deliberately: DECIMAL for money, proper DATE types for dates. I treat schema changes on big tables carefully, because adding or changing columns can rewrite or lock the table.

Part of Unit 6: Building tables and changing data in the free SQL course.