On gists

Column check constraint

MySql MySql tricks MySql - advanced

examples.sql Raw #

-- CONSTRAINT [constraint_name] CHECK (expression) [ENFORCED | NOT ENFORCED]

/*
ENFORCED - MySQL aktivně kontroluje podmínku při INSERT/UPDATE
NOT ENFORCED - MySQL podmínku nekontroluje, slouží jen jako dokumentace/metadata
*/

-- Kontrola formátu emailu pomocí REGEXP
CONSTRAINT valid_email CHECK (email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')

-- Kontrola, že koncová data je později než počáteční
CONSTRAINT valid_date_range CHECK (end_date > start_date)

-- Kontrola že sloupec obsahuje pouze velká písmena
CONSTRAINT uppercase_only CHECK (column_name = UPPER(column_name))

-- Kontrola že věk je v rozumném rozsahu
CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)

-- Kombinace více podmínek pro plat
CONSTRAINT salary_rules CHECK (
    (role = 'Junior' AND salary BETWEEN 30000 AND 50000) OR
    (role = 'Senior' AND salary BETWEEN 60000 AND 120000)
)

-- Kontrola že skóre je buď NULL nebo v rozsahu 0-100
CONSTRAINT valid_score CHECK (score IS NULL OR (score >= 0 AND score <= 100))

-- Kontrola že status je jedna z povolených hodnot
CONSTRAINT valid_status CHECK (
    status IN ('pending', 'approved', 'rejected', 'on_hold')
)

-- Komplexní validace telefonního čísla
CONSTRAINT valid_phone CHECK (
    phone_number REGEXP '^\\+?[1-9][0-9]{7,14}$'
)

-- Kontrola že discount je menší než cena
CONSTRAINT valid_discount CHECK (discount < price)

-- Kontrola že součet procentuálních hodnot nepřesáhne 100
CONSTRAINT percentage_sum CHECK (
    value1 + value2 + value3 <= 100
)


-- Komplexní kontrola hesla (minimální délka, musí obsahovat číslo a velké písmeno)
CONSTRAINT password_strength CHECK (
    LENGTH(password) >= 8 
    AND password REGEXP '[0-9]'
    AND password REGEXP '[A-Z]'
    AND password != username
)

-- Kontrola věku na základě data narození s aktuálním datem
CONSTRAINT valid_birth_date CHECK (
    YEAR(birth_date) >= 1900
    AND birth_date <= CURRENT_DATE
    AND TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) <= 120
)

-- Složitá business logika s CASE
CONSTRAINT complex_pricing CHECK (
    CASE 
        WHEN customer_type = 'VIP' THEN price >= 1000 AND discount <= price * 0.3
        WHEN customer_type = 'Regular' THEN price >= 500 AND discount <= price * 0.1
        WHEN customer_type = 'New' THEN price >= 100 AND discount = 0
        ELSE price >= 0
    END
)

-- Kombinace podmínek pro rezervační systém
CONSTRAINT valid_reservation CHECK (
    (status = 'confirmed' AND payment_id IS NOT NULL AND amount > 0)
    OR (status = 'pending' AND (payment_id IS NULL OR amount = 0))
    OR (status = 'cancelled' AND cancellation_reason IS NOT NULL)
)

-- Kontrola formátu a validity dat
CONSTRAINT valid_document CHECK (
    (doc_type = 'invoice' AND doc_number REGEXP '^INV-[0-9]{6}$')
    OR (doc_type = 'order' AND doc_number REGEXP '^ORD-[0-9]{6}$')
    AND issue_date <= due_date
    AND YEAR(issue_date) >= 2020
)

-- Kontrola závislostí mezi více sloupci
CONSTRAINT project_status_rules CHECK (
    (status = 'completed' AND end_date IS NOT NULL AND progress = 100)
    OR (status = 'in_progress' AND end_date IS NULL AND progress BETWEEN 1 AND 99)
    OR (status = 'planned' AND end_date IS NULL AND progress = 0)
)

-- Sezónní ceník s různými pravidly
CONSTRAINT seasonal_pricing CHECK (
    CASE 
        WHEN MONTH(booking_date) IN (6,7,8) THEN price >= 1000  -- Letní sezóna
        WHEN MONTH(booking_date) IN (12,1,2) THEN price >= 800  -- Zimní sezóna
        WHEN DAYOFWEEK(booking_date) IN (1,7) THEN price >= 700 -- Víkendy
        ELSE price >= 500  -- Mimo sezónu
    END
)

-- Validace adresy
CONSTRAINT valid_address CHECK (
    LENGTH(street) >= 3
    AND LENGTH(city) >= 2
    AND postal_code REGEXP '^[0-9]{5}$'
    AND (
        country = 'CZ' AND phone REGEXP '^\\+420[0-9]{9}$'
        OR 
        country = 'SK' AND phone REGEXP '^\\+421[0-9]{9}$'
    )
)

-- Komplexní kontrola produktu
CONSTRAINT product_rules CHECK (
    CASE product_type
        WHEN 'physical' THEN 
            weight > 0 
            AND dimensions IS NOT NULL 
            AND digital_url IS NULL
        WHEN 'digital' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND digital_url IS NOT NULL
        WHEN 'service' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND duration > 0
    END
    AND (
        price > 0
        OR (price = 0 AND is_free_sample = TRUE)
    )
)