SQL DDL: Creating and Managing Tables

The Precise Librarian

SQL is like giving instructions to an extremely literal librarian. Tell them: "Create a shelf labeled 'Students' with a numbered slot for ID, a 50-character space for Name, and a decimal box for GPA." They build it exactly as described — no improvisation, no assumptions.

Say "Studants" instead of "Students" and they stop, look at you, and ask for clarification. Miss a required field and they reject the entire instruction. This precision is a feature, not a bug. When you're storing thousands of records, exactly right matters.

SQL has four major sub-languages. Before you read or modify any data, you use DDL — the commands that define the structure.

SQL Sub-Languages at a Glance

This lesson covers DDL. Everything else builds on it.

CREATE TABLE: Full Syntax

The most important DDL command. It defines a table's columns, their data types, and all constraints.

CREATE TABLE table_name (
    column1  datatype  [constraints],
    column2  datatype  [constraints],
    ...
    [table-level constraints]
);

Common Data Types

The Library Database: Complete DDL

Let's build the full library database schema. Each statement is annotated to explain every decision.

-- Authors table first — no foreign key dependencies
CREATE TABLE authors (
    author_id   INT           PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50)   NOT NULL,
    last_name   VARCHAR(50)   NOT NULL,
    birth_year  INT           CHECK (birth_year > 1000 AND birth_year <= 2010),
    nationality VARCHAR(50)
);

-- Books reference authors, so authors must exist first
CREATE TABLE books (
    book_id           INT          PRIMARY KEY AUTO_INCREMENT,
    title             VARCHAR(200) NOT NULL,
    isbn              VARCHAR(13)  UNIQUE,
    author_id         INT,
    published_year    INT          CHECK (published_year > 1400),
    genre             VARCHAR(50),
    copies_available  INT          DEFAULT 1
                                   CHECK (copies_available >= 0),
    FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Every constraint in the books table explained:

• PRIMARY KEY AUTO_INCREMENT — surrogate key, database assigns the next integer automatically
• VARCHAR(200) NOT NULL — a book without a title is meaningless; reject it at the database level
• VARCHAR(13) UNIQUE — ISBNs are 10 or 13 digits and must be globally unique; NULL allowed (some records lack ISBNs)
• author_id INT — nullable FK; a book can exist without a known author (anonymous works)
• CHECK (published_year > 1400) — Gutenberg's press was ~1440; nothing before that makes sense here
• DEFAULT 1 — a newly added book starts with 1 available copy
• CHECK (copies_available >= 0) — you cannot have negative copies; the DBMS prevents it
• ON DELETE SET NULL — if the author is deleted, the book remains but author_id becomes NULL
• ON UPDATE CASCADE — if an author's PK changes (rare), FK values update automatically

CREATE TABLE members (
    member_id     INT          PRIMARY KEY AUTO_INCREMENT,
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    email         VARCHAR(100) NOT NULL UNIQUE,
    phone         VARCHAR(20),
    join_date     DATE         DEFAULT (CURRENT_DATE),
    membership_type  ENUM('standard', 'premium', 'student')
                     NOT NULL DEFAULT 'standard',
    is_active     BOOLEAN      DEFAULT TRUE
);

CREATE TABLE loans (
    loan_id       INT  PRIMARY KEY AUTO_INCREMENT,
    book_id       INT  NOT NULL,
    member_id     INT  NOT NULL,
    loan_date     DATE NOT NULL DEFAULT (CURRENT_DATE),
    due_date      DATE NOT NULL,
    return_date   DATE,          -- NULL means not yet returned
    FOREIGN KEY (book_id)   REFERENCES books(book_id)   ON DELETE RESTRICT,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    CHECK (due_date > loan_date),
    CHECK (return_date IS NULL OR return_date >= loan_date)
);

ALTER TABLE: Modifying Existing Structure

After a table is in production, you often need to change it. ALTER TABLE handles this without dropping the table and losing data.

Add a column

ALTER TABLE members
ADD COLUMN date_of_birth DATE;

Add a column with a constraint

ALTER TABLE members
ADD COLUMN max_loans INT DEFAULT 5 CHECK (max_loans BETWEEN 1 AND 20);

Modify a column's data type or constraint

-- MySQL syntax
ALTER TABLE books
MODIFY COLUMN title VARCHAR(300) NOT NULL;

-- PostgreSQL syntax
ALTER TABLE books
ALTER COLUMN title TYPE VARCHAR(300);

Drop a column

ALTER TABLE members
DROP COLUMN phone;

Add a constraint to an existing table

ALTER TABLE books
ADD CONSTRAINT chk_genre
CHECK (genre IN ('Fiction', 'Non-Fiction', 'Science', 'History', 'Biography'));

Drop a named constraint

ALTER TABLE books
DROP CONSTRAINT chk_genre;

Rename a column (MySQL 8.0+ / PostgreSQL)

ALTER TABLE members
RENAME COLUMN membership_type TO plan_type;

DROP TABLE and TRUNCATE

DROP TABLE

Removes the table and all its data permanently. This cannot be undone.

DROP TABLE loans;
DROP TABLE IF EXISTS loans;   -- safer: no error if table doesn't exist

Warning: You cannot drop a parent table while child tables have foreign keys referencing it. Drop children first, or drop with CASCADE (PostgreSQL).

TRUNCATE TABLE

Deletes all rows but keeps the table structure. Much faster than DELETE FROM table because it doesn't log individual row deletions.

TRUNCATE TABLE loans;

Schemas: Namespacing Your Tables

A schema is a named container for database objects. Think of it as a folder inside the database.

-- Create a schema
CREATE SCHEMA library_system;

-- Create a table inside it
CREATE TABLE library_system.books ( ... );

-- Set default schema for the session (MySQL)
USE library_system;

-- PostgreSQL: set search path
SET search_path TO library_system;

Schemas are useful when one database server hosts multiple applications — each gets its own schema, and names don't clash.

The Creation Order Matters

When building a multi-table schema, parent tables must be created before child tables (because the FK needs a target to reference):

1. authors        (no FKs)
2. books          (FK → authors)
3. members        (no FKs)
4. loans          (FK → books, FK → members)

Dropping in reverse order:

1. loans
2. books
3. authors, members

DDL Is the Foundation

Every query you write, every index you add, every transaction you commit operates on the structure DDL creates. A well-designed CREATE TABLE statement prevents entire categories of bugs before the application writes a single row.

The constraints you define here — NOT NULL, UNIQUE, CHECK, FOREIGN KEY — are your last line of defense against bad data. Application code can have bugs. The database's constraints don't.

Next lesson: DML — inserting, reading, updating, and deleting the actual data in your tables.