Raise a subclass of IntegrityError for duplicate key errors

[andrew-cybsafe]

Code of Conduct

• I agree to follow Django's Code of Conduct

Feature Description

When a new row is inserted that violates a unique key constraint, raise a DuplicateKeyError instead of IntegrityError. DuplicateKeyError would be a sub-class of IntegrityError.

The new exception could also be named: DuplicateEntryError, DuplicateRowError, UniqueViolation, UniqueViolationError, etc..

Problem

I often need to understand whether an IntegrityError is caused by a duplicate entry exception versus other issues. For example, when processing incoming events from another system, it's typical to retry API calls when they fail and to provide a unique event_id to allow for de-duplication by the receiving system. In such a system, a duplicate key error can be ignored, whereas other errors might need to be handled elsewhere or logged so that a potential bug can be fixed.

Request or proposal

request

Additional Details

I currently workaround this on MySQL with the following helper method:

from django.db.utils import IntegrityError as DjangoIntegrityError
from django.db.utils import ProgrammingError
from MySQLdb import IntegrityError as MySQLIntegrityError
from MySQLdb.constants.ER import DUP_ENTRY

def is_mysql_duplicate_entry_error(exception):
    """Checks that a given exception was due to a MySQL duplicate entry error"""
    if not isinstance(exception, DjangoIntegrityError):
        return False

    cause = exception.__cause__
    if cause is None:
        return False

    if not isinstance(cause, MySQLIntegrityError):
        raise ProgrammingError("Can only be used on MySQL exceptions")

    code = cause.args[0] if cause.args else 0
    return code == DUP_ENTRY

which is used like so:

try:
    object.save()
except IntegrityError as e:
    if is_mysql_duplicate_entry_error(e):
        return
    # Re-raise other errors to make them visible
    raise e

It would be really nice to be able to write:

try:
    object.save()
except DuplicateKeyError:
    return  # ignore duplicates

Implementation Suggestions

• Subclass IntegrityError with an appropriately named exception
• Update the various DB adapters to raise the new exception when the relevant error code is returned from the DB
• Add tests
• Update docs & release notes

[github-actions]

Thank you andrew-cybsafe for sharing your idea! We have a lot of them so please be patient. You can see the current queue here.

Community instructions

For commenters, please use the emoji reactions on the issue to express support, and/or concern easily. Please use the comments to ask questions or contribute knowledge about the idea. It is unhelpful to post comments of "I'd love this" or "What's the state of this?"

Reaction Guide

• 👍 This is something I would use
• 👎 This is something that would cause problems for me or Django
• 😕 I’m indifferent to this
• 🎉 This is an easy win

[jacobtylerwalls]

We would probably use this in get_or_create() in Django itself (and maybe in a couple contrib apps).

When I had to implement something similar to Django 5's create_defaults param in update_or_create() to a Django 4 project, I got a code review comment along the lines of "why are you catching IntegrityError and returning a 4xx". I pointed the reviewer at the Django source ("look, they're doing it"), but making this kind of catch smaller and self-documenting would be really nice.

[shangxiao]

For pg, libpq and hence psycopg already raise a UniqueViolation:

• https://www.postgresql.org/docs/current/errcodes-appendix.html
• https://www.psycopg.org/psycopg3/docs/api/errors.html#list-of-known-exceptions

Note, Django does not forward this though so you can't catch it, but you can examine the cause:

>>> e.__cause__
UniqueViolation("duplicate key value violates unique constraint ...")

As for the naming

I'd suggest that we don't try to try to overthink this and use something with at least the word "Unique" in it to make it explicit that it's for a UniqueConstraint violation. I'd personally vote that we just reuse UniqueViolation since that's already raised by a popular DB.

In terms of the error message itself, well that varies and it's not always "Duplicate ..."

• SQLite: UNIQUE constraint failed: ...
• MySQL: Duplicate entry '...' for key '...'
• Postgres: duplicate key value violates unique constraint ...

[tim-schilling]

Would we be able to point out which constraint is violated? We could do a bit more from a DX perspective if we captured that.

[charettes]

I really like the idea of having specialized IntegrityError subclasses when the backend allows it. I wonder if we could have them follow our constraint naming conventions being

• UniqueConstraintViolation(IntegrityError)
• CheckConstraintViolation(IntegrityError)
• ForeignKeyConstraintViolation(IntegrityError)

It would also be great if the subclasses included the constraint identifier / name in there as one could do something like

try:
   obj.save()
except UniqueConstraintViolation as exc:
    constraint = obj._meta.get_constraint(exc.constraint_name)
    raise ValidationError(constraint.validation_error_message)

which would avoid calling / emulating constraint validation in the first place (you could delegate entirely to the database)

[charettes]

I started to work on a PoC for this, and it seems like extracting string messages from the backend is the only way to achieve it so it's not trivial.

Some finding so far

• Is geting all UNIQUE constraint violated column names possible? sqlalchemy/sqlalchemy#10649
• https://github.com/openstack/oslo.db/blob/48ff2a0e43f02dd7f0251391949ca257b96184af/oslo_db/sqlalchemy/exc_filters.py#L160-L192

[charettes]

Here's a POC I worked on at Django on the med 🏖️ to see if it was feasible to implement constraint name introspection

django/django@main...charettes:django:integrity-errors-subclasses

My learning can be summarized as

1. It's quite straightforward to specialize IntegrityError to check, and unique constraint violation specialized errors. It could also be extended to foreign key constraints if we wanted to, which could be a nice symmetry with a potential ForeignKeyConstraint which might be necessary to add support for composite foreign key
2. Extracting constraint name requires regex parsing, as SQLAlchemy did on their side, and on SQLite, we unfortunately cannot reliably extract it from the error message as involved column names are the only detail include (no mention of function usage for expression indices, or restrictive clause for conditional indices). I'll ask if I'm missing something on the SQLite forum but I can't seem to find anything in the source that indicates the violated constraint name is surfaced to the client.

[charettes]

A related use case relating to model form validation that could be empowered by this feature.