Creating Tables Syntax & Rules0%
Inspecting Tables with DESCRIBE & SHOW CREATE

Creating Tables Syntax & Rules

Beginner12 min read•Updated: 2026-09-12
Study Materials

Creating Tables Syntax & Rules: Mastering MySQL DDL

Tables are the foundational relational entities where all your application records reside. Creating a production-ready table requires more than just choosing column names; it requires defining primary keys, setting storage engines, establishing character sets, and enforcing business defaults.

1. The Complete CREATE TABLE Syntax

SQL
CREATE TABLE [IF NOT EXISTS] [database_name.]table_name (
column_1 data_type [column_constraints] [DEFAULT default_value],
column_2 data_type [column_constraints] [DEFAULT default_value],
...,
[table_constraints]
) [ENGINE = storage_engine]
[DEFAULT CHARSET = charset_name]
[COLLATE = collation_name];

2. Step-by-Step Production Table Example

Let us design an enterprise table for managing e-commerce customer accounts:

SQL
USE ecommerce_platform;
 
CREATE TABLE IF NOT EXISTS customers (
-- Column 1: Surrogate Primary Key
customer_id INT UNSIGNED AUTO_INCREMENT,
-- Column 2 & 3: Personal Information
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
-- Column 4: Unique Business Identifier
email VARCHAR(255) NOT NULL,
phone_number VARCHAR(20),
-- Column 5: Financial Balance with Strict Default
account_balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
-- Column 6: Status Enum
account_status ENUM('Active', 'Suspended', 'Pending_Verification') NOT NULL DEFAULT 'Pending_Verification',
-- Column 7 & 8: Audit Timestamps
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- Table Constraints
PRIMARY KEY (customer_id),
UNIQUE KEY uq_customer_email (email)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

3. Table Naming Rules in MySQL

  1. 1
    Character Set: Table names can contain letters, numbers, underscores (_), and dollar signs ($).
  2. 2
    Length Limit: Table names cannot exceed 64 characters.
  3. 3
    Reserved Keywords: If your table name collides with a reserved keyword (e.g., order, group, user), you must enclose it in backticks: `` order ``.
  4. 4
    Case Sensitivity: Table names map to physical files on disk. On Linux, Customers and customers are two completely different tables! Always adopt all-lowercase snake_case to guarantee cross-platform compatibility.

4. Creating Tables from Existing Data (CREATE TABLE AS SELECT)

You can create a new table and populate it with data from an existing table in a single atomic statement using CTAS (CREATE TABLE ... AS SELECT):

SQL
-- Create a backup archive table containing only suspended accounts:
CREATE TABLE suspended_customers_archive AS
SELECT customer_id, email, account_balance, created_at
FROM customers
WHERE account_status = 'Suspended';
Important Warning
[!WARNING] While CREATE TABLE ... AS SELECT copies column names, data types, and rows, it does not copy primary keys, indexes, auto-increment properties, or foreign key constraints!

5. Best Practices & Common Pitfalls

  • Always Define a Primary Key: Every single table in an InnoDB database should have an explicit primary key (preferably an UNSIGNED INT or BIGINT AUTO_INCREMENT). Tables without primary keys can cause severe replication and query performance issues in clustered environments.
  • Always Include Audit Columns: Add created_at and updated_at timestamps to every business table. When debugging production issues, knowing exactly when a record was created or modified is invaluable.

Multiple Choice Questions

1. What is the maximum character length for table and column names in MySQL?

A. 32 characters B. 64 characters C. 128 characters D. 256 characters Answer: B Explanation: MySQL table, column, and index identifiers have a maximum length limit of 64 characters.

2. If a table name collides with a reserved SQL keyword like order, which characters must surround the identifier?

A. Single quotes ('order') B. Double quotes ("order") C. Backticks (order) D. Square brackets ([order]) Answer: C Explanation: MySQL uses backticks (...) to quote identifiers that match reserved SQL keywords or contain special characters.

3. What is a key limitation of creating a table using CREATE TABLE AS SELECT ...?

A. It cannot copy more than 10 rows B. It does not copy primary keys, auto-increment definitions, or foreign key constraints from the source table C. It deletes the source table D. It only works with temporary tables Answer: B Explanation: CREATE TABLE ... AS SELECT copies column definitions and data, but omits primary keys, foreign keys, and indexes from the target table.

4. Which storage engine should be explicitly specified for transactional, ACID-compliant tables in modern MySQL?

A. MyISAM B. Memory C. InnoDB D. CSV Answer: C Explanation: ENGINE = InnoDB is the default and industry standard engine providing ACID compliance, row-level locking, and crash recovery.

5. Why should every InnoDB table have an explicit primary key defined?

A. Because queries fail without a primary key B. Because InnoDB organizes table data physically in a clustered B-Tree index ordered by the primary key C. Because MySQL deletes tables without primary keys on reboot D. To prevent tables from exceeding 1,000 rows Answer: B Explanation: In InnoDB, table data is physically structured as a clustered index organized around the primary key. If no key is defined, InnoDB must generate an invisible 6-byte synthetic row ID.

Next Lesson

Inspecting Tables with DESCRIBE & SHOW CREATE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Specialized Types: ENUM, BOOLEAN, JSONInspecting Tables with DESCRIBE & SHOW CREATE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext