Batch recreate loses naming convention for Boolean/Enum CHECK constraints

[zzzeek]

Describe the bug

In batch "recreate" mode, a CHECK constraint generated by a SchemaType
(Boolean/Enum with create_constraint=True) loses its naming convention name.
The constraint is regenerated from the column's type onto the temporary table, which
is built on a plain MetaData() with no naming convention, so it comes out with the
raw name from the type rather than the convention-applied name.

This is specific to passing a Table object as copy_from, which is what
carries a live SchemaType into the recreate. In the reflected case the
constraint comes back from reflection as an ordinary named CHECK and is
transferred verbatim, so it is unaffected.

No column operation on the affected column is needed; any batch operation that
triggers a recreate is enough.

This is distinct from the limitation documented at
https://alembic.sqlalchemy.org/en/latest/batch.html#batch-check-constraints,
which concerns unnamed CHECK constraints being omitted from the recreate.
Here the type is named, so the constraint does participate in the recreate --
just under a different name than it had.

To Reproduce

from alembic.migration import MigrationContext
from alembic.operations import Operations
from sqlalchemy import Boolean, Column, Integer, MetaData, Table, create_engine

naming = {"ck": "ck_%(table_name)s_%(constraint_name)s"}

m = MetaData(naming_convention=naming)
user = Table(
    "user",
    m,
    Column("id", Integer, primary_key=True),
    Column("is_active", Boolean(create_constraint=True, name="is_active")),
)

e = create_engine("sqlite://")
m.create_all(e)

with e.begin() as conn:
    op = Operations(MigrationContext.configure(conn))
    with op.batch_alter_table("user", copy_from=user, recreate="always") as batch_op:
        batch_op.add_column(Column("y", Integer))

    print(conn.exec_driver_sql(
        "select sql from sqlite_master where name='user'").scalar())

Expected behavior

The constraint keeps the name it had, matching create_all():

CONSTRAINT ck_user_is_active CHECK (is_active IN (0, 1))

Actual behavior

CREATE TABLE "user" (
	id INTEGER NOT NULL,
	is_active BOOLEAN,
	y INTEGER,
	PRIMARY KEY (id),
	CONSTRAINT is_active CHECK (is_active IN (0, 1))
)

The constraint has been silently renamed from ck_user_is_active to is_active,
so a later batch_op.drop_constraint("ck_user_is_active", type_="check") will fail.

Additional context

ApplyBatchImpl._transfer_elements_to_new_table() creates the new table with a bare
MetaData(). Discovered while investigating #1768; related to #1845.

Versions

• Alembic: 1.19.1 (main)
• SQLAlchemy: 2.1
• Database: SQLite

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the main branch:

Establish a single producer for type-bound CHECK constraints in batch https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/6877