Will Sqlalchemy automatically create index for foreign key?

Question

I use MyISAM engine in MYSQL. I find that the foreign keys in the table has been automatically created indexes. Is the behavior of Sqlalchemy or MYSQL? I don't need the index for the foreign key. What should I do? Add the param index=False when defining the column?

My code:

class A(Base):
    __tablename__ = "a"
    a_id = Column(BigInteger, primary_key=True, autoincrement=True)

class B(Base):
    __tablename__ = "b"
    b_id = Column(BigInteger, primary_key=True, autoincrement=True)
    a_id = Column(BigInteger, ForeignKey(A.a_id))

I found the index for a_id in B has been created automatically. Is it correct? Can I do this if I want to remove such an index?

class B(Base):
    __tablename__ = "b"
    b_id = Column(BigInteger, primary_key=True, autoincrement=True)
    a_id = Column(BigInteger, ForeignKey(A.a_id), index=False)

You can't have a foreign key on something without an index on that something... — ceejayoz
– ceejayoz, Commented Feb 23, 2012 at 15:37

joinfu · Accepted Answer · 2012-03-21 14:55:21Z

2

Lafada, you are incorrect. KEY a_id (a_id) in table b clearly shows an index was made on the foreign key field. This is a behaviour of InnoDB -- it will automatically create an index over fields in a foreign key relationship if such an index does not already exist:

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

answered Mar 21, 2012 at 14:55

joinfu

313 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NPatel · Accepted Answer · 2012-02-24 04:16:04Z

I created your models in my local machine and i didnt find any index on foreign key.

I use

from sqlalchemy import create_engine

engine = create_engine('mysql://test:test@localhost/test1', echo=True)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Date, Text, BigInteger, ForeignKey
from sqlalchemy.orm.attributes import InstrumentedAttribute


Base = declarative_base()


class A(Base):
    __tablename__ = "a"
    a_id = Column(BigInteger, primary_key=True, autoincrement=True)

class B(Base):
    __tablename__ = "b"
    b_id = Column(BigInteger, primary_key=True, autoincrement=True)
    a_id = Column(BigInteger, ForeignKey(A.a_id))

Base.metadata.create_all(engine)

And the background log is.

2012-02-24 09:39:24,249 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2012-02-24 09:39:24,249 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,254 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2012-02-24 09:39:24,254 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,277 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2012-02-24 09:39:24,277 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,288 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2012-02-24 09:39:24,289 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,292 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2012-02-24 09:39:24,292 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,293 INFO sqlalchemy.engine.base.Engine DESCRIBE `a`
2012-02-24 09:39:24,293 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,321 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-02-24 09:39:24,322 INFO sqlalchemy.engine.base.Engine DESCRIBE `b`
2012-02-24 09:39:24,322 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,323 INFO sqlalchemy.engine.base.Engine ROLLBACK
2012-02-24 09:39:24,324 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE a (
    a_id BIGINT NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (a_id)
)


2012-02-24 09:39:24,324 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,590 INFO sqlalchemy.engine.base.Engine COMMIT
2012-02-24 09:39:24,591 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE b (
    b_id BIGINT NOT NULL AUTO_INCREMENT, 
    a_id BIGINT, 
    PRIMARY KEY (b_id), 
    FOREIGN KEY(a_id) REFERENCES a (a_id)
)


2012-02-24 09:39:24,591 INFO sqlalchemy.engine.base.Engine ()
2012-02-24 09:39:24,762 INFO sqlalchemy.engine.base.Engine COMMIT

I also check the create table statement in mysql for a and b.

mysql> show create table a;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a_id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table b;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| b     | CREATE TABLE `b` (
  `b_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`b_id`),
  KEY `a_id` (`a_id`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Index is only on primary keys not in foreign key.

Please try this on your machine and if you found that your machine is creating index on foreign key in table b then post that log and model you create like I wrote in this answer .

Hope this will solve your problem.

Thanks. I will try it again. BTW, I use MyISAM and you use InnoDB. Anyway, I can do it myself. Thanks a lot!
Please provide the log for MyISAM if you get the index in that. It will help us to understand the MyISAM behavior different from InnoDB.

I159 · Accepted Answer · 2014-03-28 14:38:40Z

0

This is MySQL's behavior and it is correct for MySQL. You should reconcile with this fact. At least I don't know how to rid indexing of foreign keys.

Add the param index=False when defining the column?

Hmm, maybe... I'll try it.

answered Mar 28, 2014 at 14:38

I159

31.4k32 gold badges102 silver badges138 bronze badges

Collectives™ on Stack Overflow

Will Sqlalchemy automatically create index for foreign key?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related