Frequent constraint failed on id when using @id @default(autoincrement())
Is there anything I should know about how
@id @default(autoincrement()) works?
We're using postgresql.
We find that if you delete a row and then insert that same row with the same id, we hit a prisma:error Unique constraint failed on the fields: (id).
Additionally, we find that if you use a transaction to insert multiple rows at once, we also hit prisma:error Unique constraint failed on the fields: (id).
This is a frequent issue, so much so we have fallbacks that compute an id number, which removes the whole point of auto-increment.4 Replies
Any suggestions? This is creating ship-blocking issues for us, so much so that we're going to migrate to a different ORM
I don't think anyone will be able to help you with this @Nick . This does not sound like a Prisma problem.
We find that if you delete a row and then insert that same row with the same id, we hit a prisma:error Unique constraint failed on the fields: (id).PostgreSQL keeps a counter of what the next ID should be, and that counter doesn't change regardless of what data gets deleted. From PostgreSQL docs: https://www.postgresql.org/docs/current/sql-createsequence.html
... nextval and setval calls are never rolled back...
nextval is the PG function that determines what the ID of a new inserted row should be.
Additionally, we find that if you use a transaction to insert multiple rows at once, we also hit prisma:error Unique constraint failed on the fields: (id).I'm not sure how/which IDs are provided, but this probably fails for the same reason, being a mismatch between the IDs provided in
.createMany and the IDs the DB would have assigned.
@Nick It looks like you need to decide whether you want the DB to have full control over what the IDs should be, or whether you want your code to have that control.PostgreSQL Documentation
CREATE SEQUENCE
CREATE SEQUENCE CREATE SEQUENCE — define a new sequence generator Synopsis CREATE [ { TEMPORARY | TEMP } | UNLOGGED …
Got it. This is helpful - thought I was losing my mind over here. I'll look into reseting the sequence and comb through our inserts to see if we're missing an edge case that results in duplicate data being upserted at the same time. Thanks for the info 🙏 🙏 🙏