Re: is a unique key on null field bad?
От | Geoffrey |
---|---|
Тема | Re: is a unique key on null field bad? |
Дата | |
Msg-id | 47BC4615.4010800@serioustechnology.com обсуждение исходный текст |
Ответ на | Re: is a unique key on null field bad? ("Peter Childs" <peterachilds@gmail.com>) |
Список | pgsql-general |
Peter Childs wrote: > On 20/02/2008, Geoffrey <lists@serioustechnology.com> wrote: >> So, we are trying to track down some problems we're having with an >> implementation of slony on our database. I've posted to the slony list >> about this issue, but I wanted to get a more generic response from the >> perspective of postgresql. >> >> Is it a 'bad thing' to have a unique key on a field that is often times >> null? This application has been running along just fine for a couple of >> years now, but when we try to implement a slony replication solution, >> this one table consistently has inconsistent data between the primary >> node and the slave. >> >> The problem we are having with slony seems to be related to a table that >> has just such a key, so we are trying to figure out if this is causing >> the problem. >> >> > Its not a problem as such, but it will not exactly be unique as there could > be multiple records with null values in that table. So it can't be the > primary key, (Hence why Slony has a problem) We aren't using this as the primary key, so would this still pose a problem for slony? (indexes on this table) Indexes: "tract_pkey" primary key, btree (recid) "tract_order_num_key" unique, btree (order_num) "tract_assigned" btree (assigned) "tract_code" btree (code) "tract_comments" btree (comments) "tract_compound_1" btree (code, old_order_num) "tract_date_avail" btree (date_avail) "tract_dest_state" btree (dest_state) "tract_dest_zone" btree (dest_zone) "tract_driver" btree (driver) "tract_orig_state" btree (orig_state) "tract_orig_zone" btree (orig_zone) "tract_prebooked" btree (prebooked) "tract_tractor_num" btree (tractor_num) "tract_trailer_num" btree (trailer_num) > However it you want to ensure that the field is either Unique or Null (ie > not known) then this is a good way of doing it for example with Car Number > Plates where the details are not known yet but must be unique once they are > known... -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
В списке pgsql-general по дате отправления: