django postgres conditional constrain

Question

I'm trying to set a conditional unique constrain to one of my model field (column on db perspective) with respect to another boolean field but can't find a way to do it.

I'm creating a web management tool, to aid/control custumers atendance, based on numbered cards while they are still on the store. My main objective is to prevent my user to save an card number while this number is still active (aka boolean field) with another custumer.
Something like this on models.py:

    class Cards(models.Model):
         card_number = models.PositiveIntegerField("""{{RESTRICTION NEEDED}}""")
         card_date = models.DateTimeField(auto_now=False, auto_now_add=True)
         custumer = models.ForeignKey(Customer)
         status = models.BooleanField(default=False)

maybe something like this would work directly on postgres, didn't tried yet

CREATE UNIQUE INDEX card_number_ind ON Cards (card_number) WHERE (status is False);

Any chance to do it directly with Django? I mean set a conditional unique constrain with respect to a boolean field (status) on Django?

TIA
Obs.This is my first question ever on stackoverflow, feel free to criticize on style.

You shouldn't do this in models.py, you should separate logic and data as much as you can. Do you use a form or something to save a card number? — Shang Wang
– Shang Wang, Commented Feb 3, 2016 at 20:37
You may modify save() method or set a model validation (if you are using a form) for checking the status. — Yaaaaaaaaaaay
– Yaaaaaaaaaaay, Commented Feb 3, 2016 at 20:42
@Shang Wang It's not really logic though- the database schema should correctly model the data. Just as you would want to ensure certain columns are unique in a regular database (e.g. user's email), in Felicio's case, card_number should be unique where status is False. With the constraint, it guarantees that the database will never have identical card_number where status is False. — Derek Kwok
– Derek Kwok, Commented Feb 3, 2016 at 20:53
@ShangWang Yup, form it is. I'll try Derek's solutions and report afterwards. — Felício
– Felício, Commented Feb 4, 2016 at 11:11
Yup, Derek's solution meets the restriction I needed. Thanks! — Felício
– Felício, Commented Feb 4, 2016 at 11:16

Derek Kwok · Accepted Answer · 2016-02-03 20:45:48Z

5

Django doesn't support defining conditional constraints in models.py, however you can create a migration to add such a constraint.

Start by creating an empty migration

./manage.py makemigrations appname --empty

This will create an empty migration file. Next, you'll want to add your custom constraint to the migration

class Migration(migrations.Migration):

    ... 

    operations = [
        migrations.RunSQL('create unique index card_number_ind on cards (card_number) where (status is false)'),
    ]

answered Feb 3, 2016 at 20:45

Derek Kwok

13.1k7 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rrauenza · Accepted Answer · 2020-10-07 21:27:35Z

4

Starting in Django 2.2 there is now a UniqueConstraint declaration available that has a condition option.

condition

UniqueConstraint.condition

A Q object that specifies the condition you want the constraint to enforce.

For example:

UniqueConstraint(fields=['user'], condition=Q(status='DRAFT'), name='unique_draft_user')

ensures that each user only has one draft.

answered Oct 7, 2020 at 21:27

rrauenza

7,1434 gold badges39 silver badges65 bronze badges

2 Comments

Sandeep Balagopal Over a year ago

is it available for non unique cases ? Like i want to make a field required when a certain value is there in another field.

rrauenza Over a year ago

@SandeepBalagopal Yes, you can create complex constraints as well. You also may be interested in django -pgtriggers.

Collectives™ on Stack Overflow

django postgres conditional constrain

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related