MySQL Create Table

Last updated: October 19, 2026Oct 19, 2026 6 min read

Tables hold all the data in a MySQL database. The CREATE TABLE statement defines a new table: its name, its columns and their data types, and the rules (constraints) that keep the data valid. A well-designed table saves you from many bugs later.

1. Syntax

syntax.sql

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraints],
    column_name data_type [column_constraints],
    ...,
    [table_constraints]
) [table_options];

Figure 1: a table definition lists columns (name, type, column constraints), then table-level constraints and options.

2. Creating Your First Table

A simple table of departments:

departments.sql

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(40) NOT NULL,
  location  VARCHAR(40)
);

Output

Query OK, 0 rows affected

Check the structure with DESCRIBE:

query.sql

DESCRIBE departments;

Result: 3 rows

Field	Type	Null	Key	Default
dept_id	int	NO	PRI	NULL
dept_name	varchar(40)	NO		NULL
location	varchar(40)	YES		NULL

3. Column Definitions

Option	Meaning	Example
Data type	What kind of value the column holds	`INT`, `VARCHAR(60)`, `DATE`, `DECIMAL(10,2)`
`NOT NULL`	A value must always be supplied	`full_name VARCHAR(60) NOT NULL`
`DEFAULT`	Value used when none is given	`status VARCHAR(10) DEFAULT 'active'`
`AUTO_INCREMENT`	MySQL generates 1, 2, 3, …	`emp_id INT AUTO_INCREMENT`
`PRIMARY KEY`	Unique, non-null row identifier	`emp_id INT PRIMARY KEY`
`UNIQUE`	No two rows may share the value	`email VARCHAR(100) UNIQUE`
`CHECK (…)`	A condition every row must meet	`CHECK (salary > 0)`
`COMMENT`	A note stored with the column	`COMMENT 'gross monthly pay'`

See MySQL Data Types to choose types.

4. A Complete Example with Constraints

The employees table from Figure 1, linked to departments:

employees.sql

CREATE TABLE employees (
  emp_id     INT AUTO_INCREMENT PRIMARY KEY,
  full_name  VARCHAR(60)   NOT NULL,
  email      VARCHAR(100)  UNIQUE,
  salary     DECIMAL(10,2) CHECK (salary > 0),
  hired_on   DATE          DEFAULT (CURRENT_DATE),
  status     ENUM('active', 'on_leave', 'left') NOT NULL DEFAULT 'active',
  dept_id    INT,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE = InnoDB COMMENT = 'Company staff';

INSERT INTO departments VALUES (1, 'Engineering', 'Bengaluru'), (2, 'Finance', 'Mumbai');
INSERT INTO employees (full_name, email, salary, dept_id) VALUES
('Kiran Rao',   'kiran@corp.example',  85000, 1),
('Leela Menon', 'leela@corp.example',  72000, 2);

SELECT emp_id, full_name, salary, status, dept_id FROM employees;

Result: 2 rows

emp_id	full_name	salary	status	dept_id
1	Kiran Rao	85000.00	active	1
2	Leela Menon	72000.00	active	2

AUTO_INCREMENT numbered the rows and status took its default. Now see each constraint protect the table:

constraint-errors.sql

INSERT INTO employees (full_name, email, salary, dept_id) VALUES ('Duplicate', 'kiran@corp.example', 50000, 1);
INSERT INTO employees (full_name, salary, dept_id) VALUES ('No Pay', -10, 1);
INSERT INTO employees (full_name, salary, dept_id) VALUES ('Ghost Dept', 40000, 9);
INSERT INTO employees (email, salary) VALUES ('noname@corp.example', 40000);

Output

ERROR 1062 (23000): Duplicate entry 'kiran@corp.example' for key 'employees.email'
ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))
ERROR 1364 (HY000): Field 'full_name' doesn't have a default value

The DEFAULT (CURRENT_DATE) syntax in parentheses is an expression default, allowed since MySQL 8.0.13.

5. Composite Primary Keys

A primary key can span several columns. A timesheet records one row per employee per day:

composite.sql

