我正在使用Prisma和SQLite来构建一个音乐库应用程序。
但是,当使用album
创建一个轨道,并且artist
和albumartist
不同时,我得到Foreign key constraint failed on the field: 'foreign key'
。
专辑本身引用albumartist
(如果设置),否则引用artist
。
A minimum reproducible example(Github)
基于这些输入:
| 标题|艺术家|蛋白质分析家|相册|
| - ------| - ------| - ------| - ------|
| 一百一十一|富|富|专辑111|
| 二百二十二|巴兹|各种艺术家|汇编|
| 三百三十三|富克斯|各种艺术家|汇编|
我想要这个输出:
表Track
:
| 标题|艺术家|蛋白质分析家|相册||
| - ------| - ------| - ------| - ------| - ------|
| 一百一十一|富|富|专辑111||
| 二百二十二|巴兹|各种艺术家|汇编|* 当前失败 |
| 三百三十三|富克斯|各种艺术家|汇编| 当前失败 *|
表Artist
:
| 姓名|
| - ------|
| 富|
| 巴兹|
| 富克斯|
| 各种艺术家|
表Album
:
| 姓名|艺术家|
| - ------| - ------|
| 专辑111|富|
| 汇编|各种艺术家|
我的架构:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "sqlite"
url = "file:../test.db"
}
model Track {
id Int @id @default(autoincrement())
title String @unique
artistEntry Artist @relation(name: "artist", fields: [artist], references: [name])
artist String
albumartistEntry Artist? @relation(name: "albumartist", fields: [albumartist], references: [name])
albumartist String?
albumEntry Album @relation(name: "album", fields: [album, artist], references: [name, artist])
album String /// The id of the track's album
}
model Album {
id Int @id @default(autoincrement())
name String
artistEntry Artist @relation(fields: [artist], references: [name], onDelete: Cascade, onUpdate: Cascade)
artist String
tracks Track[] @relation(name: "album")
@@unique([name, artist])
}
model Artist {
name String @id
tracks Track[] @relation(name: "artist")
albumartistTracks Track[] @relation(name: "albumartist")
albums Album[]
}
示例中的修剪代码:
export const tracksData = [
{ title: "000", artist: "Oo_oO", albumartist: "Oo_oO", album: "Album 000" }, // Works
{ title: "111", artist: "Foo", albumartist: "Foo", album: "Album 111" }, // Works
{ title: "222", artist: "Baz", album: "Compilation", albumartist: "Various Artists" }, // Fails
{ title: "333", artist: "Foox", album: "Compilation", albumartist: "Various Artists" }, // Fails
]
for (const { album, albumartist, artist, title } of tracksData) {
const artistEntry: Prisma.ArtistCreateNestedOneWithoutAlbumsInput = {
connectOrCreate: {
where: { name: artist },
create: { name: artist },
},
}
const albumartistEntry: Prisma.ArtistCreateNestedOneWithoutAlbumsInput = {
connectOrCreate: {
where: { name: albumartist },
create: { name: albumartist },
},
}
const albumEntry: Prisma.AlbumCreateNestedOneWithoutTracksInput = {
connectOrCreate: {
where: {
name_artist: {
name: album,
artist: albumartist || artist,
},
},
create: {
name: album,
artistEntry: albumartistEntry || artistEntry,
},
},
}
const data: Prisma.TrackCreateInput = {
title,
albumEntry,
artistEntry,
albumartistEntry,
}
await prisma.track.upsert({
where: { title },
create: data,
update: data,
})
}
我尝试在SQLite中通过从数据库中的sql_master
复制CREATE
语句并手动运行查询来重新创建整个过程,但我得到了相同的结果,因此这是一个SQL错误,而不是Prisma所做的事情。
1条答案
按热度按时间zbsbpyhn1#
我通过在upsert之前禁用外键检查并在完成后再次启用它来修复它。
就像这样: