Multiple AutoFields in a model

[honeyman]

Got the assertions failed recently:

  File "C:\Projects\houserules\www\hrdb\events\hrpercustomerobject.py", line 14, in <module>
    class HRPerCustomerObject(models.Model):
  File "C:\Projects\houserules\www\hrdb\events\hrpercustomerobject.py", line 37, in HRPerCustomerObject
    id_percustomer = models.AutoField ( 'Per-customer ID' )
  File "C:\Projects\houserules\www\django\db\models\fields\__init__.py", line 425, in __init__
    assert kwargs.get('primary_key', False) is True, "%ss must have primary_key=True." % self.__class__.__name__
AssertionError: AutoFields must have primary_key=True.
make: *** [_reset] Error 1

and

  File "C:\Projects\houserules\www\django\db\models\options.py", line 117, in _prepare
    model.add_to_class('id', auto)
  File "C:\Projects\houserules\www\django\db\models\base.py", line 139, in add_to_class
    value.contribute_to_class(cls, name)
  File "C:\Projects\houserules\www\django\db\models\fields\__init__.py", line 459, in contribute_to_class
    assert not cls._meta.has_auto_field, "A model can't have more than one AutoField."
AssertionError: A model can't have more than one AutoField.
make: *** [_reset] Error 1

Depending on the logic and the scenario, it may be really worthy to have several AutoFields in a model (for example, when one or both of them in some cases is controlled manually), one of which will definitely not be a primary key.
I roughly described one of possible scenarios in ​IRC, though this may be not the only one.
Please note that the databases itself do not imply such behaviour (auto_increment field in MySQL does not imply a primary key, neither SERIAL field in PostgreSQL does), so it is unsafe and overrestrictive to add such a limitation in Django.
The existing documentation on AutoField also does not imply it should be used for primary keys only.

My proposal is to withdraw such a limitation; seems that it is only required to remove two assert lines from the code�(well, and the related tests, of course).

[honeyman]

Multiple AutoField-s

[Ramiro Morales]

reformatted description

[newspire@…]

MySQL does not allow multiple auto increment fields.

ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key

[Karen Tracey]

Replying to newspire@gmail.com:

MySQL does not allow multiple auto increment fields.

ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key

So in fact you can't have multiple AutoFields in MySQL. A little experimenting with removing the assertions and trying this model:

class MAuto(models.Model):
    auto1 = models.AutoField(primary_key=True)
    auto2 = models.AutoField()
    name = models.CharField(max_length=22)

shows problems on other backends as well. On both Oracle and sqlite you get an error complaining that auto2 cannot be null if you try MAuto.objects.create(name="whatever"):

>>> MAuto.objects.create(name="First")
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "d:\u\kmt\django\trunk\django\db\models\manager.py", line 99, in create
    return self.get_query_set().create(**kwargs)
  File "d:\u\kmt\django\trunk\django\db\models\query.py", line 349, in create
    obj.save(force_insert=True)
  File "d:\u\kmt\django\trunk\django\db\models\base.py", line 328, in save
    self.save_base(force_insert=force_insert, force_update=force_update)
  File "d:\u\kmt\django\trunk\django\db\models\base.py", line 400, in save_base
    result = manager._insert(values, return_id=update_pk)
  File "d:\u\kmt\django\trunk\django\db\models\manager.py", line 144, in _insert
    return insert_query(self.model, values, **kwargs)
  File "d:\u\kmt\django\trunk\django\db\models\query.py", line 1004, in insert_query
    return query.execute_sql(return_id)
  File "d:\u\kmt\django\trunk\django\db\models\sql\subqueries.py", line 310, in execute_sql
    cursor = super(InsertQuery, self).execute_sql(None)
  File "d:\u\kmt\django\trunk\django\db\models\sql\query.py", line 1935, in execute_sql
    cursor.execute(sql, params)
  File "d:\u\kmt\django\trunk\django\db\backends\util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "d:\u\kmt\django\trunk\django\db\backends\oracle\base.py", line 371, in execute
    raise e
IntegrityError: ORA-01400: cannot insert NULL into ("SYSTEM"."MULTIAUTO_MAUTO"."AUTO2")

>>>

So whatever mechanisms those backends are using to implement an auto-assigned auto-incrementing value don't appear to work if primary_key is not set to True for an AutoField.

It would seem more needs to be done to make multiple AutoFields work across all supported backends than simply removing the two assertions. I'm inclined to close this wontfix but I'm also unclear on the scenario for when this is useful, as I can find no mention of AutoField on the referenced IRC page, so I'll just put it back to DDN. The original description mentions manual control of one or both of the fields, but that contradicts my understanding of how AutoField values are automatically assigned...?

[Malcolm Tredinnick]

Without a compelling use-case, this isn't worth doing. One auto-incrementing field per model is almost always going to be enough (everything else can be derived from the single source, e.g, in the save() method). Whilst the comments about auto-increment fields not being required to be primary key, etc, are valid, Django's AutoField isn't just an auto-increment field. It's also a primary key field and all the other goodies. So there's two ways of looking for that.

If there's some really strong use-case that's been overlooked here, it can be brought up in a thread on django-dev. But "just because we can" isn't a reason to do something.

[seanmonstar]

while some backends can't support, at the least, PostgreSQL can. You can assign multiple values as "serial", only one needs to be primary key.

And a use case where I've used (has to do with ordering):

Say I have a bunch of pages in a CMS. The primary auto field would be the id. But I also want you to be able to change the order they show up in. By default, the ordering should be as you create them. Meaning my page_order column also can't have null values, but should all be a sequential number. And it needs to be a different number than the id, since the ID doesn't ever change.

It seems possible to check if the backend picked was one that works (Postgres) and if so, not assert primary_key on the AutoFields. On backends where the implementation wouldn't work, you can toss an error. I shouldn't be stuck with MySQL functionality if I can use PostgreSQL. :)

[Karen Tracey]

As mentioned in the comment when this was closed, the way to get the decision reconsidered is to bring it up on the developer's mailing list.

[maccabee]

I see this is closed, just adding another perspective.

I'm looking for the same functionality as seanmonstar: an order column that is normally creation-order but can be changed. One workaround I'm considering is to use a DateTimeField with auto_now_add=True. However this is space-wasteful (my values should really be 1-20ish rather than a long int) and more importantly, it is limited by the precision of the time component, which MySQL seems to limit to one second. That's fine for user input speed but not for data import of multiple records.

[logik]

Know this is closed, but again just to provide perspective.

I could really have used this feature actually. I have a project with existing data where they use as an ID an auto-increment (just like Django's default id). However, they also use a different no_header. Sometimes they create revision on their object, so basically their db ends up looking like this:

id no_header suite
1 1 0
2 2 0
3 1 1
4 3 0
......

So essentially, the no_header is an auto-increment, that only increments on the creation of new objects (with suite=0). If an object undergo some specific operation, then its no_header remains the same, but suite += 1.

Which means that the no_header ends up running "behind" the id field, but it still needs to be an auto-increment. Of course I can override save() & do all that business there, but it would still be so much simpler to just have 2 auto-increment fields. IMO this is a conception flaw in the database backends - e.g there isn't an obvious reason to limit auto increment to a single field, if a separate attributes allows specifying what is a primary key. But I digress. Mostly wanted to illustrate a use case.

[Csirmaz Bendegúz]

#373
I'm looking to implement composite primary keys like this:

class Tenant(models.Model):
    pass


class User(models.Model):
    tenant = models.ForeignKey(Tenant, on_delete=models.CASCADE)
    id = models.SmallAutoField()

    class Meta:
        primary_key = ("tenant_id", "id")

In order to encourage using surrogate keys in composite primary keys, it would make a lot of sense to allow non-primary key auto fields.

We have the following options:

1. Remove the limitation for databases that support non-primary auto fields altogether. We could allow non-primary key auto fields for PostgreSQL, for example.
2. On top of (1), refactor auto fields so that they are either primary keys OR part of a composite primary key. This adds a further limitation to address my use case, but not the others.
3. Don't use auto fields in composite primary keys. We need an alternative in this case (e.g. SERIAL in contrib.postgres #27452).

IMO either (1) or (3) (or both) are a good solution.
I'm not sure about (2) - it could be argued since auto fields are backed by IDENTITY fields, it makes sense for them to be primary keys semantically.

[Natalia Bidart]

Related ​forum post.