I agree that they are very handy. They become a major pain in the butt when you start doing replication between servers. For instance if you fail over to a standby server and you forget to update it's sequence first, merging data later becomes a nightmare. I'd like to have int8 sequences and basically give each server it's own block of numbers to work with. Alex. On Fri, 2 Mar 2001, Gregory Wood wrote: > > IMHO, automatically incremented number fields used for primary keys are > > both a blessing and a curse. It is almost always better to use some > > other data that *means something* for a primary key. If there's no > > possible candidate key, *then* maybe an autonumber key is appropriate. > > Just wanted to say, I disagree strongly here (also MHO). I see quite a few > benefits and very few drawbacks to using an auto-incrementing field for a > primary key. In fact, the only drawback I can think of would be that it > takes up a little more space per record to add a field used solely to > uniquely identify that record. I can think of several drawbacks to a > non-auto-incrementing primary key though: > > 1. Less efficient joins. Comparing integers is about as easy as it gets... > text, char, and varchar require string comparisons, while floating point > numbers are not good as keys because of rounding errors. > 2. Discourages value changes. A value that "means something" might need to > be modified in some manner. Sure you can define foreign keys with CASCADEs, > but if you are using an auto-increment, you don't need to! > 3. No value is guaranteed to be unique (well, when doing an INSERT or > UPDATE... it only gets into the database if it *is* unique) unless all > queries go through a critical section. To the best of my knowledge, the only > way to do this inside the database is to use nextval either implicitly or > explicitly. > > The only time I don't use auto-incrementing fields is when I have a > many-to-many join table with two foreign keys that are both > auto-incrementing fields, in which case the primary key is a combination of > those two fields. Other than a bit of extra space, I don't see any reason > not to. > > Greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]