Context : Shops and Orders. Autoincremented "number" by shopId
Hello,
Let's say I've 2 entities in my schema. Shop and Order.
Is there a way to achieve uniqueness on
number field based on shopId value please, and keep it autoincremented ?5 Replies
You selected the bot's instantaneous insights. Let me compile your answer right away!
Based on the knowledge sources, there isn't a direct way in Prisma to have an auto-incrementing field that resets or starts separately for each shop ID. What you're looking for is similar to the feature request described in Set AUTO_INCREMENT start value in schema.prisma and Multiple sequences per table with autoincrement().
The issue #22440 specifically describes a similar use case to yours - having unique auto-incrementing IDs per repository (in your case, per shop). The contributor in that issue asks:
How does one actually solve this with PostgreSQL sequences, what would the SQL need to look like?This suggests that this functionality isn't currently supported natively in Prisma. Your current schema correctly defines a compound unique constraint with
@@unique([shopId, number]), which ensures that the combination of shopId and number is unique. However, the @default(autoincrement()) will increment across the entire table, not per shop.
You have a few alternatives:
1. Use application logic to handle this - query for the highest number for a specific shopId and increment it manually when creating new orders.
2. If you're using PostgreSQL, you might be able to create custom sequences in your migration files, as suggested in the How to set AUTO_INCREMENT discussion, but this would require manual SQL editing.
3. Consider using a different approach for your order numbers, such as using timestamps or UUIDs combined with shop identifiers.
The feature you're looking for (auto-incrementing sequences per group) is something that has been requested but doesn't appear to be directly supported in Prisma yet.Hey 👋
Does the bot answer help for your query?
It gives some ideas yes, but I'd like to know if Prisma can do this "natively" ^^
I don't think it is natively possible yet
yeah probably I've to do this with a pgsql function etc