Re: is a unique key on null field bad?

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)

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.