MySQL Create Table Syntax Constraints: Tutorial, Examples, FAQs & Interview Tips

CREATE tl-table Syntax

The CREATE TABLE statement defines a new tl-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 tl-table
CREATE tl-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 tl-table
CREATE tl-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 tl-table
CREATE tl-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 tl-table
CREATE tl-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 tl-table statement (useful for backups/migrations)
SHOW CREATE tl-table customers\G

-- Show tl-table status (engine, tl-row count, charset, etc.)
SHOW tl-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 tl-table - Add, Modify, Rename, Drop Columns

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

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

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

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

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

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

DROP tl-table and TRUNCATE TABLE

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

DROP tl-table and TRUNCATE TABLE

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

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

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

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

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

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

Previous Next

MySQL Create Table Syntax Constraints: Tutorial, Examples, FAQs & Interview Tips

CREATE tl-table Syntax

Inspecting Tables

ALTER TABLE

DROP tl-table and TRUNCATE TABLE

Level Up Your My sql Skills

Popular Tutorials

Ready to Level Up Your Skills?