On Dec 15, 2010, at 5:14 PM, Russell Warren wrote: > Why does Session.merge only look at primary key and not all unique > keys?
Well the theory of operation regarding merge() is based on that of the identity map, which is linked to object/row identity. Consider that it also cascades along relationship paths. It would be a difficult operation to define if it had to choose among multiple ways to determine the "identity" of each object along the cascade chain. > > Leaving aside some irritating DBMS restrictions on PKs and some > automatic indexing that tends to happen, the PK is not fundamentally > different than other unique keys It is fundamentally different in that a database row within a reasonable schema has only one "identity". The usage of surrogate primary keys perhaps pollutes this concept to some degree. > and I don't see why SQLA > distinguishes them from an integrity/relationship perspective. SQLA at the ORM level doesn't really know about any other attributes being "unique" and it would incur excessive complexity to implement that as built-in, where "complexity" here means the bookkeeping associated with storing, retrieving, and modifying items in the identity map would become a much more time consuming affair (for some recent insight into my epic battle with time consumption, see http://techspot.zzzeek.org/2010/12/12/a-tale-of-three-profiles/ ). It also would refer to all those unintended side effects of doing such, such as two objects that are swapping attribute values, etc. The database does a perfectly good job of maintaining UNIQUE constraints so we leave that whole affair out of the Python side. > In > databases where it is already frustrating that they have funky PK > restrictions it is tough to make merge() work the way it seems it > should. For example, in the code below this post, Sqlite requires the > autoincrementing field to be the PK, and you can't composite it with > another field... with these restrictions I can't get merge() to work > the way "it should". > I was looking for a clean way in SQLAlchemy to do an "insert if not > exists" pattern, and merge() looked perfect, but I can't make it work > at the moment. The generic "insert if not exists" pattern that is extensible to whatever attributes you want is at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject . > > I'm also aware that in the sample code the 'name' field should really > just be the "primary" key and the problem goes away, mmm the consensus I've noted for the past several years, as well as with my own experiences, is that we're better off with surrogate primary keys. SQLA does support natural primary keys fully, and note that foreign keys which reference natural primary keys are entirely valid. Mutation of these keys is supported naturally through ON UPDATE CASCADE and ON DELETE CASCADE. But I find myself usually never using them (well actually I did a yearlong project a year ago that was all on natural PKs and it was really not worth it). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
