scienceoss.com

The default engine in MySQL, MyISAM, does not support foreign keys. You need foreign keys to use the SQLAlchemy Python package effectively.

In order to use foreign keys, you need to convert your tables to the InnoDB engine:

ALTER TABLE mytable ENGINE = INNODB;

To add a foreign key to mytable where the unique keys are coming from othertable, use this:

ALTER TABLE mytable ADD FOREIGN KEY (otherID) REFERENCES othertable (otherID);

If you run that same line several times, several identical foreign keys will be created, which will confuse SQLAlchemy. In that case you need to delete the keys. To do so, you need their name. To see the name, use

SHOW CREATE TABLE mytable;

The foreign key’s label will be something that ends in something similar to fk_1 or fk_2. Using that label, you can then delete the foreign key:

ALTER TABLE mytable DROP FOREIGN KEY mytable_dbfk_2;

Tags: alter, foreign key, MySQL, Python, SQLAlchemy, tables

Filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.