unnamed constraint concerns

[jvanasco]

A few years ago I was working on a PR for SQLAlchemy and was testing the naming conventions on a project. I accidentally never undid this change:

# metadata = MetaData(naming_convention=NAMING_CONVENTION)
metadata = MetaData()

I'm now integrating Alembic onto this project and seeing a few oddities:

1- If I use autogenerate for migrations, alembic wants to set up a named foreign key. I can disable this by removing the "fk" key in the naming_convention dict, so it seems okay. I just want to double check.

2- Unique constraints are an issue. If I run a migration with the "uq" prefix enabled, Alembic generates a batch_op.create_unique_constraint

• If i run the upgrade, I get a new named constraint in addition to the unnamed constraint
• If i run a downgrade, which only lists the named constraint, both unique constraints are removed

This seems weird and is problematic. I assume I am doing something very wrong here, as Alembic and SQLAlchemy are rock solid.

This is an example of the generated migration that causes the behavior:

def upgrade() -> None:
    with op.batch_alter_table('acme_account', schema=None) as batch_op:
        batch_op.create_unique_constraint(batch_op.f('uq_acme_account_name'), ['name'])

def downgrade() -> None:
    with op.batch_alter_table('acme_account', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('uq_acme_account_name'), type_='unique')

UPDATE

After a bit of testing and digging through Alembic and SQLAlchemy, I think I figured out what is happening.

1- The call to drop_constraint() is somehow removing both constraints within the table definition.
2- I can't add an anonymous/unnamed constraint after, because of 2 things:

a- the naming_convention on the base MetaData defines uq, so no name will trigger an automatic naming.
b- if I disable the uq globally (or within the op as below), create_constraint operates via "add constraint" which requires a name (however table definitions don't require this). Downgrading is not possible.

        nc_old = peter_sslers.model.meta.Base.metadata.naming_convention
        peter_sslers.model.meta.Base.metadata.naming_convention = sa.sql.schema.DEFAULT_NAMING_CONVENTION
        batch_op.create_unique_constraint(None, ['name'])
        peter_sslers.model.meta.Base.metadata.naming_convention = nc_old

[zzzeek]

if you ran migrations without naming conventions for awhile, and this is NOT SQLite, I would go into the DB, get all those names, and assign them to the constraints in question, overriding the naming convention.

if this IS SQLite, I'd manually write a migration that gives names to all unnamed constraints (drops and recreates all of them with names).

the idea is the same, get control over those names

[jvanasco]

Thanks, Mike. This project is only deployed on SQLite right now.

It seems the only way to do this in the migration is:

    # add a named constraint
    with op.batch_alter_table('acme_account', schema=None) as batch_op:
        batch_op.create_unique_constraint(batch_op.f('uq_acme_account_name'), ['name'])

    # remove the named constraint, which will implicitly remove the unnamed one as well
    with op.batch_alter_table('acme_account', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('uq_acme_account_name'), type_='unique')

    # add the named constraint back in, permanently
    with op.batch_alter_table('acme_account', schema=None) as batch_op:
        batch_op.create_unique_constraint(batch_op.f('uq_acme_account_name'), ['name'])

I'm not providing a downgrade from this, as the constraints should have been named. I forgot SQLite doesn't create named constraints for unique, which departs from other implementations.

[zzzeek]

yeah you have to recreate the tables in order to drop the old constraints, since they have no name.

I always wince at the weekly "SQLIte is so amazing!" posts on hacker news. it's such garbage from my pov