To make it sqlalchemy specific, how do i cause generated CREATE statements to use absolute schema.table names for foreign key references?
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 > <[email protected]<javascript:>> > 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 [email protected] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
