🧱 Lesson 5: Data Types & Constraints

Why Data Types Matter

You might wonder: why not just store everything as text? Three reasons:

Think of data types like containers: you wouldn't pour soup into a paper bag or store a feather in a shipping container. The right type holds the right data efficiently.

💡 Rule of Thumb: Always pick the smallest type that can comfortably hold your data. Smaller types = less disk space, faster queries, and more rows in memory.

Numeric Types

MySQL has several numeric types, divided into integers (whole numbers) and decimals (numbers with fractional parts).

Integer Types

-- Integer examples
CREATE TABLE integer_demo (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    age         TINYINT UNSIGNED,         -- 0-255, perfect for ages
    year_born   SMALLINT,                 -- up to 32,767
    page_count  INT UNSIGNED,             -- no negative pages!
    tweet_id    BIGINT UNSIGNED           -- Twitter IDs are huge numbers
);
                

UNSIGNED — Positive Only

Adding UNSIGNED after an integer type means it only stores positive numbers (and zero), but doubles the maximum value. Use it when negative values don't make sense — ages, counts, page numbers, IDs.

-- SIGNED (default): -128 to 127
-- UNSIGNED:          0 to 255
age TINYINT UNSIGNED   -- Nobody has a negative age!
                

Decimal / Fractional Types

-- DECIMAL examples
price       DECIMAL(8, 2),    -- Up to 999,999.99
tax_rate    DECIMAL(5, 4),    -- Up to 9.9999 (e.g., 0.0825)
gpa         DECIMAL(3, 2),    -- Up to 9.99 (e.g., 3.85)
                

String Types

MySQL has many string types, but you'll use three most of the time: CHAR, VARCHAR, and TEXT.

CHAR vs. VARCHAR

The key difference is how they store data:

-- CHAR — fixed length, padded with spaces
country_code  CHAR(2),          -- 'US', 'PH', 'JP' — always 2 chars
state_code    CHAR(2),          -- 'NV', 'CA', 'TX'
md5_hash      CHAR(32),         -- MD5 hashes are always 32 hex characters

-- VARCHAR — variable length, stores only what's needed
first_name    VARCHAR(50),      -- 'Ray' stores as 3 + 1 bytes
email         VARCHAR(100),     -- 'ray@email.com' stores as 13 + 1 bytes
title         VARCHAR(200),     -- Book titles vary widely in length
                

TEXT Types

Use TEXT for long-form content that doesn't need a length limit:

-- TEXT for long content
bio           TEXT,               -- User biography
description   TEXT,               -- Product description
article_body  MEDIUMTEXT,         -- Full blog article
                

Date & Time Types

MySQL has dedicated types for dates and times — never store them as strings!

-- Date and time examples
CREATE TABLE events (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    event_name   VARCHAR(100) NOT NULL,
    event_date   DATE NOT NULL,              -- '2026-04-16'
    start_time   TIME,                       -- '14:30:00'
    created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                         ON UPDATE CURRENT_TIMESTAMP
);
                

DATETIME vs. TIMESTAMP

They look the same but behave differently:

created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP
                    

BOOLEAN, ENUM & SET

BOOLEAN

MySQL doesn't have a true boolean type — BOOLEAN is an alias for TINYINT(1):

-- These are equivalent:
is_active  BOOLEAN DEFAULT TRUE,
is_active  TINYINT(1) DEFAULT 1,
                

MySQL stores TRUE as 1 and FALSE as 0. You can use either in queries:

-- Both work:
SELECT * FROM books WHERE in_stock = TRUE;
SELECT * FROM books WHERE in_stock = 1;
                

ENUM — Pick One from a List

ENUM restricts a column to a predefined set of values. Only one value can be chosen:

CREATE TABLE shirts (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    size   ENUM('XS', 'S', 'M', 'L', 'XL', 'XXL') NOT NULL,
    color  ENUM('red', 'blue', 'green', 'black', 'white') NOT NULL
);

-- Valid:
INSERT INTO shirts (size, color) VALUES ('M', 'blue');

-- Invalid — MySQL will reject or warn:
INSERT INTO shirts (size, color) VALUES ('XXXL', 'purple');
                

SET — Pick Multiple from a List

SET is like ENUM but allows multiple values per row:

CREATE TABLE user_preferences (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50) NOT NULL,
    interests   SET('sports', 'music', 'tech', 'gaming', 'cooking')
);

-- One interest:
INSERT INTO user_preferences (username, interests)
VALUES ('alice', 'music');

-- Multiple interests (comma-separated, no spaces):
INSERT INTO user_preferences (username, interests)
VALUES ('bob', 'tech,gaming,music');
                

Constraints

Constraints are rules enforced by MySQL at the database level. They prevent invalid data from ever entering your tables — acting as a safety net regardless of which application or user is inserting data.

PRIMARY KEY

Every table should have a primary key — it uniquely identifies each row. A primary key is automatically NOT NULL and UNIQUE.

