MySQL CREATE TABLE Statement

MySQL CREATE TABLE Statement – Complete Guide with Examples

The CREATE TABLE statement in MySQL is used to create a new table in a database.
It defines the table structure, including columns, data types, and constraints.

 What Is CREATE TABLE?

CREATE TABLE is a DDL (Data Definition Language) command used to:

•  Create a new table
•  Define column names
•  Set data types
•  Apply constraints (PRIMARY KEY, NOT NULL, etc.)

 Basic Syntax

 Simple Example

•  Table created with 3 columns
•  No constraints yet

 CREATE TABLE with Constraints

Example with Common Constraints

Explanation

• PRIMARY KEY → unique + not null

• NOT NULL → value required

• UNIQUE → no duplicate values

 AUTO_INCREMENT Column

Used for automatic numbering (mostly for IDs).

•  Increases automatically
•  Common for primary keys

 DEFAULT Value in CREATE TABLE

•  Default value used if none provided

 FOREIGN KEY in CREATE TABLE

Used to link two tables.

•  Enforces referential integrity

 CREATE TABLE IF NOT EXISTS

Prevents error if table already exists.

•  Safe execution

 CREATE TABLE Using Another Table

Structure Only

Structure + Data

•  Constraints are not copied in AS SELECT.

 SHOW Table Structure

or

•  Useful for verification

 Common Constraints Used in CREATE TABLE

Common Mistakes

•  Forgetting primary key
•  Using wrong data types
•  Overusing VARCHAR size
•  Missing NOT NULL where required
•  Ignoring foreign key relationships

 Best Practices

•  Always define a primary key
•  Use correct data types
•  Use AUTO_INCREMENT for IDs
•  Apply constraints early
•  Use meaningful column names

 Interview Questions: CREATE TABLE

1.Which command is used to create a table?
CREATE TABLE

2. Can we create a table only if it doesn’t exist?
Yes, using IF NOT EXISTS.

3. What is AUTO_INCREMENT?
Automatically generates numeric values.

4. Does CREATE TABLE AS SELECT copy constraints?
No.

5. How to see table structure?
DESC table_name or SHOW CREATE TABLE.

 Summary

• CREATE TABLE defines table structure
• Supports data types and constraints
•  Used once per table creation
•  Foundation of database design
•  Very important for interviews

Mastering the MySQL CREATE TABLE statement is essential for database design, backend development, and SQL interviews