MySQL Create Table Syntax Constraints

CREATE table Syntax

The CREATE TABLE statement defines a new table with its columns, data types, constraints, and options. Each column definition specifies the name, data type, and optional constraints like NOT NULL, DEFAULT, and AUTO_INCREMENT.

Creating the shop Database Tables

USE shop;

-- customers table
CREATE table IF NOT EXISTS customers (
    customer_id  INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(255) NOT NULL,
    phone        VARCHAR(20),
    active       TINYINT(1)   NOT NULL DEFAULT 1,
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- products table
CREATE table IF NOT EXISTS products (
    product_id   INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    name         VARCHAR(200)  NOT NULL,
    description  TEXT,
    price        DECIMAL(10,2) NOT NULL,
    stock        INT           UNSIGNED NOT NULL DEFAULT 0,
    category     VARCHAR(100),
    created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- orders table
CREATE table IF NOT EXISTS orders (
    order_id     INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id  INT           UNSIGNED NOT NULL,
    total        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status       ENUM('pending','processing','shipped','delivered','cancelled')
                               NOT NULL DEFAULT 'pending',
    ordered_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- order_items table
CREATE table IF NOT EXISTS order_items (
    item_id      INT           UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id     INT           UNSIGNED NOT NULL,
    product_id   INT           UNSIGNED NOT NULL,
    quantity     INT           UNSIGNED NOT NULL DEFAULT 1,
    unit_price   DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Inspecting Tables

After creating tables, use these commands to inspect their structure:

SHOW TABLES, DESCRIBE, SHOW CREATE TABLE

-- List all tables in the current database
SHOW TABLES;

-- Describe a table's columns, types, and constraints
DESCRIBE customers;
-- or shorthand:
DESC orders;

-- Show the full CREATE table statement (useful for backups/migrations)
SHOW CREATE table customers\G

-- Show table status (engine, row count, charset, etc.)
SHOW table STATUS LIKE 'customers'\G

ALTER TABLE

ALTER TABLE modifies an existing table's structure - add or drop columns, change data types, rename columns, and more. MySQL 8.0 supports most ALTER operations online (without locking the table).

ALTER table - Add, Modify, Rename, Drop Columns

-- Add a new column
ALTER table customers
    ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Add a column at a specific position
ALTER table customers
    ADD COLUMN date_of_birth DATE AFTER last_name;

-- Modify a column's data type or constraints
ALTER table customers
    MODIFY COLUMN phone VARCHAR(30);

-- Rename a column (MySQL 8.0+)
ALTER table customers
    RENAME COLUMN phone TO phone_number;

-- Drop a column
ALTER table customers
    DROP COLUMN date_of_birth;

-- Rename a table
ALTER table customers RENAME TO shop_customers;
-- Rename it back
ALTER table shop_customers RENAME TO customers;

DROP table and TRUNCATE TABLE

DROP TABLE permanently removes a table and all its data. TRUNCATE TABLE removes all rows but keeps the table structure - it's much faster than DELETE FROM table for clearing large tables because it doesn't log individual row deletions.

DROP table and TRUNCATE TABLE - SQL Example

-- Remove all rows but keep the table structure
-- Also resets AUTO_INCREMENT counter to 1
TRUNCATE table order_items;

-- Drop a table permanently (cannot be undone)
DROP table order_items;

-- Drop only if it exists (avoids error)
DROP table IF EXISTS order_items;

-- Drop multiple tables at once
DROP table IF EXISTS order_items, orders, products, customers;

-- Create a new table based on an existing table's structure and data
CREATE table customers_backup AS
    SELECT * FROM customers;

-- Create a table with the same structure but no data
CREATE table customers_empty LIKE customers;

Before you move on