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

MySQL Create Table Syntax Constraints

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

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

-- 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;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

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