📋

Description

MySQL Error 3816 occurs when you attempt to define a `CHECK` constraint whose expression refers to a user-defined variable (e.g., `@my_var`) or a system variable (e.g., `@@sql_mode`). `CHECK` constraints are designed to evaluate expressions based solely on column values or literal constants within the row being inserted or updated, not dynamic variables.

💬

Error Message

An expression of a check constraint '%s' cannot refer to a user or system variable.

🔍

Known Causes

3 known causes

⚠️

Using User-Defined Variables

Attempting to include a session-specific user variable (e.g., `@my_variable`) directly within the `CHECK` constraint's expression.

⚠️

Referencing System Variables

Including a global or session system variable (e.g., `@@sql_mode`, `@@session.time_zone`) in the `CHECK` constraint definition.

⚠️

Misunderstanding Constraint Scope

Assuming `CHECK` constraints can evaluate dynamic session or global state, rather than being limited to the row's column values and static expressions.

🛠️

Solutions

3 solutions available

1. Replace Variables with Literal Values or Column References easy

Modify the check constraint to use hardcoded values or references to other columns in the table.

1

Identify the check constraint that is causing the error. The error message will typically include the name of the constraint.

2

Examine the expression within the check constraint. Locate any references to user-defined variables (e.g., @my_variable) or system variables (e.g., @@global.sql_mode).

3

Replace the variable references with the desired literal value or a reference to another column within the same table. For example, if you were using `@min_value`, replace it with `10` or `other_column`.

ALTER TABLE your_table
DROP CONSTRAINT your_check_constraint_name;

ALTER TABLE your_table
ADD CONSTRAINT your_check_constraint_name CHECK (column_name > 10);

4

Recreate the check constraint with the modified expression.

2. Use Stored Procedures or Triggers for Dynamic Logic medium

Move complex validation logic that relies on variables into stored procedures or triggers.

1

Determine if the logic within the check constraint can be encapsulated in a stored procedure or a trigger. This is particularly useful if the validation depends on external factors or calculations that are not directly column-based.

2

Create a stored procedure that performs the desired validation. This procedure can accept parameters that represent the values previously held by variables.

DELIMITER //

CREATE PROCEDURE validate_my_value(IN new_value INT)
BEGIN
    -- Your validation logic here, possibly using session variables or other logic
    IF new_value < @my_threshold THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value is too low.';
    END IF;
END //

DELIMITER ;

3

Remove the problematic check constraint from your table.

ALTER TABLE your_table
DROP CONSTRAINT your_check_constraint_name;

4

Instead of relying on the check constraint, call the stored procedure before or during data modification operations (e.g., in your application logic or within a trigger that fires before INSERT/UPDATE).

SET @my_threshold = 50;
CALL validate_my_value(100); -- This would not raise an error
-- CALL validate_my_value(20); -- This would raise an error

3. Pre-calculate and Store Dynamic Values medium

If the variable's value is relatively stable, pre-calculate it and store it in a separate, fixed table.

1

Identify the variable used in the check constraint and determine how its value is set and when it changes.

2

Create a new table to store this dynamic value. This table should have a single row and a single column to hold the value.

CREATE TABLE configuration (
    min_allowed_value INT
);

3

Insert the current desired value into the configuration table.

INSERT INTO configuration (min_allowed_value)
VALUES (25);

4

Modify the check constraint to reference the value from the configuration table instead of the variable.

ALTER TABLE your_table
DROP CONSTRAINT your_check_constraint_name;

ALTER TABLE your_table
ADD CONSTRAINT your_check_constraint_name CHECK (column_name > (SELECT min_allowed_value FROM configuration));

5

When you need to change the validation rule, update the value in the configuration table.

UPDATE configuration
SET min_allowed_value = 50
WHERE TRUE;

🔗

Related Errors

5 related errors

MySQL Error 3816: Check Constraint Variable Reference