Re: Using unique_together with foreign key fields which may be None

Well, I may have answered my own question.  It looks like per the SQL standard NULL values are unique.  Here from the postgres docs:

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases may not follow this rule. So be careful when developing applications that are intended to be portable.

So it looks like django is doing the right thing although it seems pretty unintuitive.  It looks like I may have to modify the models afterall.

On Monday, September 30, 2013 1:55:11 PM UTC-7, Berndt Jung wrote:

Hi,

I'm having trouble using the unique together constraint with a a field which may be None.  Given this model:

class Rule(models.Model):

    internal = models.BooleanField(default=False)

    port_range_from = models.PositiveIntegerField(null=True, blank=True)

    port_range_to = models.PositiveIntegerField(null=True, blank=True)

    # either cidr_ip or src_group for ingress rules

    cidr_ip = IPNetworkField(blank=True, null=True)

    is_ingress = models.BooleanField(default=True)

    # a security group is used as src in this rule

    src_security_group = models.ForeignKey(

        SecurityGroup, to_field='uuid', null=True, blank=True)

    class Meta:

        app_label = "nomos"

        unique_together = (

            ('port_range_from', 'port_range_to', 'cidr_ip', 'src_security_group', 'is_ingress'),

        )

Now in this instance src_security_group may be None OR cidr_ip may be None.  In this case they are mutually exclusive.  Regardless I want to use the unique_together to ensure that no two identical rules are created.  However, in the django models/base.py code the following logic is in _perform_unique_check:

lookup_kwargs = {}

            for field_name in unique_check:

                f = self._meta.get_field(field_name)

                lookup_value = getattr(self, f.attname)

                if lookup_value is None:

                    # no value, skip the lookup

                    continue

                if f.primary_key and not self._state.adding:

                    # no need to check for unique primary key when editing

                    continue

                lookup_kwargs[str(field_name)] = lookup_value

            # some fields were skipped, no reason to do the check

            if len(unique_check) != len(lookup_kwargs):

                continue

Because the lookup_value of a null field is None, the validation check is aborted entirely.  This seems wrong to me, and I'm wondering if I'm doing something wrong here.  Redefining the models is not something I can do at this point.

Berndt

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f5710e1c-04ca-4e9c-aa13-0ec756b3a40a%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.