MySQL CREATE TABLE: Create a New Table with Columns and Constraints

Use MySQL CREATE TABLE to define a new table, its columns, datatypes, default values, keys, and other constraints.

CREATE TABLE student (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(20) NOT NULL,
    mark TINYINT UNSIGNED NOT NULL DEFAULT 0,
    gender VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

This creates a table named student with five columns and an auto-incrementing primary key.

Basic CREATE TABLE Syntax Top ↑

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Each column definition normally includes a column name, a datatype, and any constraints required by the application.

Create a Simple Table Top ↑

The original page used a one-column example. A modern equivalent is:

CREATE TABLE sample_tb (
    empno VARCHAR(6) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

This creates sample_tb with one required text column named empno.

IF NOT EXISTS Top ↑

If the table might already exist, add IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS sample_tb (
    empno VARCHAR(6) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

This avoids an error for an existing table with the same name.

Columns and Datatypes Top ↑

Choose datatypes based on the values a column must store.

CREATE TABLE employee (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    empno VARCHAR(10) NOT NULL,
    name VARCHAR(80) NOT NULL,
    joining_date DATE,
    salary DECIMAL(10,2),
    PRIMARY KEY (id)
);

Useful datatype references:

• numeric datatypes
• date and time datatypes

NOT NULL and NULL Top ↑

NOT NULL means a row must contain a non-NULL value for that column unless another mechanism supplies one.

CREATE TABLE customer (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(80) NOT NULL,
    phone VARCHAR(20) NULL,
    PRIMARY KEY (id)
);

Use NULL only when "value unknown or not provided" is a valid state for that column. See SQL NULL values.

DEFAULT Values Top ↑

A DEFAULT supplies a value when an INSERT omits that column:

CREATE TABLE exam_result (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    student_name VARCHAR(80) NOT NULL,
    mark TINYINT UNSIGNED NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending',
    PRIMARY KEY (id)
);

Choose defaults that represent a real application state. Do not use an empty string or zero merely because a column is NOT NULL.

PRIMARY KEY Top ↑

A primary key uniquely identifies each row. It cannot contain NULL values.

CREATE TABLE department (
    department_id INT UNSIGNED NOT NULL,
    department_name VARCHAR(80) NOT NULL,
    PRIMARY KEY (department_id)
);

See PRIMARY KEY constraints.

AUTO_INCREMENT Top ↑

For a generated numeric identifier, combine an integer key with AUTO_INCREMENT:

CREATE TABLE student (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class VARCHAR(20) NOT NULL,
    mark TINYINT UNSIGNED NOT NULL DEFAULT 0,
    gender VARCHAR(10) NOT NULL,
    PRIMARY KEY (id)
);

See MySQL AUTO_INCREMENT.

InnoDB and utf8mb4 Top ↑

For typical modern MySQL applications, InnoDB and utf8mb4 are good defaults:

CREATE TABLE messages (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    message VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

InnoDB supports transactions and foreign keys. utf8mb4 supports the full Unicode character range.

Check Whether the Table Exists Top ↑

Use SHOW TABLES to check whether a table exists in the current database:

SHOW TABLES LIKE 'sample_tb';

If a row is returned, a table or view with that matching name is visible to the current MySQL account.

Use INFORMATION_SCHEMA when more detail is needed Top ↑

SELECT TABLE_NAME,
       TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'sample_tb';

This lets you inspect metadata rather than relying only on a display command.

SHOW CREATE TABLE Top ↑

To inspect the table definition MySQL currently stores:

SHOW CREATE TABLE student;

This is useful for checking engine, columns, indexes, defaults, and table options. See copying tables and SHOW CREATE TABLE.

Create a Table with PHP PDO Top ↑

The SQL concept should come first. If an application genuinely needs to create a table at runtime, PDO can execute the DDL statement.

<?php
$sql="CREATE TABLE IF NOT EXISTS sample_tb (
    empno VARCHAR(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

try{
    $dbo->exec($sql);
    echo 'Table is ready.';
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to create the table.';
}

See PDO connection for the database connection setup.

Should You DROP the Table First? Top ↑

The old page suggested dropping a table before creating it again. That is appropriate only when deleting the existing structure and all of its data is genuinely intended.

DROP TABLE IF EXISTS sample_tb;

CREATE TABLE sample_tb (
    empno VARCHAR(6) NOT NULL
);

See DROP TABLE.

Common CREATE TABLE Mistakes Top ↑

Using old integer display widths as value limits Top ↑

INT(2) and INT(3) do not mean the column can store only two- or three-digit numbers. Choose the numeric datatype based on its range.

Mixing table and column character sets unnecessarily Top ↑

Use a consistent table-level utf8mb4 default unless a specific column genuinely requires another character set or collation.

Using contradictory old charset settings Top ↑

The previous example declared some columns as UTF-8 but set the table default to latin1. Keep character-set choices consistent.

Dropping a table just because it already exists Top ↑

DROP destroys data. Use IF NOT EXISTS when the goal is simply to avoid a duplicate-table error.

Assuming IF NOT EXISTS validates the schema Top ↑

It does not compare the existing table definition with the CREATE TABLE statement.

Using empty-string defaults without a data reason Top ↑

An empty string is a real value, not the same as unknown or absent data. Choose defaults deliberately.

Creating application tables dynamically for every request Top ↑

Permanent schema changes are usually deployment or migration operations, not routine request-time application behavior.

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: