How to allow only one row to have a specific field value?

Question

In my Django model there should be only one value that can be the main one while the rest are optional.

In my model:

@with_author 
class BOMVersion(models.Model): 
    name = models.CharField(max_length=200,null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    tomaterial =  models.ForeignKey(Material, related_name = 'tomaterial')

    status  = models.CharField(max_length=1, choices=STATUS_CHOICES)

BOM_CHOICES = (
    ('M','Main'),
    ('A','Additional'),
)

I should be able to populate this model with data such that only one of the rows' status will have the value Main while the rest will always be Additional. (It is totally the Radio button functionality when only value can be selected.)

For example:

1 v1 ...A
2 v2 ...M
3 v3 ...A
.........
n vn ...A

I came up with a workaround where all the values added will always be additional and will have a separate UI with a radio button where the user will select what value should be the main.

But was wondering is there a way to apply this logic to a Django model directly?

I am not aware of a way to add it to the model, asside for testing in the save method. You could place a condition that if this instance status is main, update all other objects in the database to be additional. You need to make sure you don't get into an infinite loop there though. — Shovalt, Apr 29 '16 at 19:09
The functionality of the form elements will have to be implemented with javascript, and preferably validated in a ModelForm object. — Shovalt, Apr 29 '16 at 19:10

Kevin Christopher Henry · Accepted Answer · 2019-02-14T16:52:36.123

3

If your database supports it, a partial unique index is a good approach. In PostgreSQL it would look something like this:

CREATE UNIQUE INDEX bom_constraint ON appname_bomversion (status) WHERE status = 'M';

As of version 2.2 Django supports declarative partial unique indexes. In older versions you need to use migrations. See this answer for more details.

edited Feb 14 '19 at 16:52

answered Apr 30 '16 at 00:08

Kevin Christopher Henry

46,175
7
116
102

Does that really create a constraint that prevents inserting `M` into more than one row? The linked documentation does not indicate that: "The index contains entries only for those table rows that satisfy the predicate. [...] One major reason for using a partial index is to avoid indexing common values." The examples clearly show that they are expecting more than 1 value to be indexed. – Risadinha Nov 11 '16 at 10:42
@Risadinha: The difference here is that the unique column is the same as the predicate. Imagine you tried to add a second row with `status == M`. Since it satisfies the predicate, it would have to be indexed. But trying to add it to the index would violate the constraint that `status` be unique for such rows. – Kevin Christopher Henry Nov 11 '16 at 13:00
Thank you for the explanation! – Risadinha Nov 11 '16 at 15:58

How to allow only one row to have a specific field value?

1 Answers1