How Can I Disable Foreign Key?

[linxixuan]

Problem

My DB can't use foreign key，If I specify the relation, Prisma will create foreign constraint automatically.

Suggested solution

I wonder how can I disable the foreign key constraint? All I need is the relation between two tables

So, is it possible

Some Info

here is the schema and sql created by prisma

model Post {
  id          Int           @id @default(autoincrement())
  updateAt    DateTime      @default(now())
  title       String
  type        String
  url         String        @unique
  tags        String
  authorId    Int?
  likes       Int           @default(0)
  isDelete    Boolean       @default(false)
  user        User?         @relation(fields: [authorId], references: [id])
  weeklyPosts WeeklyPosts[]

  @@index([authorId], name: "authorId")
}

CREATE TABLE `Post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updateAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tags` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorId` int(11) DEFAULT NULL,
  `likes` int(11) NOT NULL DEFAULT '0',
  `isDelete` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `Post.url_unique` (`url`),
  KEY `authorId` (`authorId`),
  CONSTRAINT `post_ibfk_1` FOREIGN KEY (`authorId`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[tomhoule]

Hi @linxixuan — what database are you using? We are discussing how we tackle this internally and want to work on an option to make migrate ignore relations (-> create no foreign keys) soon.

In the meantime, you can add previewFeatures = ["planetScaleMode] to your generator and planetScaleMode = true in your datasource and it should do what you want. But these options are for sure going to change soon.

[linxixuan]

DB is mysql, our DBA suggest us do not use foreign key for some performance reason ;)

[linxixuan]

@tomhoule hello, your solution is not work. Here is my env

bug

my code

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

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
  planetScaleMode = true
}

env

$ npx prisma -v
prisma               : 2.23.0
@prisma/client       : 2.23.0
Current platform     : darwin
Query Engine         : query-engine adf5e8cba3daf12d456d911d72b6e9418681b28b (at ../../node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli adf5e8cba3daf12d456d911d72b6e9418681b28b (at ../../node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core adf5e8cba3daf12d456d911d72b6e9418681b28b (at ../../node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt adf5e8cba3daf12d456d911d72b6e9418681b28b (at ../../node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : adf5e8cba3daf12d456d911d72b6e9418681b28b
Studio               : 0.393.0

[tomhoule]

Ah, I think your prisma version is a bit too old, it probably was released around 2.24

[thebiglabasky]

@linxixuan Did you try with the latest version to confirm we can close this issue?
Thank you

[sam-bunger]

I'm having a similar issue where I need to disable foreign key constraints. I'm using Postgres.

Here is my schema:

model profiles {
  id                   String          @id
  first_name           String?
  last_name            String?
  phone_number         String          @unique
  phone_number_ref     contacts[]
  contacts_ref         contacts[]      @relation("contact_holder")
}

model contacts {
  id                 Int       @id
  contact            String?
  contact_ref        profiles? @relation(fields: [contact], references: [phone_number])
  contact_holder     String
  contact_holder_ref profiles  @relation("contact_holder", fields: [contact_holder], references: [id])
}

Every profile has an id and a phone_number. Each user in the profiles table will have each of their contacts on their phone put into contacts as individual rows. contact_holder_ref will always reference a user id in the profiles table. But a user’s contacts might may or may not reference another profile’s phone_number in the profiles table. Foreign key constraints are getting created by prisma that prevent me from adding phone numbers in contacts that don’t exist in profiles. Ideally, i'd like to stop contacts_contact_fkey from getting created.

ENV

prisma               : 2.28.0
@prisma/client       : 2.27.0
Current platform     : darwin-arm64
Query Engine         : query-engine 89facabd0366f63911d089156a7a70125bfbcd27 (at ../../../../opt/homebrew/lib/node_modules/prisma/node_modules/@prisma/engines/query-engine-darwin-arm64)
Migration Engine     : migration-engine-cli 89facabd0366f63911d089156a7a70125bfbcd27 (at ../../../../opt/homebrew/lib/node_modules/prisma/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine : introspection-core 89facabd0366f63911d089156a7a70125bfbcd27 (at ../../../../opt/homebrew/lib/node_modules/prisma/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary        : prisma-fmt 89facabd0366f63911d089156a7a70125bfbcd27 (at ../../../../opt/homebrew/lib/node_modules/prisma/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash : 89facabd0366f63911d089156a7a70125bfbcd27
Studio               : 0.417.0

[janpio]

Can you please open a new issue @sam-bunger? I think your problem is different. Thanks.

[sam-bunger]

Hey @janpio, the problems are different, but the ability to disable foreign key constraints would solve both of them.

[janpio]

The current solution is described above: #8108 (comment)

[bryan-cho]

Hi @sam-bunger – did you manage to figure out how to solve your use-case? I'm hoping to achieve the same where I have a Postgres DB and need to set up a relation that does not have a foreign key constraint.

[sam-bunger]

Hey @bryanc208, I couldn't figure it out. We ended up deciding to stop using prisma for our project, so I haven't had the chance to look around and test if this solution really works, but from this looks of it this PR may have fixed the issue: prisma/prisma-engines#2221

[Arthur-CWW]

for anyone searching for this and using planetscale in the future foreign key mode .

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}