📋

Description

This error occurs when defining a column whose `DEFAULT` or `GENERATED` expression refers to another column that is defined *after* it in the table schema. MySQL strictly requires that any referenced column, especially if it's a generated column or has an expression default, must be defined *prior* to the column referencing it.

💬

Error Message

Default value expression of column '%s' cannot refer to a column defined after it if that column is a generated column or has an expression as default value.

🔍

Known Causes

3 known causes

⚠️

Forward Reference to Generated Column

A column's default or generated expression attempts to use a value from a `GENERATED` column that is declared later in the `CREATE TABLE` statement.

⚠️

Forward Reference to Expression Default Column

A column's default or generated expression refers to a column defined later which itself has an expression-based `DEFAULT` value.

⚠️

Incorrect Column Definition Order

The overall sequence of column definitions in the `CREATE TABLE` statement violates MySQL's dependency rules for default and generated expressions.

🛠️

Solutions

3 solutions available

1. Reorder Column Definitions easy

Adjust the order of columns in your table definition to place dependencies correctly.

1

Identify the column causing the error (let's call it `column_A`) and the column it depends on (let's call it `column_B`).

2

Modify your `CREATE TABLE` or `ALTER TABLE` statement to define `column_B` before `column_A`. If `column_A` has a default value expression that refers to `column_B`, ensure `column_B` is defined earlier in the schema. This applies if `column_B` is a generated column or also has an expression as its default value.

ALTER TABLE your_table_name MODIFY COLUMN column_B data_type [constraints] FIRST;
-- Or, if you need to move column_A to the end:
ALTER TABLE your_table_name MODIFY COLUMN column_A data_type [constraints] AFTER another_column;

-- Example:
-- Original problematic definition:
-- CREATE TABLE my_table (
--   id INT PRIMARY KEY,
--   generated_col INT AS (id + 1),
--   default_col INT DEFAULT generated_col + 5
-- );
-- This would cause error 3767 because default_col refers to generated_col which is defined after it.

-- Corrected definition:
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  generated_col INT AS (id + 1),
  default_col INT DEFAULT generated_col + 5
);

-- If default_col had a direct reference to a column defined after it, you'd reorder:
-- CREATE TABLE my_table (
--   id INT PRIMARY KEY,
--   col_after INT DEFAULT 10,
--   col_before INT DEFAULT col_after + 5 -- This would be the issue if col_after was defined after col_before
-- );

-- Corrected definition:
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  col_after INT DEFAULT 10,
  col_before INT DEFAULT col_after + 5
);

-- If you're altering an existing table:
ALTER TABLE my_table MODIFY COLUMN generated_col INT AS (id + 1) AFTER id;
ALTER TABLE my_table MODIFY COLUMN default_col INT DEFAULT generated_col + 5 AFTER generated_col;

2. Remove or Redefine Default Value Expression easy

Simplify or remove the default value expression that causes the dependency issue.

1

Identify the column (`column_A`) with the problematic default value expression and the column it references (`column_B`).

2

Option 1: Remove the default value expression entirely if it's not critical. This allows you to insert values explicitly or set the default later.

ALTER TABLE your_table_name ALTER COLUMN column_A DROP DEFAULT;

-- If column_A is a generated column, you cannot remove its generation expression directly like this. You would need to redefine it as a regular column.

3

Option 2: Redefine the default value expression to not depend on `column_B` or to use a static value. If `column_B` is a generated column, you might need to re-evaluate the logic.

ALTER TABLE your_table_name ALTER COLUMN column_A SET DEFAULT some_static_value;

-- Or, if column_B is a generated column, and you need its value, consider if it can be calculated differently or if the dependency order can be fixed (see Solution 1).

3. Redefine Generated Column Logic medium

Adjust the logic of a generated column if it's the source of the dependency.

1

Identify the generated column (`generated_column`) and the column(s) it depends on (`referenced_column`).

2

If `generated_column` is defined after a column that has a default value expression referencing `generated_column`, you need to reorder the columns (Solution 1).

3

If the logic of `generated_column` itself is causing issues (e.g., it's designed to be dependent on a column defined later), you may need to simplify its expression or move its definition before columns that depend on it.

ALTER TABLE your_table_name DROP COLUMN generated_column;
ALTER TABLE your_table_name ADD COLUMN generated_column INT AS (expression_that_does_not_depend_on_later_columns) AFTER some_column;

-- Example: If generated_column depends on a column that is itself a generated column defined later:
-- Problematic:
-- CREATE TABLE my_table (
--   col1 INT,
--   col2 INT AS (col1 * 2),
--   generated_col INT AS (col2 + 1) -- col2 is defined after generated_col
-- );
-- Fix:
CREATE TABLE my_table (
  col1 INT,
  generated_col INT AS (col1 * 2 + 1) -- Combine logic if possible or ensure col2 is defined before generated_col
);

-- Or if you can't combine and must maintain order:
CREATE TABLE my_table (
  col1 INT,
  col2 INT AS (col1 * 2),
  generated_col INT AS (col2 + 1)
);

🔗

Related Errors

5 related errors

MySQL Error 3767: Column Reference Order Issue