Schema Troubleshooting
Hi everyone reposting this, i have a pretty simple Schema with three models. However, whenever i try to push the schema to my MYSQL DB, i keep running into foreign key restraints. I don't see anything wrong with my relations thats causing this to occur. One thing to note is that i did accidentally migrate the schema to my db, but i went and dropped the table and the migration folder. Did I make a silly syntax mistake that i'm missing, or will i need to baseline my db due to migrations being out of sync?
6 Replies
Salutations, traveler! I'm the Prisma AI Help Bot. You've reached a fork in the road: one path leads to the wisdom of the human sages (ETA: sometime today), and the other to my instantaneous insights. Which way shall we go?
generator client {
provider = "prisma-client-js"
previewFeatures = ["metrics"]
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model User {
userID Int @id @default(autoincrement()) //primary key of the user table
username String @unique
password String
email String @unique
emailVerified Boolean @default(false)
listings Listing[] //one to many relationship. One user can have many listing
allBids Bid[] // one to many relationship. One user can have many bids
}
model Listing {
listingID Int @id @default(autoincrement()) //primary key of the Listing table
sellerID Int //foreign key of the user table is stored here. This is the user who is creating the listing
listCreationTimestamp DateTime @default(now())
listExpirationTimestamp DateTime @default(dbgenerated("DATE_ADD(NOW(), INTERVAL 2 DAY)")) //when the auction is over
closedListing String @default("open")
listTitle String
listDesc String?
listImg Bytes? @db.LongBlob
currentBid Int @default(0) //remove from schema to futher normalize, we can use this value by joining the bid table
creator User @relation(fields: [sellerID], references: [userID])
highBider Bid? //one to one relation.
}
model Bid {
bidID Int @id @default(autoincrement()) //primary key of the table of the Bid table
listID Int @unique //foreign key of the listing table. represents the listing that users are bidding on.
bidderID Int //foreign key of the user table. represents the user who is bidding on a listing
value Int @default(0) //must be greater than 0
bidder User @relation(fields: [bidderID], references: [userID]) //establishing the foreign key of the user table.
bidOnListing Listing @relation(fields: [listID], references: [listingID]) //establishing the foreign key of the Listing table.
}
Hey!
The error message "Cannot drop index Bid_bidderID_key needed in foreign key context" suggests that Prisma is trying to modify an index that's being used by a foreign key constraint.
Since you mentioned you already tried dropping tables, can you make sure you've dropped ALL related tables completely, including any junction tables Prisma might have created?
One thing to note is that i did accidentally migrate the schema to my db, but i went and dropped the table and the migration folder.Can you elaborate on what exact steps you took?
@Nurul (Prisma) absolutely,
There 3 main ideas i have tried in the following order.
1) tried db push, then db push --force-reset.
2) removed the migration folder. then did db push --force-reset. got the same error. then I tried to create a new migration just in case there was a mirgration drift, sadly I got the same error. I then tried to reset the migration and then push a new migration up, but the error persisted.
3) For the local database server, the application i'm using is xammps to host the mysql database. Xammps uses myphpadmin for its server.
a) i navigated to the YOTP database, went into Bid table, and drop the index. tried repushing.
b) Tried dropping the indexes along with their foreign key column.
c) Tried dropping the table
d) lastly i dropped the whole database, including the migration table, but when i went to repush i got the same error. (One thing to note is that I was able to get db push to work as long as I as i forced it)
removed the migration folder.You mean you removed the migration folder from your file system in prisma directory right? Did you also delete _prisma_migrations table from your database?
@Nurul (Prisma) yeah i did both