DT
Drizzle Team•8mo ago
stephen

Schema is "wrong" resulting in "foreign key mismatch" error

I generated my sqlite schema using drizzle but now that I'm trying to use it, I'm getting a foreign key error. Here's where it seems to error: I added 
export const menuRecipes = sqliteTable('menu_recipes', {
  id: text('id').$default(() => createId()).primaryKey(),
  menuId: text('menu_id').references(() => menus.id, { onDelete: "cascade" }).notNull(),
  recipeId: text('recipe_id').references(() => recipes.id, { onDelete: "cascade" }).notNull(),
  addedBy: text('added_by').references(() => users.id),
  createdDate: text('created_date').$default(() => toISO8601(new Date())),
  updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onUpdate: "cascade" }),
});

export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
  menu: one(menus, { fields: [menuRecipes.menuId], references: [menus.id] }),
  recipe: one(recipes, { fields: [menuRecipes.recipeId], references: [recipes.id] }),
  user: one(users, { fields: [menuRecipes.addedBy], references: [users.id] }),
  menuCourse: one(menuCourses, { fields: [menuRecipes.menuCourseId], references: [menuCourses.id] }),
}));


export const menuCourseRecipes = sqliteTable('menu_course_recipes', {
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onDelete: "cascade" }).notNull(),
  menuRecipeId: text('recipe_id').references(() => menuRecipes.id, { onDelete: "cascade" }).notNull(),
}, (t) => ({
  pk: primaryKey({ columns: [t.menuCourseId, t.menuRecipeId] }),
}));

export const menuCourseRecipesRelations = relations(menuCourseRecipes, ({ one }) => ({
  menuCourses: one(menuCourses, {
    fields: [menuCourseRecipes.menuCourseId],
    references: [menuCourses.id],
  }),
  menuRecipes: one(menuRecipes, {
    fields: [menuCourseRecipes.menuRecipeId],
    references: [menuRecipes.id],
  }),
}));
export const menuRecipes = sqliteTable('menu_recipes', {
  id: text('id').$default(() => createId()).primaryKey(),
  menuId: text('menu_id').references(() => menus.id, { onDelete: "cascade" }).notNull(),
  recipeId: text('recipe_id').references(() => recipes.id, { onDelete: "cascade" }).notNull(),
  addedBy: text('added_by').references(() => users.id),
  createdDate: text('created_date').$default(() => toISO8601(new Date())),
  updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onUpdate: "cascade" }),
});

export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
  menu: one(menus, { fields: [menuRecipes.menuId], references: [menus.id] }),
  recipe: one(recipes, { fields: [menuRecipes.recipeId], references: [recipes.id] }),
  user: one(users, { fields: [menuRecipes.addedBy], references: [users.id] }),
  menuCourse: one(menuCourses, { fields: [menuRecipes.menuCourseId], references: [menuCourses.id] }),
}));


export const menuCourseRecipes = sqliteTable('menu_course_recipes', {
  menuCourseId: text('menu_course_id').references(() => menuCourses.id, { onDelete: "cascade" }).notNull(),
  menuRecipeId: text('recipe_id').references(() => menuRecipes.id, { onDelete: "cascade" }).notNull(),
}, (t) => ({
  pk: primaryKey({ columns: [t.menuCourseId, t.menuRecipeId] }),
}));

