Create Table

Creating MySQL Tables in PHP: Constraints, Keys, and Data Types

PHP applications define MySQL table schemas using CREATE TABLE IF NOT EXISTS statements executed via PDO during setup or database migrations. Designing tables with primary keys, NOT NULL constraints, unique indexes, and foreign keys protects data integrity right at the database layer.

Defining a Clean Sample Table Schema

Use BIGINT UNSIGNED AUTO_INCREMENT (or INT UNSIGNED) for surrogate primary keys, VARCHAR for short strings, DECIMAL(10, 2) for exact money values, and TIMESTAMP columns for audit trails. Always choose the InnoDB engine so transactions and foreign keys work reliably.

<?php
declare(strict_types=1);

function createUsersAndOrdersTables(PDO $pdo): void
{
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS users (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            full_name VARCHAR(120) NOT NULL,
            email VARCHAR(255) NOT NULL UNIQUE,
            status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=active, 0=suspended',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS orders (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT UNSIGNED NOT NULL,
            total_amount DECIMAL(10, 2) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
}

Choosing Appropriate Column Types

Avoid FLOAT or DOUBLE for prices because binary rounding causes penny discrepancies (19.99 becoming 19.989999). Use DECIMAL(10, 2) or store integer cents in INT UNSIGNED instead. Similarly, prefer small TINYINT status codes with clear COMMENT labels over rigid SQL ENUM columns, which require table locks to alter later.

Common Mistakes to Avoid

• Mismatched foreign key types: If users.id is BIGINT UNSIGNED, then orders.user_id must also be BIGINT UNSIGNED or MySQL rejects the foreign key with errno 150.
• Running CREATE TABLE inside regular page loads: Schema changes lock metadata; run them inside deployment migrations or CLI installers instead of user endpoints.
• Skipping indexes on foreign keys and lookup columns: Queries filtering by email or user_id trigger slow full-table scans without indexes.

Frequently Asked Questions

What does ON DELETE CASCADE do on a foreign key?

It tells MySQL to delete child rows in orders automatically whenever the parent row in users is deleted, preventing orphan records.

Why use IF NOT EXISTS when creating tables?

It makes your setup script safe to re-run without throwing an error if the table was already created earlier.

Should I store boolean flags as TINYINT(1) in MySQL?

Yes. MySQL maps BOOLEAN internally to TINYINT(1) (0 for false, 1 for true), which works cleanly with PDO.