On Fri, Jun 25, 2021, at 11:58 AM, mkmo...@gmail.com wrote: > > When using PostgreSQL, creating a foreign key on a column in a table does not > automatically index that column, unlike Oracle or MySQL. > > I would like to get SQLAlchemy to automatically create an index on the same > columns that are specified in a ForeingKeyConstraint. > > For example, if I have a table like this: > > foo = table( > 'foos', metadata, > Column('id', BigInteger), > Column('parent_id', BigInteger), > ForeignKeyConstraint(('parent_id',), refcolumns=('bar.id',), > name='foo_parent_id_fk') > ) > > I would like it to automatically add an Index like: > > Index('foo_parent_id_idx', 'parent_id') > > I've made the following, which appears to work: > > from sqlalchemy import event, Table > > @event.listens_for(Table, 'before_create') > def add_index_on_foreign_key_columns(table, connection, **kwargs): > for foreign_key in table.foreign_key_constraints: > index_name = foreign_key.name.replace('_fk', '_idx') > columns = (c.name for c in foreign_key.columns) > Index(index_name, *columns, _table=table) > > Is there a better way to accomplish this?
that's the perfect way to accomplish this and is just what I'd recommend - the only thing I would change is when creating the Index(), it should auto-add itself to the Table as you are passing Table-bound Column objects to it, so i would not use the private "_table" parameter. Otherwise, you can say "table.append_index(my_index)" to ensure it is added to the Table. > > Thanks and best regards, > > Matthew Moisen > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f423d1c0-682d-439b-8d04-74daaf498778%40www.fastmail.com.