Data Types and Constraints

The right column type saves space and prevents nonsense. Constraints stop bad data before it reaches your application.

Numbers

TINYINT for small flags, INT for ids and counts, BIGINT for large ids, DECIMAL for money. Avoid FLOAT for anything financial.

Text

CHAR is fixed length, VARCHAR is variable up to a limit, TEXT is for long content. Use utf8mb4 throughout so names and emoji are safe.

CREATE TABLE profiles (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nickname VARCHAR(50) NOT NULL,
    bio      TEXT,
    country  CHAR(2),
    verified TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

Constraints

Constraints document and enforce rules. Foreign keys also prevent orphan rows.

CREATE TABLE enrolments (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    course_id  INT UNSIGNED NOT NULL,
    grade      CHAR(1),
    UNIQUE KEY uniq_enrolment (student_id, course_id),
    CONSTRAINT fk_enrol_student
        FOREIGN KEY (student_id) REFERENCES students(id)
        ON DELETE CASCADE,
    CONSTRAINT chk_grade
        CHECK (grade IN ('A', 'B', 'C', 'D', 'E', 'F'))
) ENGINE=InnoDB;

Practice

1. Choose types for a phone number, a price and a biography.
2. Add a CHECK that keeps age between 10 and 100.
3. Explain what ON DELETE RESTRICT does.

Hands-on exercise

Create a products table with:
1. id primary key.
2. sku CHAR(12) NOT NULL UNIQUE.
3. price DECIMAL(10,2) NOT NULL.
4. stock INT UNSIGNED NOT NULL DEFAULT 0.
5. CHECK that price is zero or more.

Recap

Types should match the data. Constraints keep invalid rows out, and foreign keys protect relationships.