I think a lot of people here are missing the point. I was trying to give examples of natural keys, but a lot of people are taking great delight in pointing out exceptions to examples, rather than understanding the point. So for the sake of argument, a natural key is something that in itself is unique and the possibility of a duplicate does not exist. Before ANYONE continues to insist that a serial id column is good, consider the case where the number of tuples will exceed a bigint. Don't say it cannot happen, because it can. However, if you have an alphanumeric field, let's say varchar 50, and it's guaranteed that it will never have a duplicate, then THAT is a natural primary key and beats the hell out of a generic "id" field.
Further to the point, since I started this thread, I am holding to it and will not discuss "natural primary keys" any further. Other suggestions for good PostgreSQL Developer database (not web app) guidelines are still welcome. On Tue, Aug 25, 2015 at 7:34 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 08/25/2015 04:23 PM, Jerry Sievers wrote: > >> Adrian Klaver <adrian.kla...@aklaver.com> writes: >> >> On 08/25/2015 01:56 PM, John R Pierce wrote: >>> >>> On 8/25/2015 1:42 PM, Gavin Flower wrote: >>>> >>>>> Account numbers are externally generated, and may potentially change. >>>>> Management might suddenly decide that they want to start using the >>>>> year the account started as the first 4 digits, or that the branch >>>>> code should be reflected in it, or something else. The database >>>>> should be protected from these arbitrary changes. Hence the >>>>> account_no is not a good candidate for a primary key. >>>>> >>>> >>>> >>>> such practices would raise total havoc on a traditional paper ledger >>>> accounting system as well as things like pending AR/AP where external >>>> companies will be referencing your account numbers. >>>> >>> >>> Agreed, but it happens. When Lowes took over a local hardware >>> chain(Eagles) here in Washington state they moved very quickly on >>> changing the account numbers. The company I worked for who supplied >>> Eagles and then Lowes sat on a check for $22,000 that was sent to us >>> in error because the account numbers got switched. We called them when >>> we got the check, but it still took them six months to own up to it. >>> >> >> DOH! >> >> Next time a screwball outfit sends you a check for $22k erroneously just >> go deposit it :-) >> > > Well that is what I wanted to do, the owner overruled me:(. Something > about Lowes having more lawyers then we did. The strange part was we called > them and told them what had happened and supplied the relevant information > that explained the mix up. You would have thought us calling to return a > check that was supposed to be to us would have raised a flag! > > >> -- >>> Adrian Klaver >>> adrian.kla...@aklaver.com >>> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
