Personal Catalogue: PHP & MySQL — Chapter 2, Exercise 1
====================================================

TASK
Run all four CREATE TABLE statements from this chapter against the
catalogue database created in Chapter 1, in the correct order.

SOLUTION

Connect first:

    mysql -u root -p catalogue

Then run the four statements in this exact order — item_types and tags
first, since items and item_tags each reference one of them via a
FOREIGN KEY, and MySQL will reject a foreign key referencing a table
that doesn't exist yet:

    CREATE TABLE item_types (
        id TINYINT UNSIGNED PRIMARY KEY,
        name VARCHAR(20) NOT NULL UNIQUE
    );

    INSERT INTO item_types (id, name) VALUES
        (1, 'book'),
        (2, 'cd'),
        (3, 'dvd'),
        (4, 'bluray');

    CREATE TABLE items (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        item_type_id TINYINT UNSIGNED NOT NULL,
        title VARCHAR(255) NOT NULL,
        creator VARCHAR(255),
        format_detail VARCHAR(100),
        release_year SMALLINT UNSIGNED,
        notes TEXT,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (item_type_id) REFERENCES item_types(id)
    );

    CREATE TABLE tags (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE
    );

    CREATE TABLE item_tags (
        item_id INT UNSIGNED NOT NULL,
        tag_id INT UNSIGNED NOT NULL,
        PRIMARY KEY (item_id, tag_id),
        FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
        FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
    );

Verify all four tables exist:

    SHOW TABLES;

Expected output:

    +----------------------+
    | Tables_in_catalogue  |
    +----------------------+
    | item_tags            |
    | item_types           |
    | items                |
    | tags                 |
    +----------------------+

WHY THIS WORKS AS AN ANSWER
----------------------------
It runs the statements in the one order that actually succeeds
(referenced tables before referencing tables) and includes the seed
INSERT for item_types, since items' own foreign key would otherwise
have nothing valid to point to yet when real rows are added later.