Allow AutoFields in composite primary keys

[Csirmaz Bendegúz]

This is a follow up to #373 (CompositePrimaryKey), #8576 (Multiple AutoFields in a model), #27452 (SerialField).

At the moment, AutoFields must set primary_key=True.

This makes it impossible to use them with CompositePrimaryKey.

Multiple AutoFields in a model (#8576) may not be useful, but having an AutoField be part of a composite primary key has its uses. It's good practice to use surrogate keys, even if the table has a composite primary key (should this surrogate key be an auto-incremented field is another discussion).

So I think we should allow AutoFields to not set primary_key=True if they are part of a composite primary key.

Note: SQLite doesn't support AutoFields in composite primary keys.

[Natalia Bidart]

Hello Ben, thank for your creating this follow up ticket. I'm accepting this because of what was discussed and agreed in ​this Forum comment, but I do have a clarification question: in the Forum conversation, I understand it boiled down to two options:

Item (1) is tracked in ticket-8576, which is open but in a “Design Decision Needed” triage state. Allowing primary_key=False in an AutoField would enable all DB backends except SQLite to use such a field as part of a composite primary key. However, no backend except PostgreSQL supports having more than one AutoField. To progress this ticket, we need someone to take ownership and propose an outline for the implementation, so it can be moved to “Accepted” and enter the review process. Given the nature of the change, I fear this may be a longer and slower process.
On the other hand, I see value in having a way to define DB-level “counters” (independent of composite PKs), which is what item (2) addresses. Only PostgreSQL supports this, so I would prefer a PostgreSQL-specific field for this feature. While serial is no longer recommended, a GeneratedIdentityField in django.contrib.postgres, as suggested by Simon, seems like a promising alternative and could be more actionable in the short term. I recommend reopening ticket-27452 and repurposing it for this feature.

If I understand correctly, this ticket would be solving item (2) above, so we would be providing a Postgresql-specific field, correct?

[Csirmaz Bendegúz]

No, it's the opposite. The goal is to make AutoFields work with composite primary keys. It's not Postgres-specific, this would work with all database backends (except SQLite).

The difference between #8576 and this is that this ticket won't allow multiple AutoFields. A model can only have one AutoField, and that AutoField must be a primary key, or must be part of a composite primary key.

e.g.:

class Foo(models.Model):
    pk = models.CompositePrimaryKey("bar", "id", primary_key=True)
    bar = models.CharField(max_length=255)
    id = models.AutoField()

[Natalia Bidart]

Thank you Ben for clarifying. This seems to be outside what we discussed in the forum. Do you have more resources to point me to understand the bigger picture? I was assuming that our agreement and path forward with AutoFields was described in the referenced forum post but before proceeding I would like to have the full context.

[Csirmaz Bendegúz]

I think it aligns with what we discussed on the forum?

We keep the one AutoField per model restriction we currently have, but we'll allow AutoField to be part of a composite primary key.

I don't think we should allow multiple AutoFields, since only Postgres would support that and there's no strong use case for having multiple IDENTITY columns in a table. If consensus on this changes, then #8576 can be re-opened any time as it doesn't conflict with this ticket.

While the multiple AutoFields topic is contentious, everyone seems to agree that AutoField in composite primary keys should be added.

[Csirmaz Bendegúz]

related forum topic:
​https://forum.djangoproject.com/t/autofield-in-compositeprimarykey/37334