📋

Description

This error occurs when a column's `DEFAULT` value expression attempts to reference or derive its value from another column within the same row during table creation or alteration. MySQL's `DEFAULT` clause expects a constant, a non-row-dependent function, or a literal, not a reference to another column in the current row.

💬

Error Message

Default value expression of column '%s' cannot refer to a row value.

🔍

Known Causes

3 known causes

⚠️

Direct Column Reference in DEFAULT

The `DEFAULT` value expression for a column explicitly refers to another column within the same row context, which is not permitted for default values.

⚠️

Misuse of DEFAULT for Generated Values

Attempting to define a column whose value should be derived from other columns in the same row using the `DEFAULT` clause instead of a `GENERATED ALWAYS AS` expression.

⚠️

Complex Row-Dependent Default Logic

The intended default value calculation implicitly relies on other column values present in the same row during insertion, which is not supported by the `DEFAULT` clause.

🛠️

Solutions

3 solutions available

1. Remove Row-Specific Defaults easy

Modify the table definition to use non-row-specific default values.

1

Identify the column causing the error. The error message usually indicates which column has the problematic default value.

SHOW CREATE TABLE your_table_name;

2

Examine the `DEFAULT` clause for that column. You'll likely see a reference to another column within the same row.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT some_other_column_value;

3

Modify the `ALTER TABLE` statement to use a literal value, a built-in function (like `NOW()`, `UUID()`), or a constant that does not depend on other row values.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT 'some_literal_value';
-- OR
ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT NOW();

4

If the default value is intended to be derived from another column, you will need to handle this logic in your application code or triggers.

2. Use a Trigger to Populate Default Values medium

Leverage a trigger to set the default value based on other row data after insertion.

1

First, remove the invalid default value from the column definition.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT NULL;

2

Create a `BEFORE INSERT` trigger that checks if the problematic column is NULL and then sets its value based on other columns in the row.

DELIMITER //
CREATE TRIGGER set_default_for_your_column
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    IF NEW.your_column_name IS NULL THEN
        SET NEW.your_column_name = NEW.some_other_column_value; -- Or any other logic
    END IF;
END;//
DELIMITER ;

3

Test the insertion to ensure the trigger correctly populates the default value.

INSERT INTO your_table_name (other_column1, other_column2) VALUES ('value1', 'value2');

3. Handle Default Logic in Application Code easy

Move the logic for determining default values from the database schema to your application.

1

Remove the problematic default value from the column definition in your MySQL table.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name datatype DEFAULT NULL;

2

In your application's data insertion logic (e.g., Python, Java, PHP), before executing the `INSERT` statement, check if the value for `your_column_name` is provided.

3

If the value is not provided, calculate it based on other data available in your application and then include it in the `INSERT` statement.

4

Example (Conceptual Python):

data = {
    'column_a': 'value_a',
    'column_b': 'value_b'
}

if 'your_column_name' not in data:
    data['your_column_name'] = calculate_default_based_on(data['column_a']) # Logic here

# Then construct and execute your INSERT query with the 'data' dictionary

🔗

Related Errors

5 related errors

MySQL Error 3771: Default Value Row Reference