export const menuCourseRecipesRelations = relations(menuCourseRecipes, ({ one }) => ({
  menuCourses: one(menuCourses, {
    fields: [menuCourseRecipes.menuCourseId],
    references: [menuCourses.id],
  }),
  menuRecipes: one(menuRecipes, {
    fields: [menuCourseRecipes.menuRecipeId],
    references: [menuRecipes.id],
  }),
}));
2 Replies
stephen
stephen•8mo ago
I found this SO thread (https://stackoverflow.com/questions/6848608/sqlite-foreign-key-mismatch-error), and tried adding this to my menuRecipesRelations: 
export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
+  menuCourseRecipes: one(menuCourseRecipes, { fields: [menuRecipes.menuCourseId, menuRecipes.recipeId], references: [menuCourseRecipes.menuCourseId, menuCourseRecipes.menuRecipeId] }),
}));
export const menuRecipesRelations = relations(menuRecipes, ({ one }) => ({
+  menuCourseRecipes: one(menuCourseRecipes, { fields: [menuRecipes.menuCourseId, menuRecipes.recipeId], references: [menuCourseRecipes.menuCourseId, menuCourseRecipes.menuRecipeId] }),
}));
Getting this to be picked up when I run db:gen, however, is challenging. 
"scripts": {
    "db:gen": "drizzle-kit generate:sqlite",
    "db:migrate": "drizzle-kit generate:sqlite --out ./drizzle --schema ./app/db/schema.ts",
    "db:studio": "drizzle-kit studio --verbose --port 5555",
    "db:seed": "tsx ./app/db/seed.ts",
}
"scripts": {
    "db:gen": "drizzle-kit generate:sqlite",
    "db:migrate": "drizzle-kit generate:sqlite --out ./drizzle --schema ./app/db/schema.ts",
    "db:studio": "drizzle-kit studio --verbose --port 5555",
    "db:seed": "tsx ./app/db/seed.ts",
}
Stack Overflow
SQLite foreign key mismatch error
Why am I getting a SQLite "foreign key mismatch" error when executing script below? DELETE FROM rlsconfig WHERE importer_config_id=2 and program_mode_config_id=1 Here is main table definition:...
stephen
stephen•8mo ago
sqlite> .schema menu_recipes
CREATE TABLE `menu_recipes` (
        `menu_id` text NOT NULL,
        `recipe_id` text NOT NULL,
        `added_by` text,
        `created_date` text,
        `updated_date` text,
        `menu_course_id` text,
        PRIMARY KEY(`menu_id`, `recipe_id`),
        FOREIGN KEY (`menu_id`) REFERENCES `menus`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`recipe_id`) REFERENCES `recipes`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`added_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
        FOREIGN KEY (`menu_course_id`) REFERENCES `menu_courses`(`id`) ON UPDATE cascade ON DELETE no action
);
sqlite> .schema menu_course_recipes
CREATE TABLE `menu_course_recipes` (
        `menu_course_id` text NOT NULL,
        `recipe_id` text NOT NULL,
        PRIMARY KEY(`menu_course_id`, `recipe_id`),
        FOREIGN KEY (`menu_course_id`) REFERENCES `menu_courses`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`recipe_id`) REFERENCES `menu_recipes`(`id`) ON UPDATE no action ON DELETE cascade
);
sqlite> .schema menu_recipes
CREATE TABLE `menu_recipes` (
        `menu_id` text NOT NULL,
        `recipe_id` text NOT NULL,
        `added_by` text,
        `created_date` text,
        `updated_date` text,
        `menu_course_id` text,
        PRIMARY KEY(`menu_id`, `recipe_id`),
        FOREIGN KEY (`menu_id`) REFERENCES `menus`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`recipe_id`) REFERENCES `recipes`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`added_by`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
        FOREIGN KEY (`menu_course_id`) REFERENCES `menu_courses`(`id`) ON UPDATE cascade ON DELETE no action
);
sqlite> .schema menu_course_recipes
CREATE TABLE `menu_course_recipes` (
        `menu_course_id` text NOT NULL,
        `recipe_id` text NOT NULL,
        PRIMARY KEY(`menu_course_id`, `recipe_id`),
        FOREIGN KEY (`menu_course_id`) REFERENCES `menu_courses`(`id`) ON UPDATE no action ON DELETE cascade,
        FOREIGN KEY (`recipe_id`) REFERENCES `menu_recipes`(`id`) ON UPDATE no action ON DELETE cascade
);
My questions: 1. Should drizzle error if I don't have the correct relations? 2. If I find that I have a missing relation, what's the proper order of operations for that to get picked up by the schema generation to create a migration? 3. Is there a way to "force" this change to occur?
Want results from more Discord servers?
Add your server