Skip to main content

Understanding Data Types and Constraints

Choosing the right data types and constraints keeps the marketplace database trustworthy. In this lesson we examine the columns that power our schema and discuss why each type and rule exists.

Learning Goals

  • Map real-world commerce concepts to SQL data types
  • Understand how PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK protect data
  • Practice applying constraints when extending the schema
  • Spot common pitfalls when selecting types

Review: Marketplace Table Definitions​

category
CREATE TABLE category (
  category_id   INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(80) NOT NULL UNIQUE
);
  • INT AUTO_INCREMENT PRIMARY KEY produces unique, index-friendly identifiers.
  • VARCHAR NOT NULL UNIQUE ensures distinct category names.
customer
CREATE TABLE customer (
  customer_id           INT AUTO_INCREMENT PRIMARY KEY,
  first_name            VARCHAR(80) NOT NULL,
  last_name             VARCHAR(80) NOT NULL,
  email                 VARCHAR(190) UNIQUE,
  preferred_category_id INT,
  signup_year           INT CHECK (signup_year >= 2000),
  city                  VARCHAR(80),
  FOREIGN KEY (preferred_category_id) REFERENCES category(category_id)
);
  • Names use VARCHAR; we allow NULL emails for customers who have not completed onboarding.
  • preferred_category_id can be NULL for shoppers without a stated preference, but when present it must match an existing category.
  • CHECK guards against accidentally entering impossible signup years.
product
CREATE TABLE product (
  product_id   INT AUTO_INCREMENT PRIMARY KEY,
  sku          VARCHAR(30) NOT NULL UNIQUE,
  product_name VARCHAR(150) NOT NULL,
  price        DECIMAL(10,2) NOT NULL CHECK (price > 0),
  stock_qty    INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
  category_id  INT NOT NULL,
  seller_id    INT,
  FOREIGN KEY (category_id) REFERENCES category(category_id),
  FOREIGN KEY (seller_id) REFERENCES seller(seller_id)
);
  • price uses DECIMAL with a CHECK constraint to prevent negative values.
  • sku is UNIQUE, preventing duplicate catalog entries.
order_item
CREATE TABLE order_item (
  order_item_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id      INT NOT NULL,
  product_id    INT NOT NULL,
  quantity      INT NOT NULL CHECK (quantity > 0),
  unit_price    DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
  discount      DECIMAL(10,2) DEFAULT 0 CHECK (discount >= 0),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  UNIQUE (order_id, product_id)
);
  • The composite UNIQUE constraint keeps us from inserting the same product into the same order twice.
  • discount allows NULL or zero for full-price items.

Type Selection Guidelines​

  • Identifiers: INT (or BIGINT for very large systems) pairs well with auto-incrementing keys.
  • Codes: Use VARCHAR with validation rules (CHECK (sku REGEXP '^[A-Z]{2}-[0-9]{4}$')) if your engine supports pattern checks.
  • Dates: Use DATE, DATETIME, or TIMESTAMP for time-aware data.
  • Booleans: MySQL treats BOOLEAN as an alias for TINYINT(1); use 0/1 or TRUE/FALSE values.
  • Monetary values: Use DECIMAL(precision, scale) to avoid floating point rounding.

Adding Constraints After the Fact​

Suppose we add a shipment table and later realize tracking numbers must be unique:

ALTER TABLE shipment
ADD CONSTRAINT tracking_unique UNIQUE (tracking_number);

Before running this statement, verify no duplicate tracking numbers exist:

SELECT tracking_number, COUNT(*)
FROM shipment
GROUP BY tracking_number
HAVING COUNT(*) > 1;

Guardrails with CHECK​

CHECK constraints help encode business rules that don't fit other constraint types.

Validate order status
ALTER TABLE orders
ADD CONSTRAINT status_allowed CHECK (
  status IN ('Pending', 'Paid', 'Shipped', 'Delivered', 'Cancelled')
);

MySQL enforces CHECK clauses in version 8.0.16 and later. For older versions, keep the rules in your application logic.

Common Mistakes​

  • Wrong size: Using TEXT for numeric data prevents range checks and sorting from working as expected.
  • Missing NOT NULL: Required columns (like product_name) should disallow empty values.
  • Skipping foreign keys: Without them, orphaned rows accumulate (e.g., order items pointing to deleted products).
  • Overly permissive codes: If sku isn't constrained, typos like EL-10O1 (letter O vs zero) slip through.
  • Poorly named constraints: Name important constraints (e.g., UNIQUE (order_id, product_id)) for easier debugging.

Summary​

  • Data types express how values are stored; choose ones that mirror the domain (integers for IDs, decimals for price, text for names).
  • Constraints protect data integrity: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK each cover a different rule.
  • Review the provided DDL whenever you extend the schema to keep consistency.

Quiz​

Show quiz

  1. Which constraint keeps the same product from appearing twice in the same order?
    A) PRIMARY KEY
    B) UNIQUE (order_id, product_id)
    C) CHECK (price > 0)
    D) FOREIGN KEY

  2. Why is preferred_category_id nullable in the customer table?
    A) Categories can be deleted
    B) Some customers may have no stated preference
    C) Foreign keys cannot be required
    D) It improves performance

  3. Which data type would you use for price in a database that supports decimals, and why?
    A) TEXT to allow any value
    B) DECIMAL so cents are stored accurately
    C) INTEGER because prices are whole numbers
    D) FLOAT for approximate precision

  4. True or False: CHECK (price > 0) runs every time a row is inserted or updated.
    A) True
    B) False

Answers

  1. B
  2. B
  3. B
  4. A