warn/error for too-long naming convention names per dialect, provide auto-truncation feature within naming conventions?

Issue #3989 new
Max Rothman created an issue

Currently, MetaData.naming_convention does not support composite indexes or foreign keys, so those must be named explicitly. It'd be nice to be able to provide a template for those to be automatically named as well.

It would likely be necessary for users to define a function to provide the name, since the constraint/index could include any number of columns, but a fairly simple example could be provided in the documentation that simply ','.join()s the column names.

Comments (8)

  1. Mike Bayer repo owner

    Currently, MetaData.naming_convention does not support composite indexes or foreign keys,

    Why is that? I suppose you refer to a convention that must specify the names of all columns, which is not necessary in order to produce a naming convention, for example using just the name of the first column is usually sufficient.

    It would likely be necessary for users to define a function to provide the name, since the constraint/index could include any number of columns, but a fairly simple example could be provided in the documentation that simply ','.join()s the column names.

    the reason this is not supplied is because it would lead to names that quickly overrun the maximum name length for the database. If you really want to string together an arbitrarily long list of column names it's better that this be something you define yourself so that you can also define a consistent truncation policy. If I put a ",.join" token in, then I'll get bug reports that the names are too long. If you have ideas on this part let me know.

  2. Max Rothman reporter

    Sorry, I misunderstood how this feature works. We recently added a naming convention dict and it caused Alembic to generate a migration changing the names of a bunch of constraints and indexes, especially the composite ones, so I thought they needed to be explicitly handled but weren't currently. This can be closed.

    Out of curiosity, SqlAlchemy was already autogenerating names for those composites of the format <tablename>_<col1_name>_<col2_name>... before we turned the feature on. Where were those names coming from? Is that deprecated behavior, and is it documented anywhere? Other differences from the suggested naming convention include that primary keys were named ..._pkey instead of pk_... and unique constraints were named ..._key instead of uq_....

  3. Mike Bayer repo owner

    Where were those names coming from?

    all relational databases other than SQLite apply names automatically to constraints. The conventions however vary across platform. With "pkey" it sounds like you're using Postgresql. there is only one place that SQLAlchemy names something and that's if you use "index=True" on a Column, it gives a name to that index along the lines of "ix_tablename_columnname", but this is always a single column.

  4. Mike Bayer repo owner

    We don't truncate a too-long name generated by naming convention. which means the DB is going to do it, unpredictably. Here's the bug on the other side: https://bitbucket.org/zzzeek/alembic/issues/438/index-name-auto-truncation-done-in.

    Why don't we run naming convention names through truncation similar to that of labels? We would just need to make it deterministic per the name itself. This can be an optional feature, which people would find out about because we would check the length of labels and raise an error if it goes past that of the dialect's known limit.

  5. Log in to comment