Drizzle Foreign Key Constraints are not allowed

Can anyone lend me a hand with understanding this error message? I've got some relations in my schema that are causing the following error on db:push to planetscale:
Error: VT10001: foreign key constraints are not allowed
at PromiseConnection.query (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:35481:26)
at Command.<anonymous> (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:53292:33)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
code: 'ER_UNKNOWN_ERROR',
errno: 1105,
sql: 'ALTER TABLE `modulation_album` ADD CONSTRAINT `modulation_album_artist_id_modulation_artist_id_fk` FOREIGN KEY (`artist_id`) REFERENCES `modulation_artist`(`id`) ON DELETE no action ON UPDATE no action;',
sqlState: 'HY000',
sqlMessage: 'VT10001: foreign key constraints are not allowed'
}
Error: VT10001: foreign key constraints are not allowed
at PromiseConnection.query (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:35481:26)
at Command.<anonymous> (/home/hyhy/dev/modulation/node_modules/.pnpm/[email protected]/node_modules/drizzle-kit/index.cjs:53292:33)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
code: 'ER_UNKNOWN_ERROR',
errno: 1105,
sql: 'ALTER TABLE `modulation_album` ADD CONSTRAINT `modulation_album_artist_id_modulation_artist_id_fk` FOREIGN KEY (`artist_id`) REFERENCES `modulation_artist`(`id`) ON DELETE no action ON UPDATE no action;',
sqlState: 'HY000',
sqlMessage: 'VT10001: foreign key constraints are not allowed'
}
Using the stock drizzle config:
import { type Config } from "drizzle-kit";

import { env } from "~/env";

export default {
schema: "./src/server/db/schema.ts",
driver: "mysql2",
dbCredentials: {
connectionString: env.DATABASE_URL,
},
tablesFilter: ["modulation_*"],
} satisfies Config;
import { type Config } from "drizzle-kit";

import { env } from "~/env";

export default {
schema: "./src/server/db/schema.ts",
driver: "mysql2",
dbCredentials: {
connectionString: env.DATABASE_URL,
},
tablesFilter: ["modulation_*"],
} satisfies Config;
Solution:
Emulating foreign key constraints with Drizzle relationships — Plan...
Learn how to build virtual relationships between tables in PlanetScale while using the Drizzle TypeScript ORM.
Jump to solution
4 Replies
hyhy
hyhy11mo ago
Relations in my schema in question, for example each episode has a mix:
export const episodes = mysqlTable(
"episode",
{
id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
name: varchar("name", { length: 256 }),
mixId: bigint("mix_id", { mode: "number" }).references(() => mixes.id),
},
(episode) => ({
nameIndex: index("name_idx").on(episode.name),
}),
);

export const mixes = mysqlTable("mix", {
id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
});
export const episodes = mysqlTable(
"episode",
{
id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
name: varchar("name", { length: 256 }),
mixId: bigint("mix_id", { mode: "number" }).references(() => mixes.id),
},
(episode) => ({
nameIndex: index("name_idx").on(episode.name),
}),
);

export const mixes = mysqlTable("mix", {
id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
});
or a many to many between albums and episodes
export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
albumId: bigint("album_id", { mode: "number" }).references(() => albums.id),
episodeId: bigint("episode_id", { mode: "number" }).references(() => episodes.id),
});
export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
id: bigint("id", { mode: "number" }).primaryKey().autoincrement(),
albumId: bigint("album_id", { mode: "number" }).references(() => albums.id),
episodeId: bigint("episode_id", { mode: "number" }).references(() => episodes.id),
});
Assuming I'm either missing something in my config, or defining the relations incorrectly
Solution
hyhy
hyhy11mo ago
Emulating foreign key constraints with Drizzle relationships — Plan...
Learn how to build virtual relationships between tables in PlanetScale while using the Drizzle TypeScript ORM.
Rhys
Rhys11mo ago
If you use the MySQL 2 driver you need to set drizzle to emulate forgien keys
hyhy
hyhy11mo ago
i think this works correctly now but will have to see, it pushed up at least
export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
id: serial("id").primaryKey(),
albumId: int("album_id"),
episodeId: int("episode_id"),
createdAt: timestamp("created_at")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updatedAt").onUpdateNow(),
});

export const relAlbumsEpisodesRelations = relations(
relAlbumsEpisodes,
({ one }) => ({
artist: one(albums, {
fields: [relAlbumsEpisodes.albumId],
references: [albums.id],
}),
genre: one(episodes, {
fields: [relAlbumsEpisodes.episodeId],
references: [episodes.id],
}),
}),
);
export const relAlbumsEpisodes = mysqlTable("rel_albums_episodes", {
id: serial("id").primaryKey(),
albumId: int("album_id"),
episodeId: int("episode_id"),
createdAt: timestamp("created_at")
.default(sql`CURRENT_TIMESTAMP`)
.notNull(),
updatedAt: timestamp("updatedAt").onUpdateNow(),
});

export const relAlbumsEpisodesRelations = relations(
relAlbumsEpisodes,
({ one }) => ({
artist: one(albums, {
fields: [relAlbumsEpisodes.albumId],
references: [albums.id],
}),
genre: one(episodes, {
fields: [relAlbumsEpisodes.episodeId],
references: [episodes.id],
}),
}),
);
Want results from more Discord servers?
Add your server