Using unique_together with foreign key fields which may be None

[Berndt Jung]

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

[Berndt Jung]

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.

[Karen Tracey]

On Mon, Sep 30, 2013 at 4:55 PM, Berndt Jung <ber...@brkt.com> wrote:

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.

None in Python corresponds to NULL in SQL. SQL defines that NULL != NULL. So when checking for unique values the database will not consider two NULLs to be the same value; the NULLs will be considered to be different and will not violate the unique constraint. The Django code is doing the same check for uniquess that the database will be enforcing. There is no way I know of to make a database enforce uniqueness among NULL values...

[Derek]

We encountered the same problem for a complex model ... we ended up defining "acceptable" default values that are used instead of None/NULL, but essentially mean the same thing to the user.  (We are using MySQL which adopts the same approach as Postgresql - I believe Oracle does it otherwise).

[Thomas Lockhart]

On 10/1/13 6:36 AM, Derek wrote:

We encountered the same problem for a complex model ... we ended up defining "acceptable" default values that are used instead of None/NULL, but essentially mean the same thing to the user.  (We are using MySQL which adopts the same approach as Postgresql - I believe Oracle does it otherwise).

(Just meant for general discussion, not specific to other suggestions in this thread)

This would seem to be a problem that explicit constraints are meant to solve. One could check that the row is unique either with or without the foreign key, depending on whether it is specified. Postgres supports those very well (others may also nowadays), and if necessary one could use python or another language supported by the backend to implement complex logic.

That does not solve the validation problem at the user level, but keeps invalid data from leaking into the database.

hth

                               - Tom

On Monday, 30 September 2013 23:17:00 UTC+2, Karen Tracey wrote:

On Mon, Sep 30, 2013 at 4:55 PM, Berndt Jung <ber...@brkt.com> wrote:

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.

None in Python corresponds to NULL in SQL. SQL defines that NULL != NULL. So when checking for unique values the database will not consider two NULLs to be the same value; the NULLs will be considered to be different and will not violate the unique constraint. The Django code is doing the same check for uniquess that the database will be enforcing. There is no way I know of to make a database enforce uniqueness among NULL values...

--
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...@googlegroups.com.
To post to this group, send email to django...@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/75c02ade-18a4-4e38-a75c-f75b756f831f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.