Data Types and ConstraintsLesson 3.1

Choosing the right SQL data types for every column

integer types, DECIMAL vs FLOAT, VARCHAR vs CHAR, TEXT vs VARCHAR, DATE vs DATETIME vs TIMESTAMP, BOOLEAN storage, ENUM pros and cons

Why Data Types Matter

The type you assign a column controls storage size, sort order, index behavior, and what values the database will accept. A wrong type wastes space, silently truncates data, or causes incorrect comparisons.

Numbers

INT / BIGINT for whole numbers. Use BIGINT for IDs on tables that will grow large.
DECIMAL(p,s) for money. Never use FLOAT or DOUBLE for currency — they store approximations.

price DECIMAL(10, 2)  -- up to 99,999,999.99, exact
discount FLOAT        -- WRONG for money: 0.1 + 0.2 ≠ 0.3

Strings

VARCHAR(n) for variable-length text with a known maximum. Set n realistically — VARCHAR(255) for names, VARCHAR(1000) for descriptions.
CHAR(n) for fixed-length strings like country codes or status codes.
TEXT for long content (blog posts, comments) where length is unknown.

Dates and Times

DATE stores year-month-day only.
DATETIME stores date and time, no timezone.
TIMESTAMP stores UTC and converts to the server timezone. Use TIMESTAMP for created_at and updated_at columns.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP