📋

Description

This error indicates that a `CHECK` constraint expression on a table contains a function that MySQL explicitly disallows. It typically occurs during table creation or alteration when the function is non-deterministic, has side effects, or attempts to access data beyond the current row.

💬

Error Message

An expression of a check constraint '%s' contains disallowed function.

🔍

Known Causes

4 known causes

⚠️

Using Non-Deterministic Functions

The `CHECK` constraint includes a function (e.g., `NOW()`, `RAND()`) whose output is not constant or predictable, making the constraint non-deterministic.

⚠️

Functions with Side Effects

The constraint attempts to use a function (e.g., a stored function) that modifies data or performs other actions beyond simply returning a value.

⚠️

Referencing External Data

The function within the `CHECK` constraint tries to access data from other rows or tables, which is beyond the scope of a row-level `CHECK` constraint.

⚠️

Improperly Defined Stored Functions

A user-defined or stored function used lacks the `DETERMINISTIC` characteristic or violates other requirements for use in `CHECK` constraints.

🛠️

Solutions

3 solutions available

1. Remove Disallowed Function from Check Constraint easy

Identify and remove the specific function causing the error from your CHECK constraint definition.

1

Identify the problematic CHECK constraint. The error message usually provides the name of the constraint (e.g., '%s').

2

Examine the definition of the identified CHECK constraint in your table schema.

SHOW CREATE TABLE your_table_name;

3

Locate the function within the constraint's expression that is not permitted by MySQL's CHECK constraint rules. Common disallowed functions include user-defined functions, stored procedures, non-deterministic functions (like NOW(), RAND()), and functions that access external resources.

4

Modify the CHECK constraint by removing or replacing the disallowed function with an equivalent, permitted expression. If the function was intended to validate a specific condition, find a way to express that condition using only scalar functions, arithmetic operators, or comparisons.

ALTER TABLE your_table_name DROP CONSTRAINT your_check_constraint_name;
ALTER TABLE your_table_name ADD CONSTRAINT your_check_constraint_name CHECK (your_modified_expression);

2. Replace Disallowed Function with a Trigger medium

Replicate the validation logic of the disallowed function using a stored trigger.

1

Identify the CHECK constraint and the disallowed function causing the error, as described in the previous solution.

2

Remove the problematic CHECK constraint from the table.

ALTER TABLE your_table_name DROP CONSTRAINT your_check_constraint_name;

3

Create a BEFORE INSERT and/or BEFORE UPDATE trigger on the table. This trigger will execute before any data modification.

4

Inside the trigger, implement the validation logic that was previously handled by the disallowed function. Use SQL statements and permitted functions to check the condition.

DELIMITER $$
CREATE TRIGGER validate_your_column
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    -- Your validation logic here. For example:
    IF NEW.your_column < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value for your_column cannot be negative.';
    END IF;
END$$

5

If the validation applies to updates as well, create a similar BEFORE UPDATE trigger.

DELIMITER $$
CREATE TRIGGER update_validate_your_column
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    -- Your validation logic here. For example:
    IF NEW.your_column < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value for your_column cannot be negative.';
    END IF;
END$$

3. Simplify the Validation Logic easy

Re-evaluate the constraint's purpose and simplify the expression to use only allowed operations.

1

Identify the CHECK constraint and the disallowed function.

2

Understand the intended validation. What is the constraint trying to enforce?

3

Explore if the same validation can be achieved using simpler, allowed expressions. For example, if you were using a function to check if a date is in the future, you might be able to replace it with a direct comparison to the current date if the function itself was the issue (though NOW() is often disallowed for determinism reasons).

4

Refactor the CHECK constraint definition with the simplified, permitted expression.

ALTER TABLE your_table_name DROP CONSTRAINT your_check_constraint_name;
ALTER TABLE your_table_name ADD CONSTRAINT your_check_constraint_name CHECK (your_simplified_expression);

🔗

Related Errors

5 related errors

MySQL Error 3815: Check Constraint Function Not Allowed