CODEWITHSUNDEEP
pythonsqldjango
simonwjackson
simonwjackson

Reputation: 1878

Django: Foreign key optional but unique?

I have a model (A) that has 2 foreign keys: b and c.

b and c should unique together INCLUDING NULL

therefore b and c should only be NULL together ONCE

However, I am unable to accomplish this in Django. Here is the code I have so far. Any help is appreciated!

-_-

class A(models.Model):
  b = models.ForeignKey('B', blank = True, null = True)
  c = models.ForeignKey('C', blank = True, null = True)

  class Meta:
    unique_together = (
      ('b', 'c')
    )

This code will produce this unwanted result in the database:

+----+------+------+
| id | b_id | c_id |
+----+------+------+
|  1 |    2 | 3    |
|  2 |    2 | 77   |
|  3 |    2 | NULL |
|  4 |    2 | NULL |
|  5 | NULL | NULL |
|  6 | NULL | NULL |
+----+------+------+

The first 2 rows can only be inserted once by django. which is great :)

However, the remaining rows are duplicate entries for me, and i'd like to restrict this.

UPDATE

I've found something that gets the job done, but it seems really hacky.. Any thoughts?

class A(models.Model):
  def clean(self):
    from django.core.exceptions import ValidationError

    if not any([self.b, self.c]):
      if Setting.objects.filter(b__isnull = True, c__isnull = True).exists():
        raise ValidationError("Already exists")

    elif self.b and not self.c:
      if Setting.objects.filter(c__isnull = True, b = self.b).exists():
        raise ValidationError("Already exists")

    elif self.c and not self.user:
      if Setting.objects.filter(c = self.c, b__isnull = True).exists():
        raise ValidationError("Already exists")

Upvotes: 0

Views: 693

Answers (3)

OBu
OBu

Reputation: 5187

Maybe there is a better solution out there, but you could do the following:

  1. create a new attribute d and find a generic way to combine b_id and c_id (e.g. str(b_id) + "*" + str(c_id) and do this automatically on model creation (the signals mechanism might come in handy, here)
  2. use d as primary_key

This is more a work around then a solution, but it should do the trick.

One more thought: Would it be an option to check whether there is aready an existing instance with "Null"/"Null" on creation / update of your instance? This would not solve your problem on database level, but the logics would work as expected.

Upvotes: 1

bruno desthuilliers
bruno desthuilliers

Reputation: 77912

It's not a problem with Django but with the SQL spec itself - NULL is not a value, so it must NOT be taken into account for uniqueness constraints checks.

You can either have a "pseudo-null" B record and a "pseudo-null" C record in your db and make them the defaults (and not allow NULL of course), or have a denormalized field like OBu suggests.

Upvotes: 1

Nagarajan
Nagarajan

Reputation: 432

You can use the Unique constraint for b_id column. It wont allow the duplicate entries. Even for a_id column, primary key can be used. Primary key means the combination of unique key and not null constraints.

Upvotes: -1

Related Questions