is a unique key on null field bad?

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)

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...

Regards

Peter.

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