https://www.prisma.io/ logo
Join Slack
Powered by
on version 2.22 when I tried to delete a record wh...
# orm-help
d
on version 2.22 when I tried to delete a record which has a required relation i would get an error with the code 
P2014
and now on 2.26 I get 
P2003
. Is this change related to the new update with 
referentialActions
?
m
referentialActions
are a feature flag. You have to enable them yourself in your 
schema.prisma
file
Do you mind sharing more details about your schema?
d
yes I did enable it, but since I am on 2.26 I get a different code when I try to delete a record that has a required relation
before I always got 
P2014
j
If the preview feature is enabled, the behavior around that changes which could also mean new errors.
Can you past the full error message you are getting now so we have some context?
d
yes its also there 😄
Foreign key constraint failed on the field: 
Buyer_buyerGroupId_fkey (index)
I guess its the same meaning tho
j
Hm actually, I am a bit more confused. Can you share more about what you ahve and what you are trying to delete so @Mahmoud can maybe try it out and reproduce?
Maybe we need/should add something about changing error messages to our release notes or docs or similar.
m
More details/an example will definitely be helpful!
d
yea, so I have 2 models
Copy code
model Buyer {
  id
  buyerGroupId String @db.VarChar(255)

  buyerGroup BuyerGroup @relation(fields: [buyerGroupId], references: [id])
}

model BuyerGroup {
  id

  buyers Buyer[]
}
so before the new feature when I tried to delete a 
BuyerGroup
record I would get an error with code 
P2014
because ofcourse there is a required relation 
Buyer
record. something like this: 
The change you are trying to make would violate the required relation '...' between the 'Buyer' and 'BuyerGroup' models.
After the update to 2.26 with the new feature I get: 
Foreign key constraint failed on the field: 'Buyer_buyerGroupId_fkey (index)'
with code 
P2003
m
I’ll create a reproduction and get back to you 😄
d
did you find out anything?
m
On 
2.22.0
when deleting a record that has a required relation, I got the following error:
Copy code
Type: PrismaClientKnownRequestError
Message: 
Invalid `prisma.buyerGroup.delete()` invocation:


  The change you are trying to make would violate the required relation 'BuyerToBuyerGroup' between the `Buyer` and `BuyerGroup` models.

Code: P2014

Query:
prisma.buyerGroup.delete(
{
  where: {
    id: "5",
  },
}
)
Upgrading to 
2.26.0
gives the same error, which is expected. Upgrading to 
2.26.0
, enabling the 
referentialActions
flag gives a different error message:
Copy code
Type: PrismaClientKnownRequestError
Message: 
Invalid `prisma.buyerGroup.delete()` invocation:


  Null constraint violation on the fields: (`buyerGroupId`)

Code: P2011

Query:
prisma.buyerGroup.delete(
{
  where: {
    id: "5",
  },
}
)
Prisma version:
Copy code
prisma               : 2.26.0
@prisma/client       : 2.26.0
Current platform     : darwin
Query Engine         : query-engine 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 9b816b3aa13cc270074f172f30d6eda8a8ce867d (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 9b816b3aa13cc270074f172f30d6eda8a8ce867d
Studio               : 0.408.0
Preview Features     : referentialActions
enabling the feature flag shouldn’t change the error message. Because that means that enabling it changes the underlying behavior of prisma client 🤔 Also the fact that we got different error messages/codes is also weird
I’ll create an issue
👍 1
d
hmm, thats strange, i get an error code 
P2003
. Would there be a difference with 
deleteMany
and 
delete
okey I checked it's the same error on my side
could you perhaps share the schema you have created?
m
Copy code
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialActions"]
}

model Buyer {
  id           String     @id
  buyerGroup   BuyerGroup @relation(fields: [buyerGroupId], references: [id])
  buyerGroupId String
}

