Creating Tables Syntax & Rules0%

Inspecting Tables with DESCRIBE & SHOW CREATE

Creating Tables Syntax & Rules

Beginner12 min read•Updated: 2026-09-12

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

1CREATE 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]
6) [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:

1USE ecommerce_platform;
2 
3CREATE 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)
28) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

3. Table Naming Rules in MySQL

1
Character Set: Table names can contain letters, numbers, underscores (_), and dollar signs ($).
2
Length Limit: Table names cannot exceed 64 characters.
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
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):

1-- Create a backup archive table containing only suspended accounts:
2CREATE TABLE suspended_customers_archive AS
3SELECT customer_id, email, account_balance, created_at
4FROM customers
5WHERE 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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Inspecting Tables with DESCRIBE & SHOW CREATE

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

Related Lessons

Previous Lesson	Next Lesson
Specialized Types: ENUM, BOOLEAN, JSON	Inspecting Tables with DESCRIBE & SHOW CREATE

Practice Quiz

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

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Inspecting Tables with DESCRIBE & SHOW CREATE

Creating Tables Syntax & Rules

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Creating Tables Syntax & Rules: Mastering MySQL DDL

1. The Complete `CREATE TABLE` Syntax

1CREATE 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]
6) [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:

1USE ecommerce_platform;
2 
3CREATE 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)
28) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

3. Table Naming Rules in MySQL

1
Character Set: Table names can contain letters, numbers, underscores (_), and dollar signs ($).
2
Length Limit: Table names cannot exceed 64 characters.
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
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):

1-- Create a backup archive table containing only suspended accounts:
2CREATE TABLE suspended_customers_archive AS
3SELECT customer_id, email, account_balance, created_at
4FROM customers
5WHERE 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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

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

Question 5

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

Next Lesson

Inspecting Tables with DESCRIBE & SHOW CREATE

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

Related Lessons

Previous Lesson	Next Lesson
Specialized Types: ENUM, BOOLEAN, JSON	Inspecting Tables with DESCRIBE & SHOW CREATE

Practice Quiz

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

Previous Next

Creating Tables Syntax & Rules: Mastering MySQL DDL

1. The Complete CREATE TABLE Syntax

2. Step-by-Step Production Table Example

3. Table Naming Rules in MySQL

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

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Inspecting Tables with DESCRIBE & SHOW CREATE

Related Lessons

Practice Quiz

Loading Page Content...

Creating Tables Syntax & Rules: Mastering MySQL DDL

1. The Complete CREATE TABLE Syntax

2. Step-by-Step Production Table Example

3. Table Naming Rules in MySQL

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

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Inspecting Tables with DESCRIBE & SHOW CREATE

Related Lessons

Practice Quiz

1. The Complete `CREATE TABLE` Syntax

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

1. The Complete `CREATE TABLE` Syntax

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