FOUNDATIONBEGINNERlesson-03

Data Types & Constraints

🕑 28 min

📃 5 sections

🎓 Foundation

The data types you choose when designing a table affect storage size, query performance, and data integrity. Choosing VARCHAR(255) for every column wastes space. Choosing INT for a phone number loses leading zeros. This lesson teaches you the right type for every situation and how constraints enforce data rules automatically.

Section 1

Numeric Types

Choose the right numeric type to balance precision and storage efficiency.

🔢

INT

Whole numbers from -2 billion to 2 billion

Whole Numbers

📊

BIGINT

Very large integers — user IDs, timestamps

Large Range

🔠

SMALLINT

Small integers from -32,768 to 32,767

Compact

💵

DECIMAL(p,s)

Exact decimals — always use for money and prices

Exact

🧬

FLOAT

Approximate decimals — scientific calculations

Approximate

✅

BOOLEAN

True/false stored as TINYINT(1) — 0 or 1

True/False

Section 2

String Types

SQL

-- CHAR: fixed-length (padded with spaces)
country_code CHAR(2)        -- always exactly 2 chars: 'PK', 'US'

-- VARCHAR: variable-length (efficient)
name VARCHAR(100)           -- stores only actual length + 1 byte

-- TEXT: for long content (up to 65KB)
description TEXT            -- blog posts, comments, notes

-- ENUM: one value from a predefined list
status ENUM('active','inactive','pending')

-- Example table
CREATE TABLE products (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  code     CHAR(8)   NOT NULL,
  name     VARCHAR(150) NOT NULL,
  desc_    TEXT,
  status   ENUM('available','out_of_stock','discontinued') DEFAULT 'available'
);

Section 3

Date/Time Types

SQL

-- DATE: stores date only (YYYY-MM-DD)
birthday DATE               -- '2006-03-15'

-- DATETIME: date + time (YYYY-MM-DD HH:MM:SS)
appointment DATETIME        -- '2026-06-18 14:30:00'

-- TIMESTAMP: like DATETIME but auto-converts timezone
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- YEAR: just the year
enrollment_year YEAR        -- 2026

-- Extract parts
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM orders;
SELECT DATEDIFF('2026-12-31', '2026-06-18') AS days_remaining;

Section 4

Constraints

SQL

CREATE TABLE orders (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total       DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR(20) DEFAULT 'pending',
  order_date  DATE NOT NULL,
  UNIQUE (customer_id, order_date),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

PRIMARY KEY — uniquely identifies each row, cannot be NULL
FOREIGN KEY — links to the primary key of another table
UNIQUE — no two rows can have the same value in this column
NOT NULL — column must always have a value
DEFAULT — value used when none is provided on INSERT
CHECK — enforces a custom condition (e.g. price must be positive)

Section 5

ALTER TABLE

SQL

-- Add a new column
ALTER TABLE students ADD COLUMN phone VARCHAR(20);

-- Modify a column type/constraint
ALTER TABLE students MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Rename a column (MySQL 8.0+)
ALTER TABLE students RENAME COLUMN phone TO phone_number;

-- Drop a column
ALTER TABLE students DROP COLUMN phone_number;

-- Add a constraint after creation
ALTER TABLE students ADD UNIQUE (email);

🎤 Knowledge Check

1. Which type stores exact monetary values?

DECIMAL(10,2) stores exact values — critical for money where 0.1 + 0.2 must equal exactly 0.3, not 0.30000000004.

2. CHAR(5) vs VARCHAR(5) — which uses less storage for short strings?

VARCHAR uses less storage for short strings — it stores only the actual length. CHAR always pads to the full length with spaces.

3. Which constraint links two tables together?

FOREIGN KEY creates a relationship between tables — it says "this column's value must exist as a PRIMARY KEY in that other table."

4. What does ON DELETE CASCADE do?

ON DELETE CASCADE automatically deletes all child rows when the parent row is deleted, preventing orphaned records.

5. How do you add a column after a table is created?

ALTER TABLE is the DDL command for modifying an existing table's structure — ADD COLUMN, DROP COLUMN, MODIFY COLUMN, etc.