Allow deferral of unique constraints using definitions in Prisma schema

[vphoebe]

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 the indexes need to adjust to accommodate this. However, if an updateMany method is used to increment the index, the default order of the update operation will cause a failure on the unique constraint because the index 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:

@@unique([guildId, queueIndex], name: "queuePosition") DEFERRABLE INITIALLY DEFERRED

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:

1. Drop the @unique constraint from the Prisma schema and migrate it to the database.
2. Manually alter the constraint in the database directly with custom SQL to add the deferrals.
3. Use 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

[janpio]

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?

[vphoebe]

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.)

[tslocke]

Looks like this is not a Prisma issue after all but my misunderstanding of how to use this feature:

#13115

[janpio]

Allowing the part that you were missing (#13115 (comment)) is indeed content of this feature request here.

[controversial]

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.

[wtachau]

I came across this today when changing the type of a column from text to uuid, which involved dropping + recreating the foreign keys associated with them. Because the defer-ability of foreign keys wasn't tracked in the schema (I had created it in manual SQL migrations), it was lost, which caused a number of transactions to start failing.

[clemwo]

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 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.
My current solution is to simply set the first item to a large random negative integer, then set the second item to the first items original position and the first item then to the second items initial position, you get the idea.
But yeah would be cool if I could have one update query less

[clemwo]

Just to add to the discussion and wondering whether there is a better solution to my issue.
I also want to decrement the position of all the items that have a higher position than the item I deleted.
Currently I do that with updateMany() and while it does work currently I'm worried it might cause issues in the future. The reason I believe that is because the order of rows in the database can't be guaranteed. So my code:

    await tx.group.updateMany({
      where: {
        position: {
          gt: groupToDelete.position,
        },
      },
      data: {
        position: { decrement: 1 },
      },
    });

Doesn't work anymore if I instead use increment instead of decrement because of the unique constraint.
My solution for that is to simply order all the items and update them in single queries in the right order but that causes a lot of overhead to have n queries instead of a single updateMany

[ciphrd]

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.

[ciphrd]

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 DEFERRABLE INITIALLY DEFERRED. I was calling SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction, yet I would get a Postgres error when deleting an entity referenced by another ERROR: update or delete on table ... violates foreign key constraint ... on table ...

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 DEFERRABLE INITIALLY DEFERRED:

-- 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:

GenerativeToken
| id | authorWallet |
| 0  | a            |

Wallet
| address |
| a       |

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: ERROR: update or delete on table "Wallet" violates foreign key constraint "GenerativeToken_authorWallet_fkey" on table "GenerativeToken"

So the error wasn't directly related to prisma, as such I investigated the ON DELETE RESTRICT, and indeed I could find the relevant information in the Postgres doc

RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist 
when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. 
(The essential difference between these two choices is that NO ACTION allows the check to be deferred 
until later in the transaction, whereas RESTRICT does not.) 

So essentially ON DELETE RESTRICT will enforce the foreign key even if the foreign key check is deferred. The fix is simple:

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 NO ACTION is the default behaviour for constraints, yet Prisma will specify RESTRICT if nothing is set.

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.

[jorisre]

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.

PrismaClientKnownRequestError:
Invalid prisma.game.create() invocation:
Foreign key constraint failed on the field: Player_userId_fkey (index)

Does anyone have another workaround? @prisma, do you plan to implement this feature soon? Thank you.

[janpio]

A specific use case was mentioned in #3502 (which I now closed as a dupicate).

[Jackman3005]

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.

An operation failed because it depends on one or more records that were required but not found. No 'Person' record (needed to inline connect on create for 'Address' record) was found for a nested connect on one-to-one relation 'PersonToAddress'

[cedricpinson]

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.

An operation failed because it depends on one or more records that were required but not found. No 'Person' record (needed to inline connect on create for 'Address' record) was found for a nested connect on one-to-one relation 'PersonToAddress'

Same issue here 👍