Creating Tables and Data Types

Introduction

Tables define structure—column names, types, and rules—before you insert rows. This chapter covers CREATE TABLE, common MySQL data types, ALTER TABLE changes, and comments so your schemas stay readable and compatible with web apps.

Prerequisites

Database Management Basics
Database selected: USE blog_dev; (or create it from previous chapter)

CREATE TABLE

Users table example:

sql

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Application users';

Code explanation:

AUTO_INCREMENT — MySQL assigns next id for new rows
PRIMARY KEY (id) — unique row identifier; clustered in InnoDB
UNIQUE KEY — no duplicate emails
ENGINE=InnoDB — transactional storage (default in MySQL 8)
Table/column COMMENT documents intent in metadata

Verify:

sql

SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users\G

Common Data Types

Numeric

Type	Use
`INT` / `BIGINT`	IDs, counts
`DECIMAL(10,2)`	Money (exact)
`FLOAT` / `DOUBLE`	Approximate science metrics

Prefer DECIMAL for currency—not FLOAT.

Strings

Type	Use
`VARCHAR(n)`	Variable text up to n chars (email, name)
`CHAR(n)`	Fixed width (country code `CHAR(2)`)
`TEXT`	Long article body

Date and time

Type	Use
`DATE`	Birth date
`DATETIME`	Event timestamp (no timezone)
`TIMESTAMP`	Auto `ON UPDATE` patterns (mind timezone)

JSON (MySQL 8)

sql

settings JSON NULL

Store flexible key/value—indexed generated columns later in advanced chapter.

Boolean style

MySQL uses TINYINT(1) for true/false—ORMs map to boolean.

ALTER TABLE

Add column:

sql

ALTER TABLE users
  ADD COLUMN last_login_at DATETIME NULL AFTER created_at;

Modify column:

sql

ALTER TABLE users
  MODIFY COLUMN display_name VARCHAR(150) NOT NULL;

Drop column:

sql

ALTER TABLE users
  DROP COLUMN last_login_at;

Rename table:

sql

RENAME TABLE users TO app_users;
RENAME TABLE app_users TO users;

Code explanation:

Large tables: some ALTER operations rebuild table—plan maintenance window in production

DROP and TRUNCATE

Remove table structure and data:

sql

DROP TABLE IF EXISTS temp_import;

Remove all rows, keep structure:

sql

TRUNCATE TABLE temp_import;

TRUNCATE is fast reset—cannot use with foreign key references in some cases.

Posts Table Exercise

sql

CREATE TABLE posts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  body TEXT NOT NULL,
  published_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_posts_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Foreign keys come in Constraints chapter—index on user_id prepares joins.

Naming Conventions

Item	Convention
Tables	plural snake_case: `order_items`
Columns	snake_case: `created_at`
Primary key	`id`
Indexes	`idx_table_column`, `uk_table_column`

FAQ

VARCHAR max length?

Up to 65,535 bytes subject to row size—255/500 common for strings.

INT vs BIGINT for id?

INT billions of rows—use BIGINT for high-growth tables early.

TEXT in index?

Need prefix index or generated column—full TEXT index not direct.

MyISAM vs InnoDB?

Use InnoDB only unless legacy reason—transactions and FK require it.

NULL vs NOT NULL?

Prefer NOT NULL with sensible DEFAULT when possible—simplifies queries.

Copy table structure?

CREATE TABLE copy LIKE original; — data separate INSERT ... SELECT.