MySQL raises ER_GENERATED_COLUMN_REF_AUTO_INC (error 3109) when a generated column expression references a column defined with the AUTO_INCREMENT attribute.
ER_GENERATED_COLUMN_REF_AUTO_INC (MySQL error 3109) appears when a generated column refers to an auto-increment column. Remove the reference or drop AUTO_INCREMENT, then recreate the generated column to resolve the issue.
ER_GENERATED_COLUMN_REF_AUTO_INC
MySQL throws error 3109 when you create or alter a table so that a generated column expression references a column marked AUTO_INCREMENT. MySQL 5.7.6 and later forbid this relationship because the auto-increment value may change, breaking the deterministic requirement for stored generated columns.
The check happens during CREATE TABLE, ALTER TABLE, and after restoring dumps. Fixing it quickly is critical because schema updates will fail and block deploy pipelines.
The root cause is an expression for a virtual or stored generated column that directly or indirectly reads an AUTO_INCREMENT column. MySQL validates dependencies and rejects any generated column that depends on non-deterministic sources.
The error also appears when altering an existing column to AUTO_INCREMENT if there is already a generated column referencing it, or when changing an ordinary column into a generated one that reads an AUTO_INCREMENT column.
Remove the AUTO_INCREMENT attribute from the referenced column when business logic allows. If you still need an incrementing identifier, switch to a trigger or application-side sequence.
Alternatively, rewrite the generated column so it no longer references the AUTO_INCREMENT column. Use deterministic columns such as created_at or other immutable fields instead.
Create table failure: You try to create a table with id INT AUTO_INCREMENT and slug VARCHAR generated as (concat('p-', id)). Remove AUTO_INCREMENT or compute slug in the client.
Altering schema: You change an id column to AUTO_INCREMENT on a table that already has a derived column. Drop the generated column first, add AUTO_INCREMENT, then recreate the generated column without referencing id.
Keep generated column expressions deterministic and free of side effects. Never reference AUTO_INCREMENT or random functions. Document the rule in your schema style guide and add lint checks to CI.
Use Galaxy’s AI copilot to scan DDL statements. The editor flags invalid generated column references before you run them, preventing migration failures.
ER_BAD_FIELD_ERROR: Raised when a generated column references a non-existent column. Ensure all referenced columns exist before creation.
ER_NONDETERMINISTIC_FUNCTION: Appears when the expression uses non-deterministic functions like RAND(). Replace with deterministic logic.
The generated column directly references an AUTO_INCREMENT primary key, violating MySQL rules.
A previously valid generated column breaks after the referenced column is changed to AUTO_INCREMENT.
The expression relies on another generated column that, in turn, references an AUTO_INCREMENT column.
Generated column references a column that does not exist.
Expression uses RAND(), NOW(), or other non-deterministic functions.
Name of generated column clashes with existing column or index.
No. Both virtual and stored generated columns are blocked from reading AUTO_INCREMENT columns.
Changing a column to plain INT keeps existing values. Only the automatic sequence stops.
No. Generated columns were introduced in MySQL 5.7.6, so earlier versions never raise this error.
Galaxy’s real-time linting flags illegal references as you type DDL, and AI copilot suggests compliant rewrites.
.avif)
.avif){kind=logo}