-- Column-level (most common for single-column keys)
CREATE TABLE products (
    id  INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Table-level (required for composite keys)
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
                

NOT NULL

Prevents a column from accepting NULL (empty/unknown) values. MySQL will reject any insert or update that tries to set a NOT NULL column to NULL.

-- first_name and email are required; city is optional
first_name  VARCHAR(50) NOT NULL,
email       VARCHAR(100) NOT NULL,
city        VARCHAR(50),              -- allows NULL (default behavior)
                

💡 Tip: Ask yourself: "Does this column always need a value for the row to make sense?" If yes, add NOT NULL. A customer without a name doesn't make sense — so first_name should be NOT NULL. A customer without a phone number might be fine — so phone can allow NULL.

UNIQUE

Ensures no two rows have the same value in a column. Unlike PRIMARY KEY, a table can have multiple UNIQUE columns, and UNIQUE columns can contain NULL values (but only one NULL per column).

-- Each customer must have a unique email
email       VARCHAR(100) NOT NULL UNIQUE,

-- ISBN numbers are unique across all books
isbn        VARCHAR(13) UNIQUE,

-- You can also name the constraint
CONSTRAINT uq_email UNIQUE (email)
                

DEFAULT

Provides a value when one isn't specified during an INSERT:

in_stock    BOOLEAN DEFAULT TRUE,
role        VARCHAR(20) DEFAULT 'customer',
created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
quantity    INT DEFAULT 0,
                

-- If we insert without specifying 'in_stock':
INSERT INTO books (title, author, price)
VALUES ('Dune', 'Frank Herbert', 12.99);

-- in_stock will automatically be TRUE (1)
                

AUTO_INCREMENT

Automatically generates sequential integer values (1, 2, 3, ...). Used almost exclusively for primary keys:

id  INT AUTO_INCREMENT PRIMARY KEY
                

Key behaviors:

• You don't include id in your INSERT — MySQL assigns it automatically
• If you delete row 5, the number 5 is not reused — the next insert gets 6
• You can check the current value: SELECT LAST_INSERT_ID();
• Only one AUTO_INCREMENT column per table, and it must be indexed (usually as PRIMARY KEY)

CHECK (MySQL 8.0.16+)

Enforces a custom condition on column values:

CREATE TABLE products (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    price    DECIMAL(8, 2) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,

    -- Prices must be positive
    CONSTRAINT chk_price CHECK (price > 0),

    -- Quantity can't be negative
    CONSTRAINT chk_quantity CHECK (quantity >= 0)
);

-- This will FAIL:
INSERT INTO products (name, price, quantity)
VALUES ('Widget', -5.00, 10);
-- ERROR: Check constraint 'chk_price' is violated.
                

-- More CHECK examples
age     TINYINT UNSIGNED CHECK (age >= 0 AND age <= 150),
rating  DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
status  VARCHAR(20) CHECK (status IN ('active', 'inactive', 'pending')),
                

FOREIGN KEY (Preview)

Foreign keys link a column in one table to the primary key of another table. This enforces referential integrity — you can't add an order for a customer that doesn't exist. We'll cover foreign keys in depth in Lesson 12.

-- Quick preview — full explanation in Lesson 12
CREATE TABLE orders (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_date   DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
                

Constraints Quick Reference

Choosing the Right Type — A Practical Guide

When designing a table, use this flowchart to pick the right data type:

Common Column Patterns

Here are types you'll use again and again in real projects:

Exercises

USE bookstore;

CREATE TABLE IF NOT EXISTS employees (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    hire_date   DATE NOT NULL,
    salary      DECIMAL(8, 2) NOT NULL,
    department  ENUM('sales', 'inventory', 'management', 'support'),
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT chk_salary CHECK (salary > 0)
);

DESCRIBE employees;
                        

CREATE TABLE Orders (
    OrderID VARCHAR(10),
    CustomerName VARCHAR(500),
    Price FLOAT,
    OrderDate VARCHAR(20),
    phone INT,
    Status VARCHAR(100)
);
                    

CREATE TABLE orders (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    customer_name  VARCHAR(100) NOT NULL,
    price          DECIMAL(10, 2) NOT NULL,
    order_date     DATETIME DEFAULT CURRENT_TIMESTAMP,
    phone          VARCHAR(20),
    status         ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
                       DEFAULT 'pending',

    CONSTRAINT chk_price CHECK (price > 0)
);
                        

Summary

📚 Additional Resources

• MySQL Docs — Data Types
• MySQL Docs — Integer Types
• MySQL Docs — DECIMAL and NUMERIC
• MySQL Docs — String Types
• MySQL Docs — CHECK Constraints

🚀 What's Next?

Now that you know what types of data go into tables and how to protect them with constraints, it's time to put data in and get it back out. In the next lesson, you'll learn the four fundamental operations every database developer uses daily — INSERT, SELECT, UPDATE, and DELETE — collectively known as CRUD.