How to Fix Common Development Errors

Unique Primary Key Constraint Violation

Example

(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "alert_pkey" DETAIL: Key (id)=(3) already exists.

Explanation

Some classes in the data model powering the database are connected to keys in foreign tables. This linkage allows us to join data across multiple tables and typically relies on a unique identifier (ID) to serve as the glue. When new rows are added to any table, the unique ID is automatically incremented by one. For example, if the latest emergency in the system has an ID of 100, a new record will automatically get an ID of 101.

However, there are instances where a row may be deleted, and this can disrupt the ID sequence if not handled properly. For example, if the row with ID 101 is deleted, the database might still attempt to assign 101 to the next new record. This can lead to conflicts or errors since 101 has already been used and should not be reused unless explicitly reset.

If you get an error like the one in the example above, you need to help the database reset what it thinks is the latest ID so that it doesn’t try to apply one that already exists. To do this, we first need to get the highest number in that sequence, then add one so that the next write operation uses an unused number.

Solution

Access the database using a database program that allows you to send SQL commands. Here’s what that command will look like:

SELECT setval('public.alert_id_seq', (SELECT MAX(id) FROM public.alert) + 1);

1. public.alert_id_seq refers to the sequence (or index) of those unique IDs. In this case, this example is referring to the alert table’s id column. If you wanted to increment up a different table’s unique ID, you’d replace alert in alert_id_seq with the relevant table name. If you want to confirm what this value should be, you can run: SELECT pg_get_serial_sequence('alert', 'id'); (swapping out alert for whatever table you’re dealing with.
2. FROM public.alert again refers to that table. Replace the alert after public. with the relevant table name.

This should give you a successful commit message.