Josh Stratton wrote: > >>> From what I've read of sqlalchemy, I originally wanted to have a main >>> table with one attribute foreign keyed to another table's >>> autoincremented integer primary key userId and a userName. I thought >>> I could join the tables together and set that as the mapper. The save >>> every object in my session. I thought I had it all figured out until >>> I found out that specifically for sqlite, autoincrement (or >>> autogenerated primary keys) don't work with "composite" tables/using a >>> join. >> >> as long as the primary key of table #1 is a single-column primary key, >> SQLite autoincrements it. To get the pk into your joined table, set the >> two columns to the same attribute. e.g. as in >> http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables > > Ah, I was going to reference another page, but I see you are the author. > Heh. > > http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg09503.html > > Anyway, isn't this the same problem?
that email refers to a composite primary key in a single table. this is a primary key that consists of more than one column. your original email didn't seem to describe that usage. > If I don't use foreign keys > pointing the main table to the user lookup, I get an error: > > <class 'sqlalchemy.exceptions.ArgumentError'>: Can't find any foreign > key relationships between 'jobs' and 'users' this is always easily worked around by specifing the "ON" clause to your join(), as the second argument of table1.join(table2, and_(table1.c.foo==table2.c.bar, table1.c.bat==table2.c.hoho, ...)). > I have table A that has a few preassigned primary keys and here's a terminiology problem. A table in relational databases can only have one primary key. The primary key may be composed of multiple columns, in which case its called a "composite primary key". So maybe that is what you're describing here. > non-primary_key column 'userId', which references table B. > I have table B that has an autoincrementing primary key 'userId' and a > userName column is unique. Based on the linked message you wrote > earlier, it seems like what you're now proposing isn't possible in > sqlite. as long as the sqlite table has only one column that is declared as primary key in the CREATE TABLE statement, sqlite can autoincrment. a "userId" column declared as the PRIMARY KEY and a second "userName" column that only has a unique constraint on it does not impact SQLite's autoincrement capability. > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---