why is the constrain name set to None in create_foreign_key

[ltrojan]

I have taken a look at some of the existing issues and discussions, I'm not sure I understand everything but I could not find a definitive answer to this

I noticed that if I add a new class with a foreign key contrain, the autogenerated migration adds

sa.ForeignKeyConstraint(['user_id'], ['users.id'], )

and this seems to somehow inform alembic of the autogenerated constrain name

Whereas, when a new column with a foreign key contrain is added to an already existing table, the autogenerated script adds

op.create_foreign_key(None, 'projects', 'organizations', ['organization_id'], ['id'])

and, when dropping that constrain (either in anothe rupgrade script or i nthe downgrade script), the constrain name is left as None, and this leads alembic to raise the error

sqlalchemy.exc.CompileError: Can't emit DROP CONSTRAINT for constraint ForeignKeyConstraint(<sqlalchemy.sql.base.ReadOnlyColumnCollection object at 0x7ff3825e4b30>, None, table=Table('mytable', MetaData(), schema=None)); it has no name

is the re any plan to fix this inconsistency?

[zzzeek]

hi -

read the doc section at https://alembic.sqlalchemy.org/en/latest/naming.html which explains what has to be done here

[ltrojan]

thanks!

I don't quite understand, do i need to add explicitly that naming_convention={...} when declaring the metadata or is that done by default?

still, why does it look like the constrain name is set correctly by alembic autegenerate when creating a new table, even if the contrain name isn't provided, but doesn't when adding the column/constrain to a new column?

[zzzeek]

it's in the doc above. all databases other than SQLite assign names to new constraints automatically for both CREATE TABLE as well as ALTER TABLE ADD CONSTRAINT, if an explicit name is not given. your error message refers to the name not being present on DROP, which is because the alembic command is not passed the name of the constraint up front. the ALTER TABLE DROP CONSTRAINT command requires a name.

[ltrojan]

ok, thank you, so much!

I've added a breakpoint in the run_migrations_online script and it appears that the target metadata naming conventions looks like this:

(Pdb) target_metadata.naming_convention
immutabledict({'ix': 'ix_%(column_0_label)s'})

does that mean that there is not foreign key convention and therefore the name is left as None? do i need to explicitly add that dict as described in that page?

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_`%(constraint_name)s`",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s"
    })

(sorry for being dumb!)

[zzzeek]

yah we dont set that by default. SQLAlchemy has never applied names automatically (except for that one index case) so this needs to be set up by the end user

[ltrojan]

thanks a lot!

trying that now! I've accepted the answer but ill post again to confirm it works.... (!)