SQL CREATE TABLE and ALTER TABLE: DDL Essentials

SQL CREATE TABLE and ALTER TABLE are the foundational Data Definition Language (DDL) statements that define your database structure. Before you can SELECT, JOIN, or aggregate data, the underlying tables must exist — and these are the commands that build them.

Introduction

Every SQL query you write runs against a table that someone designed. Before you can SELECT, JOIN, or aggregate data, the underlying tables must exist — and that's where Data Definition Language (DDL) comes in.

DDL statements like CREATE TABLE and ALTER TABLE define the structure of your database: which tables exist, what columns they contain, and what constraints protect data integrity. Even if you spend most of your time writing SELECT queries, understanding DDL makes you a stronger SQL developer because you'll know why tables are shaped the way they are.

In this guide, we'll walk through the essentials of CREATE TABLE and ALTER TABLE using the Chinook digital media database as our reference schema.

Note: The SQiLs practice environment supports SELECT queries only. The DDL examples below are for learning — to explore real table structures hands-on, head to the exercises page.

Table of Contents

1. What Is DDL?
2. CREATE TABLE Syntax
3. Column Data Types
4. Constraints and Keys
5. ALTER TABLE Syntax
6. Real-World Example: The Chinook Schema
7. Cheat Sheet
8. Common Mistakes
9. Practice Exercises
10. Summary
11. FAQs

What Is DDL?

Data Definition Language (DDL) is the subset of SQL used to create, modify, and remove database objects like tables, indexes, and views. The main DDL statements are:

DDL statements change the schema (structure), while DML statements (SELECT, INSERT, UPDATE, DELETE) change the data. If you're comfortable with DML basics like JOINs or GROUP BY, learning DDL is the natural next step.

CREATE TABLE Syntax

The CREATE TABLE statement defines a new table with its columns and constraints:

CREATE TABLE table_name (
    column1  data_type  [constraint],
    column2  data_type  [constraint],
    ...
    [table_constraints]
);

A Simple Example

CREATE TABLE Artist (
    ArtistId  INTEGER      PRIMARY KEY,
    Name      VARCHAR(120) NOT NULL
);

This creates the Artist table from the Chinook database with:

• ArtistId — an integer that uniquely identifies each artist
• Name — a variable-length string up to 120 characters that cannot be null

Multi-Column Example

Here's how the Track table might be defined:

CREATE TABLE Track (
    TrackId      INTEGER       PRIMARY KEY,
    Name         VARCHAR(200)  NOT NULL,
    AlbumId      INTEGER       REFERENCES Album(AlbumId),
    MediaTypeId  INTEGER       NOT NULL REFERENCES MediaType(MediaTypeId),
    GenreId      INTEGER       REFERENCES Genre(GenreId),
    Composer     VARCHAR(220),
    Milliseconds INTEGER       NOT NULL,
    Bytes        INTEGER,
    UnitPrice    DECIMAL(10,2) NOT NULL
);

Notice that some columns allow NULL (like Composer and Bytes) while others require a value. The REFERENCES keyword creates foreign key relationships — more on that below.

Column Data Types

Choosing the right data type is critical for data integrity and performance. Here are the most common types:

For a deeper dive into data types and when to use each one, see our SQL Data Types Guide.

Constraints and Keys

Constraints enforce rules on your data. They're the guardrails that prevent bad data from entering your tables.

Constraint Reference

Primary Keys

Every table should have a primary key — a column (or combination of columns) that uniquely identifies each row:

-- Single-column primary key
CREATE TABLE Genre (
    GenreId  INTEGER      PRIMARY KEY,
    Name     VARCHAR(120)
);

-- Composite primary key (two columns together)
CREATE TABLE PlaylistTrack (
    PlaylistId  INTEGER NOT NULL,
    TrackId     INTEGER NOT NULL,
    PRIMARY KEY (PlaylistId, TrackId)
);

The PlaylistTrack table uses a composite primary key because no single column uniquely identifies a row — it's the combination of playlist and track that must be unique.

Foreign Keys

Foreign keys link tables together and enforce referential integrity — you can't insert a track with an AlbumId that doesn't exist in the Album table:

CREATE TABLE Album (
    AlbumId   INTEGER      PRIMARY KEY,
    Title     VARCHAR(160) NOT NULL,
    ArtistId  INTEGER      NOT NULL,
    FOREIGN KEY (ArtistId) REFERENCES Artist(ArtistId)
);

These relationships are exactly what you leverage when writing JOIN queries.

ALTER TABLE Syntax

Once a table exists, ALTER TABLE lets you modify its structure without recreating it from scratch.

Common ALTER TABLE Operations

Adding a Column

Suppose you want to add an email column to the Artist table:

ALTER TABLE Artist
ADD COLUMN Email VARCHAR(100);

The new column will be NULL for all existing rows unless you specify a DEFAULT:

ALTER TABLE Artist
ADD COLUMN IsActive BOOLEAN DEFAULT TRUE;

Dropping a Column

Remove a column you no longer need:

ALTER TABLE Artist
DROP COLUMN Email;

Warning: Dropping a column permanently deletes all data in that column. Always back up first.

Modifying a Column

Change a column's data type (syntax varies by database):

-- PostgreSQL
ALTER TABLE Track
ALTER COLUMN Composer TYPE TEXT;

-- MySQL
ALTER TABLE Track
MODIFY COLUMN Composer TEXT;

-- SQL Server
ALTER TABLE Track
ALTER COLUMN Composer TEXT;

Adding a Constraint

Add a check constraint after the table is created:

ALTER TABLE Track
ADD CONSTRAINT chk_price CHECK (UnitPrice >= 0);

Real-World Example: The Chinook Schema

The Chinook database models a digital media store. Here's how its core tables relate:

Artist  1──M  Album  1──M  Track  M──M  Playlist
                              │
                              └── InvoiceLine ──┐
                                                │
                          Customer  1──M  Invoice

Each relationship is enforced by foreign keys:

• Album.ArtistId → Artist.ArtistId
• Track.AlbumId → Album.AlbumId
• InvoiceLine.TrackId → Track.TrackId
• Invoice.CustomerId → Customer.CustomerId

Understanding this DDL design helps you write better queries. When you know that Track has a foreign key to Album, you know you can confidently JOIN those tables — and you know the column names to use.

Exploring Table Structure with SELECT

Even though you can't run DDL in the SQiLs practice environment, you can explore table structures using SQLite's metadata:

-- List all tables in the database
SELECT name
FROM sqlite_master
WHERE type = 'table'
ORDER BY name;

-- See column details for a specific table
PRAGMA table_info('Track');

These queries help you understand how tables were created without needing DDL access.

Cheat Sheet

-- ═══════════════════════════════════════
--  DDL CHEAT SHEET
-- ═══════════════════════════════════════

-- CREATE a table
CREATE TABLE TableName (
    Id       INTEGER PRIMARY KEY,
    Name     VARCHAR(100) NOT NULL,
    ParentId INTEGER REFERENCES OtherTable(Id),
    Price    DECIMAL(10,2) DEFAULT 0.00,
    CHECK (Price >= 0)
);

-- ADD a column
ALTER TABLE TableName ADD COLUMN Email VARCHAR(100);

-- DROP a column
ALTER TABLE TableName DROP COLUMN Email;

-- RENAME a column
ALTER TABLE TableName RENAME COLUMN Name TO FullName;

-- ADD a constraint
ALTER TABLE TableName ADD CONSTRAINT uq_email UNIQUE (Email);

-- DROP a table (irreversible!)
DROP TABLE IF EXISTS TableName;

Common Mistakes

1. Forgetting NOT NULL on Required Columns

-- Bad: allows NULL names
CREATE TABLE Artist (
    ArtistId INTEGER PRIMARY KEY,
    Name     VARCHAR(120)
);

-- Good: enforces that every artist has a name
CREATE TABLE Artist (
    ArtistId INTEGER PRIMARY KEY,
    Name     VARCHAR(120) NOT NULL
);

2. Using the Wrong Data Type

Storing prices as INTEGER loses decimal precision. Storing zip codes as INTEGER drops leading zeros (e.g., 01234 becomes 1234). Always match the data type to the actual data. See our data types guide for details.

3. Missing Foreign Keys

Without foreign keys, nothing prevents orphaned rows — like a Track referencing an AlbumId that doesn't exist. Always define REFERENCES for relationship columns.

4. Using ALTER TABLE to Fix Bad Design

If you find yourself repeatedly adding and dropping columns, it's a sign the table was poorly designed from the start. Step back and redesign the schema before layering on patches.

5. Forgetting IF EXISTS / IF NOT EXISTS

-- Crashes if table already exists
CREATE TABLE Artist (...);

-- Safe: only creates if missing
CREATE TABLE Artist IF NOT EXISTS (...);

-- Safe: only drops if present
DROP TABLE IF EXISTS Artist;

Practice Exercises

While SQiLs exercises use SELECT queries, they're an excellent way to explore the table structures created by DDL. Try these to strengthen your understanding:

Summary

• DDL (Data Definition Language) defines database structure — tables, columns, constraints, and indexes.
• CREATE TABLE builds new tables with typed columns and constraints like PRIMARY KEY, NOT NULL, UNIQUE, and FOREIGN KEY.
• ALTER TABLE modifies existing tables — add columns, drop columns, rename, or add constraints.
• Constraints are your data quality guardrails. Use NOT NULL for required fields, FOREIGN KEY for relationships, and CHECK for business rules.
• Understanding DDL helps you write better SELECT queries because you'll know how tables relate and what data types to expect.
• In the SQiLs practice environment, use sqlite_master and PRAGMA table_info() to explore table structures.

FAQs

What is the difference between DDL and DML?

DDL (Data Definition Language) changes the structure of your database — creating tables, adding columns, defining constraints. DML (Data Manipulation Language) changes the data — inserting, updating, deleting, and selecting rows. Think of DDL as building the container and DML as filling it.

Can I undo a CREATE TABLE or ALTER TABLE?

It depends on your database. PostgreSQL wraps DDL in transactions, so you can ROLLBACK a CREATE TABLE. MySQL and SQLite auto-commit most DDL statements immediately. Always test schema changes in a development environment first.

When should I use ALTER TABLE vs. recreating the table?

Use ALTER TABLE for small, additive changes like adding a column or constraint. If you need to fundamentally restructure a table — change primary keys, split into multiple tables, or rename many columns — it's often cleaner to create a new table, migrate the data, then drop the old one.

What happens to existing data when I add a NOT NULL column?

If you add a NOT NULL column without a DEFAULT value, the database will reject the change because existing rows would violate the constraint. Always provide a DEFAULT when adding NOT NULL columns to tables that already contain data:

ALTER TABLE Artist
ADD COLUMN Country VARCHAR(50) NOT NULL DEFAULT 'Unknown';