On Wed, Aug 26, 2015 at 5:23 AM, rob stone <floripa...@gmail.com> wrote:
> On Tue, 2015-08-25 at 20:17 -0400, Melvin Davidson wrote: > > 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. > > > > Funny how Melvin's attempt to bring order to the chaos ended up as a > discussion about primary keys. > > We once hired a "genius" to design an application to handle fixed > assets. Every table had a primary key named "id". Some were integer and > some were character. So the foreign key columns in child tables had to > be named differently. Writing the joins was complex. > > I also know of an airline reservation system where you are unable to > alter your e-mail address. It apparently needs a DBA type person to > make the change. I can only guess that your e-mail address is used as a > foreign key in one or more tables. As well as assigning you a frequent > flyer number they also assign another integer identifier. A bit of > common sense goes a long way when designing an application. > > Cheers, > rob > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > I am in favour of using BIGINT "id" for the primary key in each table I create. I found out that in the fields in my tables that I thought would be unique end up not being so in the longer term. Also these values may need to be updated for some reason. I have been using PRIMARY KEY(id) where id is of type BIGINT on each table I create. I use a sequence to provide a default value to this field. I create one such sequence DB object per table and the use it in the table definition. For example if I have a sequenceDB "some_schema.some_table_seq" for table "some_schema.some_table". In the table definition of "some_schema.some_table" I have the field "id" as follows. id BIGINT NOT NULL DEFAULT NEXTVAL('some_schema.some_table_seq') When I use this "id" field as a foreign key in another table, I would prefix it with the name of its parent table followed by a couple of underscores as shown below. FOREIGN KEY(some_table__id)REFERENCES some_schema.some_table(id)ON UPDATE CASCADE ON DELETE CASCADE For the composite keys that are unique (for now) I create a unique constraint. Allan.
