How to fix ER_CHECK_CONSTRAINT_VIOLATED (3819): Check constraint validation failed in MySQL

-- Check table constraints
SHOW CREATE TABLE your_table_name;

-- Or query information_schema for CHECK constraints
SELECT 
    CONSTRAINT_NAME,
    CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'your_table_name';

-- Find rows that would violate constraints
-- Example: If constraint is "age >= 0 AND age <= 150"
SELECT * FROM your_table_name 
WHERE age < 0 OR age > 150;

-- For INSERT operations, examine the data you're trying to insert:
-- Check values against constraint conditions manually

-- Use EXPLAIN to see which part of a complex constraint fails
-- (Note: MySQL doesn't show which specific constraint failed in error message)

-- Update violating rows to satisfy constraints
-- Example: Fix age values outside valid range
UPDATE your_table_name
SET age = 
    CASE
        WHEN age < 0 THEN 0
        WHEN age > 150 THEN 150
        ELSE age
    END
WHERE age < 0 OR age > 150;

-- For NULL values in NOT NULL constraints with conditions:
UPDATE your_table_name
SET column_name = COALESCE(column_name, default_value)
WHERE column_name IS NULL 
    AND [constraint condition involving column_name];

-- Remove or quarantine severely invalid data
CREATE TABLE invalid_data_backup AS
SELECT * FROM your_table_name
WHERE [constraint violation condition];

DELETE FROM your_table_name
WHERE [constraint violation condition];

-- For bulk imports, clean data before insertion:
-- Use staging tables, validate with SELECT queries first
-- Apply data transformations to meet constraint requirements

-- Drop the existing constraint
ALTER TABLE your_table_name
DROP CONSTRAINT constraint_name;

-- Add a modified constraint
ALTER TABLE your_table_name
ADD CONSTRAINT constraint_name
CHECK (modified_condition);

-- Example: Relax age constraint
ALTER TABLE employees
ADD CONSTRAINT chk_age_reasonable
CHECK (age >= 16 AND age <= 100);  -- Was 0-150

-- Temporarily disable constraint checking (MySQL 8.0.16+)
SET @@session.check_constraint_checks = 0;

-- Perform data modifications
INSERT INTO your_table_name ...
UPDATE your_table_name ...

-- Re-enable constraint checking
SET @@session.check_constraint_checks = 1;

-- Verify no violations exist
SELECT * FROM your_table_name
WHERE NOT ([constraint condition]);

// JavaScript/Node.js example
function validateEmployeeData(employee) {
    const errors = [];
    
    // Check age constraint
    if (employee.age < 0 || employee.age > 150) {
        errors.push('Age must be between 0 and 150');
    }
    
    // Check salary constraint
    if (employee.salary < 0) {
        errors.push('Salary cannot be negative');
    }
    
    // Check email format constraint
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(employee.email)) {
        errors.push('Invalid email format');
    }
    
    return errors;
}

// Use before database operations
const validationErrors = validateEmployeeData(newEmployee);
if (validationErrors.length > 0) {
    // Show errors to user, don't attempt insert
    console.error('Validation failed:', validationErrors);
    return;
}
// Proceed with database insert

# Python example with SQLAlchemy
from sqlalchemy import CheckConstraint
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Employee(Base):
    __tablename__ = 'employees'
    
    id = Column(Integer, primary_key=True)
    age = Column(Integer)
    salary = Column(Numeric(10, 2))
    email = Column(String(255))
    
    __table_args__ = (
        CheckConstraint('age >= 0 AND age <= 150', name='chk_age'),
        CheckConstraint('salary >= 0', name='chk_salary'),
        CheckConstraint('email LIKE "%@%"', name='chk_email_format')
    )
    
    # Add validation method
    def validate(self):
        errors = []
        if not (0 <= self.age <= 150):
            errors.append('Age must be between 0 and 150')
        if self.salary < 0:
            errors.append('Salary cannot be negative')
        if '@' not in self.email:
            errors.append('Email must contain @')
        return errors

// Node.js with mysql2/promise
try {
    await connection.execute(
        'INSERT INTO employees (age, salary, email) VALUES (?, ?, ?)',
        [age, salary, email]
    );
} catch (error) {
    if (error.code === 'ER_CHECK_CONSTRAINT_VIOLATED') {
        // MySQL error code 3819
        console.error('Check constraint violation:', error.message);
        
        // Parse error message to identify constraint
        const constraintMatch = error.message.match(/constraint \'(.+?)\'/);
        if (constraintMatch) {
            const constraintName = constraintMatch[1];
            console.error(`Violated constraint: ${constraintName}`);
            
            // Provide user-friendly message based on constraint
            switch(constraintName) {
                case 'chk_age':
                    throw new Error('Age must be between 0 and 150');
                case 'chk_salary':
                    throw new Error('Salary cannot be negative');
                // ... other constraints
            }
        }
        
        // Fallback generic message
        throw new Error('Invalid data: does not meet business rules');
    }
    // Re-throw other errors
    throw error;
}

# Python with mysql-connector
try:
    cursor.execute(
        "INSERT INTO employees (age, salary, email) VALUES (%s, %s, %s)",
        (age, salary, email)
    )
    connection.commit()
except mysql.connector.Error as err:
    if err.errno == 3819:  # ER_CHECK_CONSTRAINT_VIOLATED
        print(f"Check constraint violation: {err.msg}")
        
        # Extract constraint name from error message
        import re
        match = re.search(r"constraint \'(.+?)\'", err.msg)
        if match:
            constraint_name = match.group(1)
            print(f"Violated constraint: {constraint_name}")
            
            # Map to user messages
            constraint_messages = {
                'chk_age': 'Age must be between 0 and 150',
                'chk_salary': 'Salary cannot be negative',
            }
            user_msg = constraint_messages.get(constraint_name, 'Invalid data')
            raise ValueError(user_msg) from err
    else:
        raise

-- Test constraints with sample data before applying to production
CREATE TABLE test_constraints LIKE your_table_name;

-- Copy constraints
ALTER TABLE test_constraints
ADD CONSTRAINT chk_test
CHECK (your_constraint_condition);

-- Test with valid data
INSERT INTO test_constraints (column1, column2)
VALUES (valid_value1, valid_value2);  -- Should succeed

-- Test with invalid data
INSERT INTO test_constraints (column1, column2)
VALUES (invalid_value1, invalid_value2);  -- Should fail with error 3819

-- Check constraint behavior with NULL values
-- Remember: NULL comparisons in CHECK constraints
-- NULL = NULL is NULL (not TRUE), so:
-- CHECK (column = value) fails if column IS NULL
-- Use: CHECK (column IS NULL OR column = value)

-- Review complex constraints for logical errors
-- Bad: CHECK (age > 18 AND age < 65)  -- What about age = 18 or 65?
-- Good: CHECK (age >= 18 AND age <= 65)

-- Test edge cases:
-- Minimum/maximum values
-- Boundary conditions
-- NULL handling
-- String patterns with LIKE/REGEXP
-- Date comparisons with different formats

-- Document constraints for team reference:
-- Constraint name, purpose, condition
-- Business rule being enforced
-- Example valid/invalid values