model BuyerGroup {
  id     String  @id
  buyers Buyer[]
}
j
Mahmoud, that is exactly why it is behind a preview feature flag - it does change the behavior in some cases. The difference is documented in the release notes, although not clear enough yet 😕
What might be happening here is: In the old version (or without the preview feature flag enabled), the Client was blocking the deletion with error P2014. In the new version with the preview feature flag enabled, that does not happen any more but instead whatever is configured in the database becomes activated. In this case it seems to be a 
ON DELETE CASCADE
(I assumes - can you share the full SQL of your database @Dev__?) and that would violate the null constraint - and the error is thrown.
How did you create the database schema @Mahmoud? Migrate with the old version?
m
Yea I simply upgraded 
prisma
and 
@prisma/client
packages, added the feature flag and ran 
generate
j
What version did you use to migrate the database SQL? The old?
That would fit to the 1 potentially breaking upgrade path.
d
sorry, but its private, cant share the full SQL unfortunatly. so I removed the new feature and I get the code 
P2014
again. Using the new feature gives a different code
👍 1
I didnt see anything about error codes changes in the release notes when using the new feature so my application kinda broke. thats why I wondered if that would be the cause
j
You are effectively experiencing https://github.com/prisma/prisma/releases/tag/2.26.0, the ⚠️ under 
New behavior
. But instead of it deleting data, you are getting an error message.
Seems we need to improve that warning and clarify that it could also lead to error messages like this.
👍 1
Hmm, I tried to reproduce this now as well - but can't 😞 In <2.26.0 I get the error P2014 as expected. In 2.26.0 I get the same error, as expected. In 2.26.0 I do not get the error, but the entry is deleted instead as the database is configured with 
FOREIGN KEY ("buyerGroupId") REFERENCES "BuyerGroup" ("id") ON DELETE CASCADE ON UPDATE CASCADE
. Something is probably different in your project @Dev__ - can you maybe share the relevant bits of the SQL from your project?
This is the relevant SQL from my reproduction that explains everything to me:
Copy code
-- CreateTable
CREATE TABLE "Buyer" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "buyerGroupId" TEXT NOT NULL,
    FOREIGN KEY ("buyerGroupId") REFERENCES "BuyerGroup" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "BuyerGroup" (
    "id" TEXT NOT NULL PRIMARY KEY
);
If your database was in a different state from before, this might explain the error message maybe.
d
I am using push, would that make a difference?
j
(I'll try to figure out with @Mahmoud via DM why this is not happening for him as for me and why he sees your error as well or another one)
Not really - the resulting SQL is the same
d
where can I see the output of SQL with push tho
j
Good point. Optimally use another database UI like DBeaver and export the SQL of the current database
d
alright, give me a minute
🙏 1
Copy code
create table "BuyerGroup"
(
    id          varchar(255)                                       not null constraint "BuyerGroup_pkey" primary key,
    "createdAt" timestamp with time zone default CURRENT_TIMESTAMP not null,
    "updatedAt" timestamp with time zone                           not null
);

create table "Buyer"
(
    id                       varchar(255)                                          not null constraint "Buyer_pkey" primary key,
    "buyerGroupId"           varchar(255)                                          not null constraint "Buyer_buyerGroupId_fkey" references "BuyerGroup" on update cascade on delete restrict,
    "createdAt"              timestamp with time zone default CURRENT_TIMESTAMP    not null,
    "updatedAt"              timestamp with time zone                              not null
);
@janpio
👍 1
j
The other potentially useful thing would be to enable full logging via https://www.prisma.io/docs/concepts/components/prisma-client/debugging#setting-the-debug-environment-variable + https://www.prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/logging#log-to-stdout - I wonder if there are some weird query things going on under the hood.
d
this is the export in datagrip
j
Aha! That is different from the default, so I think this could make sense now.
Let me play with this a bit...
d
wdym different from the default?
isnt it supposed to be like that with a 
prisma push
?
j
Mine:
Copy code
FOREIGN KEY ("buyerGroupId") REFERENCES "BuyerGroup" ("id") ON DELETE CASCADE ON UPDATE CASCADE
Yours:
Copy code
"buyerGroupId"           varchar(255)                                          not null constraint "Buyer_buyerGroupId_fkey" references "BuyerGroup" on update cascade on delete restrict,
Did you run 
db push
after upgrading and enabling the preview feature flag maybe?
(Because that default setting did indeed change and could explain your SQL)
I assume you only modify the actual database SQL via 
db push
?
d
yes, i always use a push in development
j
d
so I just did a fresh migrate (dropped the database) and the sql export is the same I i sent above
yes, i used 
prisma db push
after upgrading and enabling the new feature
j
Ok good, that already updated the database from the old default.
When I do that, I get this error message:
Copy code
PrismaClientUnknownRequestError2 [PrismaClientUnknownRequestError]: 
Invalid `prisma.buyerGroup.delete()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(SqliteFailure(Error { code: ConstraintViolation, extended_code: 1811 }, Some("FOREIGN KEY constraint failed"))) })
    at cb (C:\Users\Jan\Documents\throwaway\refActionsError\node_modules\@prisma\client\runtime\index.js:33900:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  clientVersion: '2.26.0'
}
(I am testing with SQLite, so probably this is a bit different - will switch to Postgres now which you are using, right?)
d
yes i am using postgres
m
I was using postgres too
j
Ok, finally could find the time to reproduce this - and it does. Will create an issue and discuss internally.
So fundamentally this is expected behavior. When you ran 
db push
after the upgrade and adding the preview feature flag, you changed the behavior of the 
ON DELETE
to the default, which is 
Restrict
. And that is what causes the error message for you.
Copy code
PrismaClientKnownRequestError2 [PrismaClientKnownRequestError]:
Invalid `prisma.buyerGroup.delete()` invocation:


  Foreign key constraint failed on the field: `Buyer_buyerGroupId_fkey (index)`
    at cb (C:\Users\Jan\Documents\throwaway\refActionsError\node_modules\@prisma\client\runtime\index.js:33896:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  code: 'P2003',
  clientVersion: '2.26.0',
  meta: { field_name: 'Buyer_buyerGroupId_fkey (index)' }
}
5 Views
Open in Slack
PreviousNext
Powered by