Rename an existing constraint

[jens.t...@gmail.com]

Hello,

I started out migrating my db schema forward using Alembic, and without any constraint naming convention. That caused constraints to be named using MySQL’s default naming. Alas, now I added a naming convention (see doc here) which is different than the db’s naming.

So now I face a mix of current db-style constraint names, and new convention-stale constraint names. That’ll cause me a headache.

What is the recommended way of renaming all existing constraint names? Is dropping/creating them the only way, or did I fail finding a “rename_constraint()” function in Alembic?

Thanks!

Jens

[Mike Bayer]

Unfortunately there's no RENAME CONSTRAINT directive, so dropping and
recreating is how it has to go.

Alembic and SQLAlchemy together do have the ability to return lists of
constraints and produce CREATE instructions for them. Recently I did
some of this related to foreign keys, and the same idea would apply to
indexes, unique constraints, CHECK constraints. Here's two ways to
do that to give you some ideas:


from alembic.operations.ops import CreateForeignKeyOp
from alembic.migration import MigrationContext
from alembic.operations import Operations

with engine.connect() as conn:

ctx = MigrationContext.configure(conn)
op = Operations(ctx)
m = MetaData(naming_convention={...})

t = Table(table_name, m, autoload_with=conn)
for fk in t.foreign_keys:
op.drop_constraint(
fk.name, table_name, type_="foreignkey")

# give fk the name we want
fk.name = "somename"

# or name it None to get the naming convention
# fk.name = None

create_fk = CreateForeignKeyOp.from_constraint(fk)

op.invoke(create_fk)


or using the inspector, more manual / explicit:

from sqlalchemy import inspect
with engine.connect() as conn:

ctx = MigrationContext.configure(conn)
op = Operations(ctx)
insp = inspect(conn)
for fk in insp.get_foreign_keys(table_name):

for fk in fks:
op.drop_constraint(
fk['name'], fk['source_table'], type_="foreignkey")

for fk in fks:
op.create_foreign_key(
fk['name'], fk['source_table'],
fk['referred_table'],
fk['constrained_columns'],
fk['referred_columns'],
onupdate=fk['options'].get('onupdate'),
ondelete=fk['options'].get('ondelete'),
deferrable=fk['options'].get('deferrable'),
initially=fk['options'].get('initially'),
)

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alem...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[jens.t...@gmail.com]

Thank you, Mike!

I’ll take a closer look at your proposed code this week.

I am curious though: not even MySQL has a rename feature, is that because of consistency? PostgreSQL adds ALTER TABLE … RENAME CONSTRAINT with 9.2 though. (Boy, I keep running into issues that keep pushing me towards Porstgres.)

Jens

[jens.t...@gmail.com]

Given an Alembic migration, would you recommend the following code to rename constraints?

from alembic import op                                                                              

import sqlalchemy as sa                                                                             

from srv.orm.meta import NAMING_CONVENTION # as per Pylons cookiecutter template                                                         

                                                                                                    

def upgrade():                                                                                      

                                                                                                    

    connection = op.get_bind()                                                                      

    engine = connection.engine                                                                      

    metadata = sa.MetaData(naming_convention=NAMING_CONVENTION)                                     

                                                                                                    

    for table_name in engine.table_names():                                                         

        table = sa.Table(table_name, metadata, autoload_with=connection)                            

        for fk in table.foreign_keys:                                                               

            op.drop_constraint(fk.name, table_name, type_="foreignkey")                            

            fk.name = None                                                                         

            op.invoke(CreateForeignKeyOp.from_constraint(fk))                                      

For downgrade() we'd create a metadata without naming_convention, thus falling back to the db's naming (which is where we're coming from).

However, how would I go about iterating over indexes, unique constraints, and check constraints of a table?

Thank you!

[Mike Bayer]

On Wed, Aug 30, 2017 at 7:18 AM, <jens.t...@gmail.com> wrote:
> Given an Alembic migration, would you recommend the following code to rename
> constraints?
>
> from alembic import op
> import sqlalchemy as sa
> from srv.orm.meta import NAMING_CONVENTION # as per Pylons cookiecutter
> template
>
> def upgrade():
>
> connection = op.get_bind()
> engine = connection.engine
> metadata = sa.MetaData(naming_convention=NAMING_CONVENTION)
>
> for table_name in engine.table_names():
> table = sa.Table(table_name, metadata, autoload_with=connection)
> for fk in table.foreign_keys:
> op.drop_constraint(fk.name, table_name, type_="foreignkey")
> fk.name = None
> op.invoke(CreateForeignKeyOp.from_constraint(fk))
>
> For downgrade() we'd create a metadata without naming_convention, thus
> falling back to the db's naming (which is where we're coming from).
>
> However, how would I go about iterating over indexes, unique constraints,
> and check constraints of a table?

same idea, the table has table.indexes for the Index objects,
table.constraints in fact has all of ForeignKeyConstraint,
UniqueConstraint, CheckConstraint so you could iterate just that and
do an isinstance() to take the right course of action.

>
> Thank you!
>
>
> On Wednesday, August 30, 2017 at 7:20:32 AM UTC+10, jens.t...@gmail.com
> wrote:
>>
>> Thank you, Mike!
>>
>> I’ll take a closer look at your proposed code this week.
>>
>> I am curious though: not even MySQL has a rename feature, is that because
>> of consistency? PostgreSQL adds ALTER TABLE … RENAME CONSTRAINT with 9.2
>> though. (Boy, I keep running into issues that keep pushing me towards
>> Porstgres.)
>>
>> Jens
>
>
>