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.