MySQL CREATE TABLE

A table is where data is actually stored in a MySQL database. A table consists of columns (which define the structure) and rows (which hold the actual data). The CREATE TABLE statement is used to define a new table, its columns, and the data types for each column.

Syntax

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

• table_name — The name of the new table.
• column1, column2 — The column names.
• datatype — The data type for each column (e.g., INT, VARCHAR, DATE).
• constraints — Optional rules such as NOT NULL, UNIQUE, or PRIMARY KEY.

Example: Creating a Simple Table

The following statement creates a table named students:

CREATE TABLE students (
    student_id   INT,
    first_name   VARCHAR(50),
    last_name    VARCHAR(50),
    age          TINYINT,
    email        VARCHAR(100),
    enrolled_on  DATE
);

This creates a table with six columns. No data is stored yet — this only defines the structure.

Verifying the Table Was Created

To see all tables in the current database, run:

SHOW TABLES;

To see the structure of a specific table, use:

DESCRIBE students;

Or the shorter version:

DESC students;

Sample output:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| student_id  | int          | YES  |     | NULL    |       |
| first_name  | varchar(50)  | YES  |     | NULL    |       |
| last_name   | varchar(50)  | YES  |     | NULL    |       |
| age         | tinyint      | YES  |     | NULL    |       |
| email       | varchar(100) | YES  |     | NULL    |       |
| enrolled_on | date         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Creating a Table with Constraints

Constraints enforce rules on the data that can be entered into a column. Commonly used constraints include:

• NOT NULL — The column cannot be empty.
• UNIQUE — All values in the column must be different.
• PRIMARY KEY — Uniquely identifies each row; combines NOT NULL and UNIQUE.
• DEFAULT — Sets a default value when none is provided.

CREATE TABLE students (
    student_id   INT          NOT NULL,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    age          TINYINT,
    email        VARCHAR(100) UNIQUE,
    enrolled_on  DATE         DEFAULT (CURRENT_DATE),
    PRIMARY KEY (student_id)
);

In this version:

• student_id is the primary key — it uniquely identifies each student.
• first_name and last_name cannot be left empty.
• email must be unique — no two students can share the same email.
• enrolled_on defaults to today's date if no value is provided.

Creating a Table Only if It Does Not Exist

To avoid an error when the table already exists, use IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS students (
    student_id  INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL
);

Example: Creating a Products Table

CREATE TABLE products (
    product_id   INT          NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(8,2) NOT NULL,
    stock        INT          DEFAULT 0,
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id)
);

Here, AUTO_INCREMENT automatically assigns a new unique number to product_id whenever a new row is inserted. CURRENT_TIMESTAMP automatically stores the current date and time when a row is created.

Key Points

• The CREATE TABLE statement defines the structure of a new table.
• Each column requires a name and a data type.
• Constraints like NOT NULL, UNIQUE, and PRIMARY KEY enforce data integrity.
• Use IF NOT EXISTS to prevent errors if the table already exists.
• Use DESCRIBE table_name; to view the structure of an existing table.