How to Create a Table in MySQL (CREATE TABLE with Examples)

Applies to: MySQL 8.4.11 and MariaDB 11.4.13 (every example below was run on both)

A table is created with CREATE TABLE, followed by the table name and a list of columns and keys in brackets. You can run it in the mysql command-line client, or paste it into the SQL tab of phpMyAdmin.

1. Connect and choose the database

mysql -u appuser -p appdb

Or, once connected, USE appdb;. If you forget, MySQL answers ERROR 1046 (3D000): No database selected. To create a new database first: CREATE DATABASE shop;.

2. Create a simple table

CREATE TABLE customers (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email       VARCHAR(255) NOT NULL,
  full_name   VARCHAR(100) NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• INT UNSIGNED ... AUTO_INCREMENT with PRIMARY KEY (id): every row gets its own number automatically.
• VARCHAR(255): text up to 255 characters. NOT NULL makes the column required.
• UNIQUE KEY: no two customers can have the same email address.
• DEFAULT CURRENT_TIMESTAMP: filled in automatically when the row is inserted.
• ENGINE=InnoDB and utf8mb4: transactions, foreign keys and full Unicode (including emoji). Both are already the defaults, but writing them makes the script portable.

3. Check the result

This is the MySQL 8.4 output. MariaDB shows the same columns, written slightly differently (int(10) unsigned and current_timestamp()).

mysql> DESCRIBE customers;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int unsigned | NO   | PRI | NULL              | auto_increment    |
| email      | varchar(255) | NO   | UNI | NULL              |                   |
| full_name  | varchar(100) | NO   |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

SHOW TABLES; lists the tables, and SHOW CREATE TABLE customers\G prints the full statement, which is handy for copying a table to another server.

4. A table with a foreign key

CREATE TABLE orders (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  INT UNSIGNED NOT NULL,
  total        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status       ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new',
  ordered_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_status (status),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers (id) ON DELETE CASCADE
) ENGINE=InnoDB;

• DECIMAL(10,2) stores money exactly. Don’t use FLOAT for prices.
• KEY idx_status is an index that speeds up searches such as WHERE status = 'paid'.
• The foreign key only accepts customer numbers that exist. In our test, an order for customer 7 (who doesn’t exist) failed with ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails.
• ON DELETE CASCADE deletes a customer’s orders together with the customer. We deleted customer 1 and their order was gone too. Leave it out if orders must be kept.

Other useful forms

CREATE TABLE IF NOT EXISTS customers (...);                 -- no error if it exists
CREATE TABLE customers_copy LIKE customers;                  -- same structure, no rows
CREATE TABLE new_orders AS SELECT id, customer_id, total
  FROM orders WHERE status = 'new';                          -- structure and rows from a query

With IF NOT EXISTS, an existing table only produces a note (Note 1050 Table 'customers' already exists) and the table is not changed. Note that CREATE TABLE ... AS SELECT copies the data but not the keys: in our test, new_orders had no primary key and no AUTO_INCREMENT.

Common errors

• ERROR 1050 (42S01): Table 'customers' already exists: pick another name, add IF NOT EXISTS, or change the existing table with ALTER TABLE.
• ERROR 1064 (42000): You have an error in your SQL syntax: often a missing comma, or a reserved word used as a name. CREATE TABLE order (...) fails; CREATE TABLE `order` (...) with backticks works. More in MySQL error 1064.
• ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id' in foreign key constraint ... are incompatible: both columns must have the same type. Ours failed because one was INT and the other INT UNSIGNED.
• ERROR 1824 (HY000): Failed to open the referenced table 'nosuch': create the parent table first.
• On MariaDB, both foreign key mistakes above show up as ERROR 1005 (HY000): Can't create table `shop`.`bad_fk` (errno: 150 "Foreign key constraint is incorrectly formed"). Check the column types and that the parent table exists.
• ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'customers.uq_email': the unique key is doing its job.

MySQL and MariaDB differ in one default

The table statements worked on both, but the default collation differs: MySQL 8.4 used utf8mb4_0900_ai_ci and MariaDB 11.4 used utf8mb4_uca1400_ai_ci. Keep all tables of one application on the same collation. When we joined two tables with different collations, both servers refused with ERROR 1267 (HY000): Illegal mix of collations.

Related: install MySQL on Ubuntu, create tables in phpMyAdmin and run MySQL queries from the Linux command line.

Official documentation: MySQL 8.4: CREATE TABLE statement.