23

Does Column with ForeignKey creates index automatically? Or I need to do that manually adding index=True?

some_field = Column(Integer, ForeignKey(SomeModel.id))
user
  • 5,370
  • 8
  • 47
  • 75
Vitalii Ponomar
  • 10,686
  • 20
  • 60
  • 88

2 Answers2

24

You do need to either specify index=True or create an Index object explicitly:
Index('myindex', mytable.c.col1, mytable.c.col2, unique=True), which allows more control over other parameters of the index, such as the name and support for more than one column.

See Indexes for more information.

Stephen Fuhry
  • 12,624
  • 6
  • 56
  • 55
van
  • 74,297
  • 13
  • 168
  • 171
  • 3
    Sqlalchemy won't do that for you, but mysql's Inno engine will do this automatically. – Shuo Feb 08 '15 at 11:26
  • 2
    Is this true for primary keys too? – Berislav Lopac Nov 23 '15 at 16:32
  • 3
    @BerislavLopac: I believe most (if not all) RDBMSs do create index for primary key automatically. – van Nov 23 '15 at 18:35
  • 3
    @van: Postgres will *not* create indexes on foreign keys for you, unlike MySQL's InnoDB engine. – Albert S Jan 27 '17 at 16:59
  • @van Your comment is really interesting to me and I appreciate it! Does anyone have information specific to SQLAlchemy, Postgres, and whether indexes are created for `primary_key=True` ? Specifically, I mean tables created by ORM **without** `index=True` – blong Jan 04 '21 at 16:57
  • 1
    @blong: see this answer, which covers it: https://stackoverflow.com/a/42899045/99594 – van Jan 06 '21 at 12:10
2

As van's answer indicates, you should explicitly add an index as indicated by the docs.

The implementation of foreign keys is database specific, and some DBs such as MySQL will still automatically create an index for foreignkey column, but others will not. See discussion in comments above.

e.g from MySQL docs:

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

FluxLemur
  • 1,827
  • 16
  • 18