Automatically add Index on same columns in ForeignKeyConstraint in Postgresql using Custom DDL/Compilation

[mkmo...@gmail.com]

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?

Thanks and best regards,

Matthew Moisen

[Mike Bayer]

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/43dba570-41de-4164-82c8-1cecb3dd9b1cn%40googlegroups.com.

[mkmo...@gmail.com]

Hi Mike,

Thanks for your feedback. I'll leave your suggestion here if anyone comes across this:

    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 for c in foreign_key.columns)
             Index(index_name, *columns)

Best regards,

Matthew Moisen