MySQL - Create Tables

MySQL - Creating Tables

In a relational database like MySQL, tables are the fundamental structures used to store data. Each table is organized into columns (fields) and rows (records). This tutorial covers the CREATE TABLE statement, best practices, and modern techniques for defining your data structures.

The `CREATE TABLE` Statement

The CREATE TABLE statement is used to define a new table in your database. When creating a table, you must specify its name, the names of its columns, and the data type for each column.

### Key Considerations for Table Design

*   **Table and Column Naming**: Choose descriptive, consistent names. A common convention is `snake_case` (e.g., `user_profiles`) for tables and columns.
*   **Data Types**: Selecting the correct data type is crucial for data integrity and performance. Use the most specific type that fits your data (e.g., `INT` for whole numbers, `DECIMAL` for currency, `VARCHAR` for variable-length strings, `TIMESTAMP` for timestamps).
*   **Primary Key**: Every table should have a primary key, which is a column (or set of columns) that uniquely identifies each row.
*   **Storage Engine**: Modern MySQL defaults to the `InnoDB` storage engine, which provides essential features like transactions, foreign keys, and crash recovery. It's the recommended choice for most applications.

Syntax

The basic syntax for creating a table is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [CONSTRAINTS],
    column2_name data_type [CONSTRAINTS],
    ...,
    PRIMARY KEY (column_name)
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

Example: Creating a `users` Table

Let’s create a modern users table with common fields and constraints.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Let’s break down the components of this example:

id INT AUTO_INCREMENT PRIMARY KEY: An integer primary key that automatically generates a new, unique ID for each user.
VARCHAR(50) NOT NULL UNIQUE: A variable-length string for the username that cannot be empty (NOT NULL) and must be unique across all rows (UNIQUE).
password_hash VARCHAR(255): A field to store a hashed password. Never store plain-text passwords!
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP: A timestamp that automatically records when the row was created.
updated_at ... ON UPDATE CURRENT_TIMESTAMP: A timestamp that automatically updates to the current time whenever the row is modified.

Verification

After creating the table, you can verify its structure using the DESCRIBE (or DESC) command:

DESCRIBE users;

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
username	varchar(50)	NO	UNI	NULL
email	varchar(100)	NO	UNI	NULL
password_hash	varchar(255)	NO		NULL
full_name	varchar(100)	YES		NULL
created_at	timestamp	NO		CURRENT_TIMESTAMP	DEFAULT_GENERATED
updated_at	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

Using `IF NOT EXISTS`

If you try to create a table that already exists, MySQL will throw an error. To avoid this, especially in setup scripts, use the IF NOT EXISTS clause.

CREATE TABLE IF NOT EXISTS users (
    -- ... column definitions ...
);
-- If the table exists, this query does nothing and returns a warning.
-- If it doesn't exist, the table is created.

Creating a Table from an Existing Table (CTAS)

You can create a new table and populate it with data from an existing table using a CREATE TABLE ... AS SELECT (CTAS) statement. This is useful for creating backups, summaries, or subsets of data.

Syntax

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
[WHERE condition];

Important Note: The new table will inherit column names and data types, but it will not inherit constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, or attributes like AUTO_INCREMENT. You must add these manually using ALTER TABLE after creation.

Example: Create a Backup

Let’s create a backup of our users table.

CREATE TABLE users_backup_2024 AS SELECT * FROM users;

This creates a new table users_backup_2024 with an exact copy of the data from users at that moment.

Creating Tables Programmatically

Creating tables programmatically is common in application setup, migrations, and testing. Below are modern examples for various languages.

PHP (using PDO)
NodeJS (using mysql2/promise)
Java (using JDBC)
Python (using mysql-connector-python)



```php
<?php
// createTable.php
$host = 'localhost'; $db = 'test_db'; $user = 'root'; $pass = 'password';
$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully.\n";

    $sql = "CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;";

    $pdo->exec($sql);
    echo "Table 'products' created successfully or already exists.\n";

} catch (PDOException $e) {
    die("Could not connect or create table: " . $e->getMessage());
}

Output: Connected successfully. Table 'products' created successfully or already exists.

// Prerequisite: npm install mysql2 dotenv
require('dotenv').config();
const mysql = require('mysql2/promise');

async function main() {
  let connection;
  try {
    connection = await mysql.createConnection({
        host: process.env.DB_HOST, user: process.env.DB_USER,
        password: process.env.DB_PASSWORD, database: 'test_db'
    });
    console.log('Connected successfully.');

    const createTableSql = `
      CREATE TABLE IF NOT EXISTS tutorials (
        tutorial_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100) NOT NULL,
        author VARCHAR(40) NOT NULL,
        submission_date DATE
      );
    `;

    await connection.query(createTableSql);
    console.log("Table 'tutorials' created successfully or already exists.");

  } catch (err) {
    console.error(`Error: ${err.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

main();

Output: Connected successfully. Table 'tutorials' created successfully or already exists.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {
    static final String DB_URL = "jdbc:mysql://localhost/test_db";
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        String sql = "CREATE TABLE IF NOT EXISTS employees (" +
                     "id INT AUTO_INCREMENT PRIMARY KEY, " +
                     "first_name VARCHAR(50), " +
                     "last_name VARCHAR(50), " +
                     "email VARCHAR(100) NOT NULL UNIQUE)";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            System.out.println("Connected successfully...");
            stmt.executeUpdate(sql);
            System.out.println("Table 'employees' created or already exists.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output: Connected successfully... Table 'employees' created or already exists.

import mysql.connector
from mysql.connector import errorcode

config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'test_db'}
TABLES = {}
TABLES['tutorials'] = (
    "CREATE TABLE `tutorials` ("
    "  `tutorial_id` int(11) NOT NULL AUTO_INCREMENT,"
    "  `title` varchar(100) NOT NULL,"
    "  `author` varchar(40) NOT NULL,"
    "  `submission_date` date DEFAULT NULL,"
    "  PRIMARY KEY (`tutorial_id`)"
    ") ENGINE=InnoDB")

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    print("Connected successfully.")

    cursor.execute(TABLES['tutorials'])
    print("Table 'tutorials' created successfully.")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
        print("Table 'tutorials' already exists.")
    else:
        print(err.msg)
finally:
    if 'cursor' in locals(): cursor.close()
    if 'cnx' in locals() and cnx.is_connected(): cnx.close()

Output: Connected successfully. Table 'tutorials' created successfully.

MySQL - Create Tables

MySQL - Creating Tables

The CREATE TABLE Statement

Syntax

Example: Creating a users Table

Verification

Using IF NOT EXISTS

Creating a Table from an Existing Table (CTAS)

Syntax

Example: Create a Backup

Creating Tables Programmatically

The `CREATE TABLE` Statement

Example: Creating a `users` Table

Using `IF NOT EXISTS`