MySQL Create Table

What Does Creating a Table Involve?

In simple terms: A table is like a spreadsheet with strict rules. Before storing data, you define its columns — each with a name and a type (number, text, date) — and the rules the data must follow: which values are required, which must be unique, and how tables relate to each other. A well-designed table prevents bad data from ever being saved.

Technically: CREATE TABLE defines a table's columns, data types, defaults, and constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY. It also defines indexes for fast searches and table options such as the storage engine (InnoDB) and character set. From PHP, the statement is executed with mysqli::query().

Anatomy of a CREATE TABLE Statement

CREATE TABLE IF NOT EXISTS products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sku VARCHAR(30) NOT NULL,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT UNSIGNED NOT NULL DEFAULT 0,
    category VARCHAR(50) NOT NULL DEFAULT 'general',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_products_sku (sku),
    KEY idx_products_category (category),
    CONSTRAINT chk_products_price CHECK (price >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Line by Line

Choosing Data Types

Note: TIMESTAMP converts values between the connection's time zone and UTC and is limited to the year 2038. DATETIME is simpler and has no such limit.

Relationships With Foreign Keys

A foreign key links a column to the primary key of another table and guarantees that the referenced row exists:

CREATE TABLE IF NOT EXISTS orders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_orders_customer_created (customer_id, created_at),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
        REFERENCES customers (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Foreign keys protect data integrity: an order can never refer to a customer that does not exist. The referencing column must have the same type as the referenced one (here, both INT UNSIGNED).

Creating the Shop Schema From PHP

The following script creates all four tables of the example database. It runs with an account that has the CREATE privilege (see MySQL Create DB):

<?php
// bin/create-tables.php — run with: php bin/create-tables.php
$db = new mysqli('127.0.0.1', getenv('DB_ADMIN_USER') ?: 'root', getenv('DB_ADMIN_PASSWORD') ?: '', 'gocourse_shop');
$db->set_charset('utf8mb4');

$options = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci';

$tables = [
    'customers' => "CREATE TABLE IF NOT EXISTS customers (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) NOT NULL,
        city VARCHAR(100) NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uq_customers_email (email)
    ) $options",

    'products' => "CREATE TABLE IF NOT EXISTS products (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        sku VARCHAR(30) NOT NULL,
        name VARCHAR(150) NOT NULL,
        price DECIMAL(10,2) NOT NULL,
        stock INT UNSIGNED NOT NULL DEFAULT 0,
        category VARCHAR(50) NOT NULL DEFAULT 'general',
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uq_products_sku (sku),
        KEY idx_products_category (category),
        CONSTRAINT chk_products_price CHECK (price >= 0)
    ) $options",

    'orders' => "CREATE TABLE IF NOT EXISTS orders (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        customer_id INT UNSIGNED NOT NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',
        total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_orders_customer_created (customer_id, created_at),
        CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE RESTRICT
    ) $options",

    'order_items' => "CREATE TABLE IF NOT EXISTS order_items (
        order_id INT UNSIGNED NOT NULL,
        product_id INT UNSIGNED NOT NULL,
        quantity INT UNSIGNED NOT NULL,
        unit_price DECIMAL(10,2) NOT NULL,
        PRIMARY KEY (order_id, product_id),
        CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
        CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
        CONSTRAINT chk_items_quantity CHECK (quantity > 0)
    ) $options",
];

try {
    foreach ($tables as $name => $sql) {
        $db->query($sql);
        echo "Table ready: $name\n";
    }
} catch (mysqli_sql_exception $e) {
    fwrite(STDERR, "Failed: " . $e->getMessage() . "\n");
    exit(1);
}

$result = $db->query('DESCRIBE products');

echo "\n";
while ($column = $result->fetch_assoc()) {
    echo rtrim(sprintf('%-11s %-14s %-4s %s', $column['Field'], $column['Type'], $column['Null'], $column['Key'])) . "\n";
}

Output (MySQL 8.4)

Table ready: customers
Table ready: products
Table ready: orders
Table ready: order_items

id          int unsigned   NO   PRI
sku         varchar(30)    NO   UNI
name        varchar(150)   NO
price       decimal(10,2)  NO
stock       int unsigned   NO
category    varchar(50)    NO   MUL
is_active   tinyint(1)     NO
created_at  datetime       NO
updated_at  datetime       NO

Step-by-Step Explanation

1. The tables are created in dependency order: customers and products first, because orders and order_items reference them with foreign keys.
2. IF NOT EXISTS makes the script safe to run again.
3. order_items uses a composite primary key (order_id, product_id): each product appears at most once per order.
4. order_items.unit_price stores the price at the time of the order, so later price changes do not alter past orders.
5. DESCRIBE products returns one row per column. PRI, UNI, and MUL in the Key column indicate primary key, unique index, and a non-unique index. BOOLEAN appears as tinyint(1).

Changing Tables Later: Migrations

Applications evolve: new columns, new indexes, new tables. These changes use ALTER TABLE:

ALTER TABLE customers ADD COLUMN phone VARCHAR(20) NULL AFTER email;
ALTER TABLE products ADD INDEX idx_products_name (name);

In real projects, every schema change is stored as a versioned migration file in the repository, and a tool applies them in order on each environment (development, staging, production). Popular tools include Laravel Migrations, Doctrine Migrations, and Phinx. Migrations keep all databases consistent and make changes reviewable like any other code.

Common Mistakes

Using FLOAT for Money

Effect: Rounding errors in totals.

Fix: Use DECIMAL(10,2) or integer paise.

Missing Unique Constraints

Effect: Duplicate emails or SKUs are inserted when two requests run at the same time, even if PHP checked first.

Fix: Enforce uniqueness in the database with UNIQUE keys.

Foreign Key Type Mismatch

Effect: Error 3780 / "incompatible" foreign key when, for example, INT references INT UNSIGNED.

Fix: Use exactly the same type for both columns.

Creating Tables in the Wrong Order

Effect: Foreign keys fail because the referenced table does not exist yet.

Fix: Create referenced tables first.

Using the Old utf8 Character Set or MyISAM

Effect: No emoji support; no transactions or foreign keys.

Fix: Use utf8mb4 and InnoDB.

Best Practices

• Give every table an auto-increment primary key (or a deliberate composite key).
• Use precise types: DECIMAL for money, DATETIME for timestamps, VARCHAR with realistic lengths.
• Enforce rules in the database: NOT NULL, UNIQUE, CHECK, and foreign keys.
• Add indexes for columns used in searches, joins, and sorting.
• Manage schema changes with versioned migrations.

Official reference: https\://www.php.net/manual/en/mysqli.query.php