Data Types and ConstraintsLesson 3.2

NOT NULL, UNIQUE, DEFAULT, and CHECK constraints

NOT NULL purpose, UNIQUE constraint, DEFAULT values, CHECK constraint syntax, constraint naming, column vs table-level constraints

Constraints Are Your First Line of Defense

Constraints enforce rules at the database engine level. They fire before data is written — even if the application has a bug. Think of them as the last safety net that cannot be bypassed.

NOT NULL

Prevents empty values. Add NOT NULL to every column that must always have a value. Do not default to allowing NULL — be deliberate.

UNIQUE

Ensures no two rows share the same value in a column. Often paired with NOT NULL for natural keys.

DEFAULT

Provides a fallback value when a column is omitted from an INSERT.

CHECK

Validates that a value meets a logical condition.

CREATE TABLE products (
  product_id   INT PRIMARY KEY AUTO_INCREMENT,
  name         VARCHAR(100) NOT NULL,
  sku          VARCHAR(50)  NOT NULL UNIQUE,
  price        DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock        INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
  status       VARCHAR(20) NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','inactive','discontinued'))
);

Name your constraints when you want clear error messages:

CONSTRAINT chk_price_positive CHECK (price >= 0),
CONSTRAINT chk_valid_status   CHECK (status IN ('active','inactive'))