CREATE TABLE timesheets (
  emp_id    INT,
  work_date DATE,
  hours     DECIMAL(4,2) NOT NULL,
  PRIMARY KEY (emp_id, work_date),
  FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
INSERT INTO timesheets VALUES (1, '2026-03-02', 8), (1, '2026-03-03', 7.5), (2, '2026-03-02', 8);
INSERT INTO timesheets VALUES (1, '2026-03-02', 4);   -- same employee, same day

Output

Query OK, 0 rows affected
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry '1-2026-03-02' for key 'timesheets.PRIMARY'

6. IF NOT EXISTS

query.sql

CREATE TABLE departments (id INT);

Output

ERROR 1050 (42S01): Table 'departments' already exists

query.sql

CREATE TABLE IF NOT EXISTS departments (id INT);
SHOW WARNINGS;

Output

Query OK, 0 rows affected, 1 warning

Result: 1 row

Level	Code	Message
Note	1050	Table 'departments' already exists

IF NOT EXISTS doesn't compare structures. If a table with that name exists but has different columns, MySQL keeps the old table unchanged.

7. Copying a Structure: CREATE TABLE … LIKE

LIKE creates an empty table with the same columns, indexes and constraints (except foreign keys):

like.sql

CREATE TABLE employees_archive LIKE employees;
SELECT COUNT(*) AS rows_copied FROM employees_archive;

Result: 1 row

rows_copied
0

8. Creating from a Query: CREATE TABLE … AS SELECT

Create a table and fill it from a query in one step. Column types come from the query; keys and indexes are not copied:

as-select.sql

CREATE TABLE dept_payroll AS
SELECT d.dept_name, COUNT(*) AS staff, SUM(e.salary) AS monthly_cost
FROM employees e JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

SELECT * FROM dept_payroll;

Result: 2 rows

dept_name	staff	monthly_cost
Engineering	1	85000.00
Finance	1	72000.00

See MySQL Copy Table for more ways to copy tables.

9. Viewing the Full Definition

query.sql

SHOW CREATE TABLE departments;

Result: 1 row

Table	Create Table
departments	CREATE TABLE `departments` ( `dept_id` int NOT NULL, `dept_name` varchar(40) NOT NULL, `location` varchar(40) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

MySQL fills in everything you left out: engine, character set and collation.

10. Naming and Design Tips

Use lowercase names with underscores: order_items, not OrderItems or order-items.
Pick a naming style for tables (plural employees or singular employee) and keep to it.
Give every table a primary key; InnoDB stores rows in primary-key order.
Use NOT NULL wherever a value is always expected.
Use DECIMAL for money, DATE/DATETIME for dates, and the smallest integer type that fits.
Avoid reserved words like order, group and key as names.

11. Practice Exercises

Exercise 1: Create a projects table with an auto-increment ID, a required unique name, a budget that must be positive and a start date.

solution.sql

CREATE TABLE projects (
  project_id INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(80) NOT NULL UNIQUE,
  budget     DECIMAL(12,2) CHECK (budget > 0),
  start_date DATE
);
DESCRIBE projects;

Result: 4 rows

Field	Type	Null	Key	Extra
project_id	int	NO	PRI	auto_increment
name	varchar(80)	NO	UNI
budget	decimal(12,2)	YES
start_date	date	YES

Exercise 2: Create project_members linking projects and employees, so an employee can join a project only once.

solution.sql

CREATE TABLE project_members (
  project_id INT,
  emp_id     INT,
  role       VARCHAR(30),
  PRIMARY KEY (project_id, emp_id),
  FOREIGN KEY (project_id) REFERENCES projects(project_id),
  FOREIGN KEY (emp_id)     REFERENCES employees(emp_id)
);
SHOW TABLES;

Result: 7 rows

Tables_in_company
departments
dept_payroll
employees
employees_archive
project_members
projects
timesheets

12. Summary

CREATE TABLE name (column type constraints, …); defines a new table.
Constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY, DEFAULT) protect your data.
AUTO_INCREMENT generates IDs; composite keys span several columns.
IF NOT EXISTS avoids errors; LIKE copies structure; AS SELECT copies data from a query.
Check results with DESCRIBE and SHOW CREATE TABLE.

Was this tutorial helpful?

Help Us Improve

MySQL Create Table

1. Syntax

2. Creating Your First Table

3. Column Definitions

4. A Complete Example with Constraints

5. Composite Primary Keys

6. IF NOT EXISTS

7. Copying a Structure: CREATE TABLE … LIKE

8. Creating from a Query: CREATE TABLE … AS SELECT

9. Viewing the Full Definition

10. Naming and Design Tips

11. Practice Exercises

12. Summary