-
Notifications
You must be signed in to change notification settings - Fork 1.5k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Allow deferral of unique constraints using definitions in Prisma schema #8807
Comments
Nice feature request, applied some labels so we will have an easier time finding and prioritizing this. Does this exist in other databases besides PostgreSQL? |
Looks like it's unsupported in MySQL and SQLite, unfortunately. Still trying to track down an answer for MongoDB. It doesn't appear to have explicitly deferrable constraints, but I also don't know how uniqueness is enforced by default (for example, if it's already post-transaction this wouldn't matter anyway.) |
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
This comment was marked as outdated.
Looks like this is not a Prisma issue after all but my misunderstanding of how to use this feature: |
Allowing the part that you were missing (#13115 (comment)) is indeed content of this feature request here. |
Is there a good workaround for this? My use case (Postgres) is updating a unique field on a large number of records, where the update may include “swapping” a value in the field between two existing records (so row A gets the value that row B had, and row B gets the value that row A had). Right now, even if I make the swap in a transaction, Prisma encounters an error and rolls back the transaction. |
I came across this today when changing the type of a column from |
I have the same issue here. I'm storing a position value to keep items sorted. Now when I want to move an item up or down, I simply swap the values. |
Just to add to the discussion and wondering whether there is a better solution to my issue.
Doesn't work anymore if I instead use increment instead of decrement because of the unique constraint. |
As a reminder, prisma generates migration sql files which can be manually updated. So while it's not possible to define this at the schema level, it's still possible to gerenate the migrations, update the SQL files to change the constraints to DEFERRED, and migrate the DB. Pointed out by #8807 (comment) which also highlighted the issue with such an approach. If you go with this option, please make sure to provide some documentation to remind yourself & other devs about it. |
So I banged my head a little this morning on a related issue because I had an error raised with a foreign key set to To be more specific, this was my schema: model Wallet {
address String @id
generativeTokens GenerativeToken[] @relation()
}
model GenerativeToken {
id Int @id
author Wallet @relation(fields: [authorWallet], references: [address])
authorWallet String
} By default prisma generative the following constraint in the migration file: -- AddForeignKey
ALTER TABLE "GenerativeToken" ADD CONSTRAINT "GenerativeToken_authorWallet_fkey" FOREIGN KEY ("authorWallet") REFERENCES "Wallet"("address") ON DELETE RESTRICT ON UPDATE CASCADE; Which I updated to add -- AddForeignKey
ALTER TABLE "GenerativeToken" ADD CONSTRAINT "GenerativeToken_authorWallet_fkey" FOREIGN KEY ("authorWallet") REFERENCES "Wallet"("address") ON DELETE RESTRICT ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED; Let's assume the following data set:
When I ran the following SQL statement against the DB: BEGIN;
SET CONSTRAINTS ALL DEFERRED;
DELETE FROM "Wallet" WHERE "Wallet".address = 'a';
INSERT INTO "Wallet"(address) VALUES('a');
COMMIT; I would get the following SQL error: So the error wasn't directly related to prisma, as such I investigated the
So essentially model GenerativeToken {
author Wallet @relation(fields: [authorWallet], references: [address], onDelete: NoAction)
} I think what could be slightly missleading on prisma's end is the fact that Bottom line: if you are venturing into a design where you need deferred constraints, be careful about the generated schema and the potential db rules which may conflict into each other. |
I also have this need; I tried to do the same thing as @ciphrd but in the case of a Create, but I couldn't succeed—I keep getting the same error.
Does anyone have another workaround? @prisma, do you plan to implement this feature soon? Thank you. |
A specific use case was mentioned in #3502 (which I now closed as a dupicate). |
I think this would help with solving this class of problem. Here's a snippet from the SO question: Person (
PersonID int primary key,
PrimaryAddressID int not null,
...
)
Address (
AddressID int primary key,
PersonID int not null,
...
) We have a similar relationship where I am currently forced to define a schema where "Person" has a nullable "primaryAddressId" in the database (even though this is never null and should not be allowed to be null) because I cannot create both records at the same time in Prisma without the FK constraints preventing the creation.
|
Same issue here 👍 |
Problem
Unique constraints are enforced during each statement in a database operation by default. However, it would be useful to defer this enforcement to the end of the full transaction in some cases where values are getting "shifted" and may be temporarily duplicated.
For example, take a table that represents an ordered list organized with an
index
column which is a unique identifier (only one row can occupy the index in the list.) If an item is to be "inserted" into the list, all theindex
es need to adjust to accommodate this. However, if anupdateMany
method is used to increment theindex
, the default order of the update operation will cause a failure on the unique constraint because theindex
is already in use by another row. If the unique enforcement waited till the full transaction completed, this would not cause an issue.A more specific example and use case is explained in my Q&A thread here: #8789
This is possible in PostgreSQL and possibly other databases: https://www.postgresql.org/docs/9.1/sql-set-constraints.html
Suggested solution
Allow
DEFERRED
options to be defined in the Prisma schema, alongside the@unique
declarations. This will allow prisma to keep the DEFERRED options in sync with the schema AND the database. It can currently be done directly in the database manually but will be overwritten when the schema is deployed with Migrate.Possible example to match SQL syntax, probably a way that fits better with the prisma syntax:
Alternatives
One early alternative I used was first querying all the rows to be affected, sorted in a way that would allow the unique constraint to be maintained (start at the highest index if they are to be incremented by 1.) Then, using a for loop, do a single update for each, maintaining the operations in the order that the query was sorted by. This works, but is not ideal for performance.
The next alternative I found was to manually add the DEFERRED options to the database. In order to get this to work, I had to:
prisma db pull
to "rediscover" the unique constraint so it would be enforced in the code/typings. However, the database kept the DEFERRED options intact.This will however be overwritten if
prisma migrate dev
for example is run in the future, so it's not a sustainable solution.Additional context
Please see the discussion thread I opened for a more specific example. As this is a general feature request, here I attempted to be more general so it can apply to all projects. #8789
Other similar suggestions in the past
I found a few other mentions of this so I figured I'd link them:
#3502
#3495
The text was updated successfully, but these errors were encountered: