How can I use auto increment in my composite unique key in Django?
<p>I have a model class like this:</p>

<pre><code>class TestModel(models.Model):
    class Meta:
        unique_together = ('prefix', 'number')

    prefix = models.CharField(max_length=30)
    number = models.IntegerField()
</code></pre>

<p>prefix and number together form a natural key for my model, but I don't particularly care what the number is. Thus, auto increment (not per-prefix) would be fine. I don't want to have to specify the number, but I want to be able to (e.g. because there are existing prefix+number pairs that need to put into this system).</p>

<p>For eaxmple, most times I'd just want to use auto increment:</p>

<pre><code>'the_prefix', 1
'the_prefix', 2
</code></pre>

<p>However, I might want to use particular (prefix, number) combination in a few cases, such as (prefix, number) pairs from a legacy system:</p>

<pre><code>'manually', 2  # number is not unique alone...
'manually', 4  # ... or sequential
</code></pre>

<hr>

<p>As I understand it,</p>

<ul>
<li>only Single-column PKs are supported, so I can't use prefix+number as a composite PK directly;</li>
<li>only AutoField is auto-incrementing, and there can be only one AutoField per model (and it must be the PK), so I can't have both id &amp; number auto incremented; and</li>
<li>instead making prefix+id unique together makes no sense, as id is already unique on its own.</li>
</ul>

<p>Is there a way to achieve this (or is one of my statements wrong)? If there is a database-agnostic solution, that would of course be preferred, but otherwise, SQLite (development) and MySQL (production) are most important to me personally.</p>

<p>Workarounds are also welcome - I'm trying to use the PK as "default" for number, but</p>

<ul>
<li>keeping number blank to specify the default defeats the unique constraint, and</li>
<li>to copy the id, one needs to save the object without a number first. I struggle to implement this in a way that seems reliable and transparent to the user</li>
</ul>

<p>Here is a proof-of-concept approach, it's by no means production ready, but it seems to demonstrate the heavy lifting necessary to achieve this. I don't claim that's a clean or good way.</p>

<p>Anyway, here it is:</p>

<pre><code>class Subquery(str):
    query = None


class MyIntegerField(models.IntegerField):
    def get_prep_value(self, value):
        value = super(models.IntegerField, self).get_prep_value(value)
        if value is None:
            return None
        if isinstance(value, models.QuerySet):
            subquery = Subquery()
            subquery.query = '({} WHERE %s = "")'.format(value.query)
            return subquery
        return int(value)

    def get_placeholder(self, value, compiler, connection):
        if isinstance(value, Subquery):
            return value.query
        return "%s"


class TestModel(models.Model):
    class Meta:
        unique_together = ('prefix', 'number')

    prefix = models.CharField(max_length=30)
    number = MyIntegerField()

    def __str__(self):
        return "{}: {} {}".format(self.id, self.prefix, self.number)
</code></pre>

<p>Now you can do this:</p>

<pre><code>In [1]: from app.models import TestModel

In [2]: from django.db.models import *

In [3]: next_number = TestModel.objects.all() \
   ...:     .annotate(max=Func(F('number'), function='MAX')) \
   ...:     .annotate(result=Case(When(max=None, then=0), default='max') + 1) \
   ...:     .values('result')

In [4]: str(next_number.query)
Out[4]: 'SELECT (CASE WHEN MAX("app_testmodel"."number") IS NULL THEN 0 ELSE MAX("app_testmodel"."number") END + 1) AS "result" FROM "app_testmodel"'

In [5]: TestModel(prefix='the_prefix', number=next_number).save()

In [6]: TestModel(prefix='the_prefix', number=next_number).save()

In [7]: TestModel(prefix='manually', number=2).save()

In [8]: TestModel(prefix='manually', number=4).save()

In [9]: TestModel.objects.all()
Out[9]: [&lt;TestModel: 1: the_prefix 1&gt;, &lt;TestModel: 2: the_prefix 2&gt;, &lt;TestModel: 3: manually 2&gt;, &lt;TestModel: 4: manually 4&gt;]
</code></pre>

<p>How it works:</p>

<ul>
<li>Before being passed into a query, the field value is processed with <code>get_prep_value</code></li>
<li>We use this to replace <code>QuerySet</code> values by the SQL that evaluates them. To make the query pass, the "field value" must still translate to a SQL-valid value, which is why <code>Subquery</code> is a subclass of <code>str</code> - to the database, a <code>Subquery</code> is an empty string.</li>
<li>The subquery is used as part of the <code>INSERT</code> query via <code>get_placeholder</code>. The placeholder must consume the field value; in this draft, a <code>WHERE %s = ""</code> is appended to the subquery, which of course does <em>not</em> work for all possible queries.</li>
<li>The query used in this situation (<code>next_number</code>) computes the maximum <code>number</code> and returns that plus 1, i.e. auto-increment</li>
</ul>

<p>The result is a query like this:</p>

<pre><code>INSERT INTO app_testmodel(..., number, ...) VALUES(..., (SELECT ...), ...)
</code></pre>

<p>Working from there, it should be easy to implement a custom <code>AutoField</code>, thus solving the problem.</p>

python mysql django database sqlite