1. Davide Alberani
  2. imdbpy
  3. Issues
Issue #17 new

imdbpy2sql doesn't create all foreign keys

Anonymous created an issue

It seems that imdbpy2sql doesn't create all of the foreign keys when creating the tables, indexes, etc. Some of them are there, just not all.

I'm using MySQL with my tables configured as InnoDB (required if you want foreign keys.) SQLalchemy is also involved there too. Imdbpy version 4.9 and MySQL 5.1.

In the file - alchemyadapter.py function: def addForeignKeys(self, mapTables, ifNotExists=True):

I added to the try: except: section: print fkName print "Description: %s" % e

to see why it was failing to create the FKs that I was expecting, and the error is: constrain error: fk_name_id_2 Description: (OperationalError) (1005, "Can't create table 'hdtg.#sql-43e7_44' (errno: 121)") 'ALTER TABLE cast_info ADD CONSTRAINT fk_name_id_2 FOREIGN KEY(person_id) REFERENCES name (id)' ()

11 FKs failed with the same kind of error. After some Googling, I believe this error is due to the FKs needing to be uniquely named per database, and your naming convention is unique per table.

Instead of naming the FK fk_<foreign_table_name><foreign_col_name><col_num>, maybe add the parent table in there as well - that way fk_title_id_2 becomes fk_cast_info_title_id_2 and fk_complete_cast_title_id_2

Hope this helps!

Comments (1)

  1. Davide Alberani repo owner

    Thanks for the bug report.

    There are some known issues with the creation of the indexes. I can't reproduce your situation with MySQL 5.5 (using SQLAlchemy); for sure there are problems with Postgresql. I'm not too sure it's a matter of uniqueness of the keys: this should be already solved.

    Anyway, if possible try with the code from the repository: it should contain some improvements.

    Thanks again, and if you have other information/hint, please let us know.

  2. Log in to comment