Create Table

Creating Database Tables with SQL Constraints

Tables define structured schemas with defined columns, data types, default values, and integrity constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE).

Common Column Data Types & Constraints

• INT UNSIGNED AUTO_INCREMENT: Integer surrogate primary key.
• VARCHAR(length): Variable-length character string.
• DECIMAL(precision, scale): Exact precision floating-point number (ideal for currency).
• PRIMARY KEY: Uniquely identifies each record row.
• FOREIGN KEY: Establishes relational integrity linking to another table.

flowchart TD
    A["SQL Table Definition (users)"] --> B["id: INT Primary Key"]
    A --> C["email: VARCHAR(100) UNIQUE NOT NULL"]
    A --> D["created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP"]

Practical Code Example

<?php
declare(strict_types=1);

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    // SQL statement for table creation
    $sql = "CREATE TABLE IF NOT EXISTS orders (
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id INT UNSIGNED NOT NULL,
        total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
        status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    $pdo->exec($sql);
    echo "Table 'orders' created successfully!
";
} catch (PDOException $e) {
    die("Table Creation Error: " . $e->getMessage());
}

Best Practices

• Use DECIMAL(10,2) for Currency: Never use FLOAT or DOUBLE for financial amounts due to float rounding inaccuracies.
• Always Explicitly Set Primary Keys: Every table should have an id INT AUTO_INCREMENT PRIMARY KEY column.
• Use ENGINE=InnoDB: Enables transaction support and foreign key constraints.

Self-Check Challenge

What SQL data type should be used to store money amounts accurately without precision loss? (DECIMAL)