Create MySQL Databases and Tables

What database and table creation defines

A MySQL schema groups objects such as tables and views. Creating a table defines the shape and rules for a set of records: each column gets a data type and nullability, while keys and constraints identify records and protect relationships. These definitions are part of the application’s data model, not just setup boilerplate. If the schema permits ambiguous or invalid values, every application has to compensate, and inconsistent clients can still write incorrect data.

Use a database name that identifies the application or domain, and choose a character set and collation deliberately. For new MySQL 8.0 installations, utf8mb4 supports Unicode text, including supplementary characters. A collation controls string comparison and ordering, including case and accent behavior. The exact available collations depend on the MySQL release. Pick one that matches the application’s language and comparison requirements, and use compatible settings across related text columns.

Create a schema and a useful table

Use CREATE DATABASE to create the schema, then select it with USE in an interactive session or qualify table names in scripts. Define columns explicitly rather than relying on implicit defaults. The example uses an unsigned auto-incrementing key, a required product name, an exact decimal price, and a timestamp default. InnoDB is specified because the table participates in transactional application behavior.

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE shop;

CREATE TABLE products (
  product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku VARCHAR(64) NOT NULL,
  name VARCHAR(160) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id),
  UNIQUE KEY uq_products_sku (sku),
  CONSTRAINT chk_products_price CHECK (price >= 0)
) ENGINE = InnoDB;

The unique key makes each SKU distinct, the primary key identifies each row, and the check constraint rejects negative prices on MySQL versions that enforce CHECK constraints. MySQL began enforcing CHECK constraints in 8.0.16; older releases could parse but ignore them. Verify the production version before relying on such rules. MySQL’s BOOLEAN is a synonym for TINYINT(1), so applications should still treat the column consistently as a true-or-false domain.

Keys, constraints, and sensible defaults

A primary key should uniquely and reliably identify a row. A natural identifier such as a username can change or have awkward length, so many schemas use a compact surrogate key and enforce business uniqueness separately. Use NOT NULL when a value is mandatory, and use a DEFAULT only when the default is genuinely correct for records that omit the value. A default timestamp can capture creation time; a default price of zero may instead hide missing data.

Foreign keys encode relationships. For example, an order’s customer identifier can reference a customer primary key. The referenced and referencing definitions must be compatible, and the related tables should use an engine that supports the required constraint. Choose deletion behavior explicitly. Restricting deletion is often safer for records that have financial or audit value; cascading deletion is appropriate only when dependent rows are truly owned by and disposable with the parent.

Understand InnoDB primary and secondary indexes

InnoDB stores each table's rows in its clustered index. When a table has a primary key, that primary key is the clustered index; you do not create an additional clustered index with a separate statement. An InnoDB table can have only one clustered row order. If no primary key exists, InnoDB uses the first suitable UNIQUE index whose key columns are all NOT NULL; if there is no such key, it creates a hidden row identifier. Prefer an explicit, stable primary key rather than relying on that fallback.

Secondary indexes are separate lookup structures. Their leaf entries include the table's primary-key value so InnoDB can find the full row. A very wide primary key therefore increases the storage footprint of every secondary index. A compact, immutable surrogate key is often a practical primary key, while business identifiers such as SKU or email are protected separately with UNIQUE constraints. The best choice still depends on the domain and access patterns.

A foreign key protects referential integrity; it is not itself a general performance guarantee. InnoDB needs an index for the referencing columns and creates one if a suitable index is missing. Define a named child-side index explicitly when it supports common queries, such as fetching one customer's recent orders. Put the equality-filter column first and the ordering column next when that matches the workload. Composite indexes follow a leftmost-prefix rule, so validate the proposed order with representative queries and EXPLAIN rather than indexing every column.

CREATE TABLE customers (
  customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(254) NOT NULL,
  PRIMARY KEY (customer_id),
  UNIQUE KEY uq_customers_email (email)
) ENGINE = InnoDB;

CREATE TABLE orders (
  order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  total DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id),
  KEY idx_orders_customer_created (customer_id, created_at),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE = InnoDB;

Here each primary key defines the table's clustered index, the unique email key prevents duplicate emails, and the named composite index supports customer-based lookups ordered by creation time while also beginning with the foreign-key column. The foreign key prevents an order from referring to a missing customer. A second index on customer_id alone would usually be redundant because it is already the leftmost prefix of the composite index; confirm with actual queries before adding it.

Review each proposed index against real reads and writes before keeping it: every additional index takes space and must be maintained during inserts, updates, and deletes. Compare the candidate access path with MySQL index-design guidance and use EXPLAIN to verify that the optimizer can use it.

Evolving a schema safely

Development tutorials often use CREATE TABLE IF NOT EXISTS for repeatable setup, but this statement does not compare the existing table definition with the new definition or migrate it. An old table can remain structurally wrong while the statement succeeds. For application and production changes, use ordered schema migrations, review the generated DDL, test it on a copy with realistic data, and consider lock duration and compatibility with running application versions.

MySQL DDL behavior depends on the statement and version. Many DDL statements cause implicit commits, so do not assume that wrapping arbitrary table changes in a transaction makes them reversible. Before destructive or large changes, make a verified backup and document recovery steps. Grant schema-changing privileges only to the migration or administration identity; the ordinary runtime account should generally not be able to alter tables.

Creation checklist

Before creating a table, write down what one row represents, which values identify it, what may be missing, and which relationships must remain valid. Choose appropriate data types, name keys and constraints clearly, and check character and time-zone assumptions. After creation, inspect the definition and test both valid and invalid inserts. This verifies that the schema behaves as intended rather than merely that the DDL ran successfully.

A carefully designed schema gives queries a reliable foundation and helps prevent invalid data at its source. Continue with MySQL data types, keys and constraints, and schema migrations.