When creating an implicit many to many relation, following the docs here; https://www.prisma.io/docs/concepts/components/prisma-schema/relations, the table created for the join does not have a primary called out. MySql by default requires a primary key (and for good reason, replication counts on it). Is there a way to force a primary key creation on the implicit link table?

CREATE TABLE "Category" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY
);
-- Relation table + indexes -------------------------------------------------------
CREATE TABLE "_CategoryToPost" (
    "A" integer NOT NULL REFERENCES "Category"(id),
    "B" integer NOT NULL REFERENCES "Post"(id)
);
CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);
CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);

No there is not and we are not aware of this being a problem for MySQL in any way.

When I tried creating my database on my cloud host (digitalOcean), I got the error saying a primary key is required (unless you change a db setting). From this post on SO, I gather I'll not be able to change that option though have not tried yet. The post does reference mysql docs, but again, I've not verified that. https://stackoverflow.com/questions/62418099/unable-to-create-or-change-a-table-without-a-primary-key-laravel-digitalocean

Right now I am not aware of a workaround. You can probably create a migration file and add the primary key there manually.

I'm very new to Prisma (and the migration stuff), but I'll give it a go. I'm currently at that phase where when things work, I'm amazed, and when they don't a launch into flounder mode until they do. Loving the potential though. I think you guys are on to something (just not sure what yet) 🙂.

Thanks so much. I think you hit an edge case there with DO's MySQL that we should cover better.

That does kind of suck. Just back from lunch and reading about how to make my own migration. I'm thinking I create one with the -createonly flag, modify it by hand, then run it. I did verify that in mysql I can add a primary key as composite

ALTER TABLE _NoteToTag ADD primary key (A,B)

and that seemed to work at least with no error and my app still worked. It does seem like it's something you should at least create an option for. I'm guessing the only reason it's not an error in MySql is that a long long time ago before everyone believed in primary keys, a lot of apps were built that still exist. I have a lot of road rash from having to fix those apps.

Sorry about suggesting modifying the migration - that was a red herring - if you do that all future migrations will try to remove that primary key again 😞

🙂. I read all about that in the docs. I assume that when you said "modify" you really meant to it the -create way, then modify. Over the years, I've done a lot of work with Microsoft's entity framework migrations so I'm aware of how fragile they can be.

Yes, I did mean

migrate dev --create-only

, then modify the file and apply it with another

migrate dev

or

migrate deploy

. The problem with that is that any future migration you create also needs to be done via

--create-only

so you can remove the statement that would undo your manual modification again 😕

hmm. sounds like maybe a better plan is to do the alter directly, then clear the migrations folder and then do

prisma introspect

? Would that somehow bring the primary key back int the initial migration file, then I can move forward with the normal migration process as I make changes?

It's a bit more complicated 😕

I get it. It's a hard problem. I'm putting a demo app together for a course I'm working on (React, not prisma) and I'm still wrestling with how to teach many to many relationships. I really love how easily the many to many just worked in prisma. I suppose in a production app, the corner cases (like this) would pile up. Maybe best if I revert to the standard 3 table solution that everyone expects to see.

We have to pick our fights 😄

there is that. BTW, I assume you are in a far away timezone. I appreciate you spending the time answering my query.

One day I hope we can make any m:n relation an implicit relation with nice API support. That definitely is our vision for that part.

I hope you figure out the implicit relationships, but it isa path fraught with peril as it's quite far from the base table design you are mapping from. I wish the wind at your back in solving this problem but it may cause more heartache then it cures. At scale, things like locking will get really nasty.

Ha, how could I say no to that after that

(...)

😛 But yep, always 👍

100% agree. It's ironic that I'm kind of arguing against it. I'm usually the one who, when engineers tell me why they can't do something, I'm telling them to go figure it out. I guess i've been burned so many times over the years by ORMS and leaky abstractions that it's hard to have a good attitude about it.

Welcome to the club.