Skip to content

Creating and Altering Tables

Design tables with CREATE TABLE, modify them with ALTER TABLE, and drop safely.

What you'll learn

  • CREATE TABLE syntax with columns and data types
  • Common column options: NOT NULL, DEFAULT, UNIQUE, CHECK
  • ALTER TABLE to add, rename, and drop columns
  • DROP TABLE and the danger it carries
  • Best practices for table and column naming

Concept

CREATE TABLE

CREATE TABLE defines a new table with its columns, data types, and constraints. Each column declaration specifies a name, a type, and optional modifiers like NOT NULL, DEFAULT, UNIQUE, or CHECK. The table name and column names should be descriptive, lowercase, and use underscores (order_items, not OrderItems).

Column Modifiers

  • NOT NULL — the column must always have a value; never NULL
  • DEFAULT — a value used when the column is omitted from INSERT
  • UNIQUE — no two rows can have the same value in this column
  • CHECK (condition) — the value must satisfy a boolean expression
  • REFERENCES table(col) — foreign key constraint

These modifiers are constraints that the database enforces on every insert and update. They are your first line of defense against bad data.

ALTER TABLE

ALTER TABLE modifies an existing table. You can add columns (ADD COLUMN), drop columns (DROP COLUMN), rename columns (RENAME COLUMN), change data types (ALTER COLUMN ... TYPE), and add or remove constraints. In production, altering a large table can be expensive and may briefly lock it — plan migrations carefully.

DROP TABLE

DROP TABLE permanently removes the table and all its data. There is no undo (unless you are inside a transaction). DROP TABLE IF EXISTS avoids an error if the table does not exist. Always double-check before dropping — this is the most destructive SQL command.

Naming Conventions

Use singular, lowercase, snake_case names: user or users, order_item or order_items. Pick one convention (singular vs plural) and stick with it across the project. Primary keys are typically named id; foreign keys are <referenced_table>_id (e.g. user_id).

Example

              -- Create a table with various column modifiers
CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(200) NOT NULL,
  price       DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
  category    VARCHAR(50) NOT NULL DEFAULT 'misc',
  sku         VARCHAR(50) UNIQUE,
  in_stock    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Add a new column
ALTER TABLE products ADD COLUMN weight DECIMAL(6, 2);

-- Rename a column
ALTER TABLE products RENAME COLUMN sku TO product_code;

-- Drop a column
ALTER TABLE products DROP COLUMN weight;

-- Drop the table (destructive!)
DROP TABLE IF EXISTS products;
            

A complete table definition with six column modifiers, then three ALTER TABLE operations and a safe DROP TABLE IF EXISTS.

Try it

  • SQL Formatter

    Format CREATE and ALTER TABLE statements.

  • Code Diff

    Compare old and new schema definitions side by side.

Common mistakes

The mistake

Using FLOAT or REAL for monetary values

The fix

Floating-point types introduce rounding errors. Use DECIMAL(p, s) for money — e.g. DECIMAL(10, 2) stores up to 99,999,999.99 exactly.

The mistake

Running DROP TABLE without IF EXISTS

The fix

If the table does not exist, DROP TABLE errors. Use DROP TABLE IF EXISTS to make scripts idempotent and safe to re-run.

Practice Creating and Altering Tables

2 exercises

Practice Creating and Altering Tables

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors