Code

Opened 6 years ago

Closed 5 years ago

Last modified 5 years ago

#8576 closed (wontfix)

Multiple AutoFields in a model

Reported by: honeyman Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: multiple autofield
Cc: Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description (last modified by ramiro)

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).

Attachments (1)

multiple_autofields.diff (1.4 KB) - added by honeyman 6 years ago.
Multiple AutoField-s

Download all attachments as: .zip

Change History (9)

Changed 6 years ago by honeyman

Multiple AutoField-s

comment:1 Changed 6 years ago by ramiro

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

reformatted description

comment:2 Changed 6 years ago by ElliottM

  • Keywords multiple autofield added
  • Triage Stage changed from Unreviewed to Accepted

comment:3 follow-up: Changed 5 years ago by 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

comment:4 in reply to: ↑ 3 Changed 5 years ago by kmtracey

  • Triage Stage changed from Accepted to Design decision needed

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...?

comment:5 Changed 5 years ago by mtredinnick

  • Resolution set to wontfix
  • Status changed from new to closed

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.

comment:6 Changed 5 years ago by seanmonstar

  • Has patch unset
  • Resolution wontfix deleted
  • Status changed from closed to reopened

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. :)

comment:7 Changed 5 years ago by kmtracey

  • Resolution set to wontfix
  • Status changed from reopened to closed

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.

comment:8 Changed 5 years ago by 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.