On Sat, Sep 12, 2009 at 12:35 PM, Grant Maxwell <grant.maxwell@xxxxxxxxxxxx> wrote: > Hi Folks > > I'm looking for a bit of advice regarding alpha primary keys. > > I have a table (designed by someone else) that has a numeric primary key and > also a unique non-null email address field. > > The use of the primary key is causing me some headaches in that in multiple > database server environment each server allocates a unique number from a > range > (and that works fine) but when the table is replicated > (master-master-master) the exception handling is a bit tricky because each > database server may have > records that are duplicate at the email address field - with a different > primary key number. > > I don't know why it was done this way but it seems to me that the email > addresses are unique, non null and could be used as the primary key. This > would make the > replication much faster and simpler. > > Does anyone out there think the change (number to email address as primary > key) would be a bad idea ? > > your thoughts would be appreciated. I think it's a fine idea so long as you understand: *) the index(es) will be larger *) referring tables will have to be updated (via RI) if/when the email address changes *) lots of people will tell you that you are doing it wrong :-) *) if you need to specialize p-key further (add another field), it can be a big change on the plus side: *) you get to drop an index because you obviously had to index the key serparately *) if you query a referring table and are interested in email address (and no dependent props), you get to skip a join *) sorting be email address can be free *) less data transfer headaches merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
