2

In my model:

class MyModel(models.Model):
  active = models.BooleanField()
  path = models.CharField(max_length = 512)

I would like to constrain that the 'path' attribute is unique amongst instances where active is True.

It seems like overriding save() might not work, as if two saves are done concurrently, both might pass the test and go ahead and save. Is there some trick or custom SQL (I'm using MySQL) I could use to have a conditional uniqness constraint like this at the database level?

kdt
  • 27,905
  • 33
  • 92
  • 139

2 Answers2

2

What you are probably looking for is Model Validation. This was introduced in 1.2, and is quite well documented. Django Models Instances : Validating Objects

Whilst you may be able to write some tricky SQL to do this, as long as the database rows are all created through the Django ORM, then model validation is the more maintainable solution.

Aside: I'm interested in how you'd do it at the database level, short of having some level of pre-commit trigger. Uniqueness of one value dependent upon another being true is something I haven't seen a pattern for. Having a 2-field unique constraint on active+path would allow 1 false, 1 true and any number of NULL active rows for a given path value (assuming active is nullable, such as a NullBooleanField would provide).

Matthew Schinckel
  • 35,041
  • 6
  • 86
  • 121
  • Hmm, the validation stuff seems like it's equivalent to overriding save() (albeit neater when using forms). However, nulling the field instead of using True/False is an interesting idea. I guess I could have an 'inactive' field which is True or None and then unique_together (path, inactive), yeah? – kdt Sep 07 '11 at 11:39
  • You could. I'm not sure if django applies the unique_together constraint correctly - it probably does, but I'd want to check. The only reasons I would suggest against doing that is that I'm a bit of a puritan about NULL values, and how they semantically mean something different to False. Oh, and the django admin seems to abhor NULL values, and replaces them with empty strings. – Matthew Schinckel Sep 07 '11 at 11:42
  • Let me be clear: I love NULL values, just hate when they aren't used as NULL. – Matthew Schinckel Sep 07 '11 at 11:42
  • "Uniqueness of one value dependant [sic] upon another being true is something I haven't seen a pattern for" -- I've seen it and it is better described as an antipattern. Think of a row that needs to be deleted but the designer is paranoid about data loss. So, instead of 'physically' deleting the row, it is 'logically' deleted by setting its `active` attribute to FALSE. Then comes the requirement to be able to reuse the key value of a 'deleted' entity, hence the question… – onedaywhen Sep 07 '11 at 13:04
  • …It's an anitpattern because all queries written against the table must remember to filter `active = TRUE` because no one is really interested in 'deleted' rows and of course this leads to bugs when coders forget. This can be ameliorated using `VIEW`s to expose only the 'active' rows but the better solution is to use a trigger to copy the row to an 'audit' table when the row is 'physically' deleted. – onedaywhen Sep 07 '11 at 13:04
  • If one can't change the design, a way of implementing the key in SQL is to use a variation on the ['null buster'](http://stackoverflow.com/questions/191421/how-to-create-a-unique-index-on-a-null-column/191729#191729) trick, perhaps in a `VIEW`. – onedaywhen Sep 07 '11 at 13:09
  • @onedaywhen: I agree with what you have said. Having said that, if all database access goes through the ORM (and, under most circumstances, it should), then that will handle the 'coders forget' part. That is, the validation will prevent storing of multiple identical values. However, for the reasons you outlined, it is still a dirty method. What if you try to undelete, for instance. How should that be handled? In those cases, a separate audit table (perhaps with serialised values) may be the best solution. – Matthew Schinckel Sep 08 '11 at 01:14
  • @onedaywhen: If a customer has a phone number and that number changes, the number might have communication records that were against the old number. In this case the `active=False` means the number is no longer active (don't message it) while the `active=True` means this number is the one to message, but it doesn't fake the historical records onto the new number that did not exist when the other number was created. Yet you can still lookup all communication to a customer thru a simple join. – boatcoder Mar 10 '22 at 17:30
  • @boatcoder: IME almost always best to move the old number to an archive table. Far more user journeys will require the current number. – onedaywhen Jul 01 '22 at 07:08
  • @onedaywhen If you move it to another table, how do you deal with Foreign Keys? Keep 2 of them in the communications table for current and archived? I've never found separate tables to work well when you need FK references. Easier to have an active/not active flag or better yet (deactivated_at) flag that stores when the number was replaced so you have some idea of what happened when. – boatcoder Jul 21 '22 at 23:50
  • @boatcoder: add a column for sequence number? Make it a temporal table with start and end date columns? It depends on how the data will be accessed. – onedaywhen Oct 18 '22 at 15:23
1

Accidentally came across this whilst looking for something else, but for anyone else who ends up here, this is quite simple in Django >= 2.2 (which came out long after this question was asked) thanks to UniqueConstraint.

from django.db.models import Q, UniqueConstraint

class MyModel(models.Model):

    active = models.BooleanField()

    path = models.CharField(max_length=512)

    class Meta:
        constraints = [UniqueConstraint(fields=["path"], condition=Q(active=True)]
Kye
  • 4,279
  • 3
  • 21
  • 49