On Aug 21, 2013, at 5:37 PM, Gerald Thibault <dieselmach...@gmail.com> wrote:
> To make it sqlalchemy specific, how do i cause generated CREATE statements to > use absolute schema.table names for foreign key references? you either need to specify "schema" in your referenced Table def, or you'd otherwise have to intercept the AddConstraint construct using @compiles to inject the schema name that you'd want there. > > I realized the reason MyISAM had no issue with it was because it ignores all > those lines, so even if they were wrong (which they seem to be), it wouldn't > care. > > Here is the relevant output from SHOW ENGINE STATUS INNODB: > > ------------------------ > LATEST FOREIGN KEY ERROR > ------------------------ > 130821 13:22:18 Error in foreign key constraint of table test2/registrations: > FOREIGN KEY(user_id) REFERENCES user_vars (id) > )ENGINE=InnoDB: > Cannot resolve table name close to: > (id) > )ENGINE=InnoDB > > It seems the schema is mandatory when operating cross-schema. > > Is there a way to get this behavior from sqlalchemy? > > If MyISAM ignores the FK declarations, and InnoDB requires a schema in order > to have cross-schema fk references, it seems like using schema.table format > would fix this. > > On Wednesday, August 21, 2013 1:57:07 PM UTC-7, Michael Bayer wrote: > you might try asking this as a generic MySQL question on stackoverflow, I > don't really know how MySQL does cross-schema work. my rough understanding > was "not much". > > > > > On Aug 21, 2013, at 4:17 PM, Gerald Thibault <diesel...@gmail.com> wrote: > >> I have a User class, and a Registration class with a FK to User.id. >> >> When I try to create these on a db using InnoDB as default, I get this error: >> >> sqlalchemy.exc.OperationalError: (OperationalError) (1005, "Can't create >> table 'test2.registrations' (errno: 150)") '\nCREATE TABLE >> test2.registrations (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tuser_id >> INTEGER, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(user_id) REFERENCES user_vars >> (id)\n)\n\n' () >> >> If i copy this query and try it manually via MySQL Workbench, it still >> fails. >> >> If I prepend the default schema to the table (user_vars -> >> 'test.user_vars'), the query succeeds. >> >> I'm not entirely sure what is happening here. In the absence of an explicit >> schema, I thought mysql used the active schema to handle table lookups, but >> it looks like this isn't the case. Is it 'switching' the active schema to >> the one hosting the new table? Also, this works perfectly with MyISAM >> tables, so I have even less to go on. >> >> Any ideas? Do I need explicit schema declarations for every fk declared? >> >> -- >> 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 post to this group, send email to sqlal...@googlegroups.com. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/groups/opt_out. >> <innodb.py> > > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
