CREATE TABLE and Data Types

CREATE TABLE and Data Types

Talking to the Database

The CREATE TABLE statement defines a table, its columns, data types, and constraints. Choosing the right types and constraints keeps data correct and keeps queries fast.

CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email)
);

The id column is an auto-incrementing integer, so MySQL assigns the next number automatically. UNIQUE KEY uq_email prevents duplicate email addresses.

Choosing Data Types

MySQL offers a rich set of types, and the golden rule is to pick the smallest type that comfortably fits your data.

• Integer types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, from 1 to 8 bytes.
• Decimal types: DECIMAL(p, d) for money and exact values, FLOAT and DOUBLE for scientific numbers.
• String types: CHAR(n) for fixed-length text, VARCHAR(n) for variable text up to n characters.
• Date types: DATE for dates, TIME for times, DATETIME for both, TIMESTAMP for time-zone-aware stamps.

VARCHAR sizes are characters, not bytes, so VARCHAR(255) holds 255 characters regardless of the encoding. Do not give every string VARCHAR(1000) by habit; smaller columns use less space and let indexes stay compact and fast.

Column Constraints

Constraints protect data quality at the database level:

• NOT NULL rejects missing values.
• DEFAULT provides a value when none is given.
• UNIQUE forbids duplicates in the column.
• PRIMARY KEY is a UNIQUE NOT NULL identifier.
• CHECK (available in MySQL 8.0.16 and later) validates values.

CREATE TABLE products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    PRIMARY KEY (id)
);

Altering a Table

You can evolve a table without deleting it. ALTER TABLE adds, drops, or changes columns, and DROP TABLE removes the whole table permanently.

ALTER TABLE products ADD COLUMN description TEXT;

Key Points

• CREATE TABLE defines columns, types, and constraints in one statement.
• Pick the smallest data type that fits your data.
• Use DECIMAL for money and VARCHAR for flexible text.
• Constraints such as NOT NULL, UNIQUE, and CHECK protect data quality.
• ALTER TABLE evolves a schema without dropping the table.