How to Create a Table in MySQL

Select a database, then run CREATE TABLE with a name, a list of columns and a primary key:

USE company;

CREATE TABLE departments (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_departments_name (name)
);

If you have not created the database yet, run CREATE DATABASE company; first. Without a selected database the statement fails with ERROR 1046 (3D000): No database selected; you can also write the name in full as company.departments.

Anatomy of a column definition

Each column is name type [NOT NULL] [DEFAULT value] [other attributes]. The second table in this example uses most of what you need day to day:

CREATE TABLE employees (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  department_id INT UNSIGNED    NOT NULL,
  email         VARCHAR(255)    NOT NULL,
  full_name     VARCHAR(200)    NOT NULL,
  salary        DECIMAL(10,2)   NOT NULL,
  hired_on      DATE            NOT NULL,
  is_active     BOOLEAN         NOT NULL DEFAULT TRUE,
  notes         TEXT            NULL,
  created_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_employees_email (email),
  KEY idx_employees_department (department_id),
  CONSTRAINT fk_employees_department
    FOREIGN KEY (department_id) REFERENCES departments (id),
  CONSTRAINT chk_employees_salary CHECK (salary >= 0)
);

• NOT NULL rejects missing values. Columns are nullable unless you say otherwise, so add it wherever a value is required.
• DEFAULT supplies a value when an INSERT omits the column.
• AUTO_INCREMENT generates the next integer for the key. A table can have one such column and it must be indexed.
• PRIMARY KEY identifies each row. InnoDB physically orders the table by it, and every secondary index stores a copy of it, so keep it short and stable. Always define one; replication and many tools depend on it.
• UNIQUE KEY prevents duplicates. KEY (or INDEX) is a plain index to speed up lookups.
• FOREIGN KEY requires every department_id to exist in departments. The referenced table has to exist first, which is why departments was created before employees.
• CHECK validates a row against an expression. MySQL enforces check constraints from 8.0.16 onwards; older versions parsed and ignored them.

Naming constraints and indexes yourself (uq_..., fk_..., chk_...) makes error messages readable and lets you drop them later by name.

Choosing data types

You are storing	Use	Notes
Whole numbers	INT, BIGINT; TINYINT/SMALLINT for small ranges	Add UNSIGNED for values that are never negative
Money and exact decimals	DECIMAL(p,s)	DECIMAL(10,2) holds up to 99999999.99. Do not use FLOAT or DOUBLE for money; they are approximate
Short text	VARCHAR(n)	n is a length in characters, not bytes
Long text	TEXT, MEDIUMTEXT, LONGTEXT	Cannot be fully indexed; an index needs a prefix length
True/false	BOOLEAN	A synonym for TINYINT(1); stores 0 and 1
Calendar date	DATE
Date and time	DATETIME or TIMESTAMP	TIMESTAMP is stored in UTC and converted to the session time zone, but only covers 1970 to January 2038. DATETIME stores what you give it and runs to the year 9999
Structured documents	JSON	Validated on insert
One value from a fixed list	ENUM('a','b')	Changing the list requires ALTER TABLE

Two habits carried over from old tutorials are worth dropping:

• INT(2) does not limit a column to two digits. The number in brackets was a display width, never a size or range. It is deprecated, and current versions do not show it in SHOW CREATE TABLE. Write INT.
• FLOAT(M,D) and ZEROFILL are deprecated as well.

Table options

Options go after the closing bracket:

CREATE TABLE audit_log (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event      VARCHAR(50) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='Append-only audit trail';

InnoDB is the default storage engine and the right choice unless you have a specific reason: it gives you transactions, row-level locking, foreign keys and crash recovery. If you leave out the character set and collation, the table inherits the database defaults.

Verifying the table

SHOW TABLES;
DESCRIBE departments;

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

SHOW CREATE TABLE employees\G prints the full definition as the server stored it, including the engine, character set, index names and constraints. Use it when you need to copy a table's structure or check what a migration actually produced.

Then confirm the constraints do their job:

INSERT INTO departments (name) VALUES ('Engineering');

INSERT INTO employees (department_id, email, full_name, salary, hired_on)
VALUES (1, 'priya@example.com', 'Priya Nair', 72000.00, '2026-03-02');

INSERT INTO employees (department_id, email, full_name, salary, hired_on)
VALUES (99, 'sam@example.com', 'Sam Okoro', 65000.00, '2026-03-09');

The last statement is rejected because department 99 does not exist:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`employees`, CONSTRAINT `fk_employees_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))

Other ways to create a table

-- Only if it is not already there (safe to re-run)
CREATE TABLE IF NOT EXISTS departments (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Same columns and indexes as an existing table, no rows
CREATE TABLE employees_archive LIKE employees;

-- From a query result
CREATE TABLE active_employees AS
SELECT id, email, full_name FROM employees WHERE is_active;

-- Visible only to this session, dropped when it disconnects
CREATE TEMPORARY TABLE tmp_ids (id BIGINT UNSIGNED PRIMARY KEY);

IF NOT EXISTS only checks the name. If a table with that name exists with a different structure, MySQL leaves it alone and raises a warning. CREATE TABLE ... LIKE copies indexes but not foreign keys. CREATE TABLE ... AS SELECT copies data and column types but no indexes and no primary key; add them afterwards with ALTER TABLE.

Common errors

Error	Cause and fix
ERROR 1050 (42S01): Table 'employees' already exists	Use IF NOT EXISTS, or drop the old table first if you mean to replace it.
ERROR 1064 (42000): You have an error in your SQL syntax ... near '...'	Often a trailing comma before the closing bracket, or a column named with a reserved word such as order, rank or groups. Rename it or quote it in backticks.
ERROR 1824 (HY000): Failed to open the referenced table 'departments'	The foreign key points at a table that does not exist yet. Create the parent first.
ERROR 3780 (HY000): Referencing column 'department_id' and referenced column 'id' in foreign key constraint ... are incompatible.	The two columns must have the same type, including UNSIGNED. INT cannot reference INT UNSIGNED.
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes	An index on a long utf8mb4 string. Shorten the column or index a prefix: KEY (title(191)).
ERROR 1142 (42000): CREATE command denied to user ...	The account lacks the CREATE privilege on this database. Foreign keys additionally need REFERENCES on the parent table.

Dropping a table

DROP TABLE IF EXISTS employees_archive, active_employees;

DROP TABLE removes the definition, the data and the indexes. It needs the DROP privilege, and like other DDL in MySQL it commits implicitly, so ROLLBACK will not bring the table back. The only undo is a backup.

A table that other tables reference cannot be dropped while those foreign keys exist:

DROP TABLE departments;

ERROR 3730 (HY000): Cannot drop table 'departments' referenced by a foreign key constraint 'fk_employees_department' on table 'employees'.

Drop the child table first (DROP TABLE employees, departments; in one statement also works), or remove the constraint with ALTER TABLE employees DROP FOREIGN KEY fk_employees_department;.

If you want to keep the table and remove only its rows, use TRUNCATE TABLE table_name; instead. It empties the table and resets AUTO_INCREMENT, and it also cannot be rolled back.

Further reading

• CREATE TABLE Statement
• Data Types
• FOREIGN KEY Constraints
• DROP TABLE Statement