Re: is a unique key on null field bad?

Peter Childs wrote:
> On 20/02/2008, Geoffrey <lists(at)serioustechnology(dot)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