On Jul 30, 2014, at 12:28 PM, Rich Shepard <rshep...@appl-ecosys.com> wrote:
> On Wed, 30 Jul 2014, Michael Bayer wrote: > >> Celko's books are great but surrogate integer PKs are an unavoidable >> practice within relational databases, they are a requirement of most DBAs >> I've dealt with as they perform predictably in terms of indexing and space >> requirements, especially considering that a PK implies the format of all >> the FKs that will refer to it. Typically a UNIQUE constraint is placed on >> the "natural" key to prevent dupes. > > Mike, > > That's interesting. I've not had any issues, but I've not developed many > multi-user, large databases. > >> In my own experience we actually tried using meaningful UUIDs as primary >> keys in a project some years ago and it was an utter disaster. All PK / FK >> indexes quadrupled in space and performance suffered terribly. This was on >> a Postgresql backend which should have been a better performer in a >> non-standard context like that (on a big ol' DB like SQL server, forget >> it). > > Wonder if that's been improved in later versions. > > So, do you recommend that surrogate keys be used in all tables, or only on > those that meet certain criteria? I'm always open to learning new things and > improving the work I do. I find surrogate PKs very easy to deal with in that I never have to worry about UPDATE CASCADE situations, some of which are thorny enough that even SQLAlchemy doesn't have a 100% approach for every situation. With that, plus the predictable indexing, I'm always going to use them. But, I think there's a fair degree of preference still here. With natural PKs, the biggest issue is how much space indexes are going to take up considering that everything that FKs to that PK has to mirror out those same columns. Just on those occasions I've had to deal with the Trac database, which uses natural primary keys, for every table it's a mystery how to join things, b.c. every table has a different notion of "primary". But then again they also have pretty inconsistent conventions. I think natural PKs just complicate the natural plumbing of being able to wire rows together, they are of course more "pure" but in relational DBs we are already seriously impure compared to Codd, RDBMS draws from relational algebra to the point that is practical and that's about it. You of course will always have a natural key and hopefully adequate constraints on it. -- 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/d/optout.
