MySQL Create Table

Learn how to create tables in MySQL

📋 What is CREATE TABLE?

The CREATE TABLE statement creates a new table in a database. Tables store data in rows and columns, where each column has a specific data type defining what kind of information it can hold. 


-- Create a simple users table
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    email VARCHAR(100)
);

Output:

Query OK, 0 rows affected (0.05 sec)

✓ Table 'users' created successfully

Key Concepts

📊

Columns

Define structure with data types 

id INT,
name VARCHAR(50)
🔑

Primary Key

Unique identifier for each row 

id INT PRIMARY KEY
🔢

AUTO_INCREMENT

Automatically generate unique numbers 

id INT AUTO_INCREMENT
📝

Data Types

INT, VARCHAR, DATE, TEXT, etc. 

age INT,
bio TEXT

🔹 Basic CREATE TABLE Syntax

The basic syntax requires a table name and at least one column definition. Each column needs a name and data type. Multiple columns are separated by commas, and the entire definition is enclosed in parentheses. 

-- Basic syntax
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

-- Example: Simple products table
CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

Output:

Table 'products' created with 3 columns

✓ Ready to store product data

🔹 CREATE TABLE with Primary Key

A primary key uniquely identifies each record in a table. It must contain unique values and cannot be NULL. Using AUTO_INCREMENT with a primary key automatically generates sequential numbers for new records, eliminating the need to manually assign IDs. 

-- Table with primary key
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

-- Alternative syntax
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE,
    total DECIMAL(10, 2),
    PRIMARY KEY (order_id)
);

Output:

Table created with AUTO_INCREMENT primary key

✓ IDs will be generated automatically: 1, 2, 3...

🔹 Common Data Types

MySQL offers various data types for different kinds of information. Choosing the right data type ensures efficient storage and proper data validation. Numeric types store numbers, string types store text, and date types store temporal data with different levels of precision. 

-- Table demonstrating common data types
CREATE TABLE employees (
    -- Numeric types
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    age INT,
    salary DECIMAL(10, 2),
    
    -- String types
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    bio TEXT,
    
    -- Date and time types
    birth_date DATE,
    hire_date DATETIME,
    
    -- Boolean type
    is_active BOOLEAN
);

Data Type Reference:

  • INT: Whole numbers (-2147483648 to 2147483647)
  • VARCHAR(n): Variable-length text up to n characters
  • TEXT: Large text data up to 65,535 characters
  • DECIMAL(m,d): Exact decimal numbers
  • DATE: Date values (YYYY-MM-DD)
  • DATETIME: Date and time combined

🔹 CREATE TABLE IF NOT EXISTS

Using IF NOT EXISTS prevents errors when a table already exists. This is essential for scripts that may run multiple times, such as initialization scripts or migrations, ensuring your code doesn't fail if the table is already present in the database. 

-- Safe table creation
CREATE TABLE IF NOT EXISTS categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100),
    description TEXT
);

-- Won't cause error if table exists
CREATE TABLE IF NOT EXISTS categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(100)
);

Output:

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Note: Table already exists (no error thrown)

🔹 Complete Example

This comprehensive example creates a blog posts table with various column types, constraints, and best practices. It includes a primary key, different data types for different content, and default values for automatic timestamp tracking of when posts are created. 

-- Complete blog posts table
CREATE TABLE IF NOT EXISTS blog_posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author VARCHAR(100),
    view_count INT DEFAULT 0,
    is_published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Verify table creation
SHOW TABLES;

-- View table structure
DESCRIBE blog_posts;

Output:

Field Type Null Key
post_id int NO PRI
title varchar(200) NO
content text YES

💡 Best Practices

  • Always define a PRIMARY KEY for each table
  • Use AUTO_INCREMENT for primary key columns
  • Choose appropriate data types to save storage space
  • Use VARCHAR instead of CHAR for variable-length text
  • Add NOT NULL constraints for required fields
  • Include created_at and updated_at timestamp columns
  • Use meaningful and descriptive column names

🧠 Test Your Knowledge

Which keyword automatically generates unique numbers for a column?