MySQL CREATE TABLE

Once you have a database selected, the next step is to create tables inside it. A table is where your actual data lives — it is a structured grid of rows and columns, similar to a spreadsheet. The CREATE TABLE statement defines the table name, its columns, and the rules each column must follow.

---

Syntax

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

Each line inside the parentheses defines one column. You give it a name, a data type (what kind of data it holds), and optional constraints (rules it must follow).

---

A Simple Example

Let's create a students table inside the school database:

USE school;

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    age INT,
    city VARCHAR(80),
    joined_at DATE,
    PRIMARY KEY (id)
);

Here is what each part means:

Column	Data Type	Constraint / Note
id	INT	Auto-incrementing primary key — unique identifier for each row
name	VARCHAR(100)	Text up to 100 characters, cannot be empty
email	VARCHAR(150)	Text up to 150 characters, must be unique across all rows
age	INT	Whole number, optional (NULL allowed)
city	VARCHAR(80)	Text up to 80 characters, optional
joined_at	DATE	Stores a date in YYYY-MM-DD format

---

Using IF NOT EXISTS

Just like with databases, running CREATE TABLE when the table already exists will cause an error. Use IF NOT EXISTS to avoid this:

CREATE TABLE IF NOT EXISTS students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

---

Common Data Types

Choosing the right data type for each column is important — it controls what values are accepted and how efficiently data is stored. Here are the most commonly used types:

Data Type	Description	Example
INT	Whole numbers	1, 42, -10
VARCHAR(n)	Variable-length text up to n characters	'Alice', 'Dhaka'
TEXT	Long text with no fixed size limit	Article body, descriptions
DATE	Date only (YYYY-MM-DD)	2025-04-30
DATETIME	Date and time (YYYY-MM-DD HH:MM:SS)	2025-04-30 14:30:00
DECIMAL(p,s)	Exact decimal number	99.95, 1234.56
BOOLEAN	True (1) or False (0)	1, 0

---

Common Constraints

Constraints are rules applied to columns to control the data they accept:

Constraint	What it does
NOT NULL	Column must always have a value — empty/NULL not allowed
UNIQUE	Every value in this column must be different across all rows
PRIMARY KEY	Uniquely identifies each row; combines NOT NULL and UNIQUE
AUTO_INCREMENT	Automatically assigns the next integer value when a row is inserted
DEFAULT value	Assigns a default value if none is provided on insert

---

Create Table with DEFAULT Values

You can set a default value for a column so that if no value is provided during an insert, MySQL uses the default automatically:

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    product VARCHAR(120) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

---

Key Points to Remember

• CREATE TABLE name ( columns... ); defines a new table inside the active database.
• Each column needs a name, a data type, and optionally constraints.
• Always define a PRIMARY KEY — it uniquely identifies every row.
• Use AUTO_INCREMENT on an integer primary key to let MySQL assign IDs automatically.
• Use IF NOT EXISTS in scripts to prevent errors on re-run.
• Pick the smallest appropriate data type — it saves storage and improves performance.