Constraints: letting the database enforce the rules

Key points

• Application validation runs in one place; a constraint runs for every writer - the batch job, the migration script, the person on a console. Only one of those is enforceable.
• NOT NULL is the cheapest and most valuable. It also helps the optimiser, which can skip null handling entirely for that column.
• A UNIQUE constraint creates an index, so it costs write time. It also gives you an atomic upsert via INSERT ... ON DUPLICATE KEY UPDATE without a read-then-write race.
• Foreign keys prevent orphan rows. They add a check per write and take locks on the referenced row, which is why very high-write systems sometimes drop them - a deliberate trade, not a default.
• CHECK constraints are enforced from MySQL 8.0.16 - before that they were parsed and ignored, so an older schema may contain checks that never did anything.
• Choose ON DELETE behaviour explicitly. RESTRICT is the safe default; CASCADE is convenient and can silently delete far more than intended.

Example

CREATE TABLE orders (
    id           BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(32)  NOT NULL,
    customer_id  BIGINT       NOT NULL,
    status       VARCHAR(20)  NOT NULL DEFAULT 'NEW',
    total        DECIMAL(12,2) NOT NULL,        -- never FLOAT for money
    created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Uniqueness the application cannot violate, from any code path.
    CONSTRAINT uq_order_number UNIQUE (order_number),

    -- No order without a real customer.
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE RESTRICT          -- refuse to orphan; CASCADE deletes orders
        ON UPDATE CASCADE,

    -- Enforced from 8.0.16. Silently ignored before that.
    CONSTRAINT ck_total_non_negative CHECK (total >= 0),
    CONSTRAINT ck_status CHECK (status IN ('NEW','PAID','SHIPPED','CANCELLED'))
) ENGINE=InnoDB;

-- The unique constraint gives an atomic upsert - no read-then-write race.
INSERT INTO daily_counts (day, hits) VALUES (CURRENT_DATE, 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

-- DECIMAL vs FLOAT for money - this is not a style preference:
SELECT 0.1 + 0.2 = 0.3;                              -- 0  with FLOAT
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2))
       = CAST(0.3 AS DECIMAL(10,2));                 -- 1  with DECIMAL

-- Adding a constraint to a table that already has bad data:
ALTER TABLE orders ADD CONSTRAINT ck_total CHECK (total >= 0);
--   fails if any existing row violates it. Find them first:
SELECT COUNT(*) FROM orders WHERE total < 0;

A constraint enforces the rule for every writer, including the ones you did not write - and money is DECIMAL, never FLOAT.