On delete cascade not working

I have the following tables:

export const exercises = sqliteTable("exercises", {
  id: integer("id").primaryKey().notNull(),
  created_at: integer("created_at").default(sql`(strftime('%Y-%m-%d', 'now'))`),
  exercise_name: text("exercise_name").notNull(),
});

export const labels = sqliteTable("labels", {
  id: integer("id").primaryKey().notNull(),
  name: text("name").notNull(),
  parent_id: integer("parent_id"),
});

export const exercise_label = sqliteTable("exercise_label", {
  exercise_id: integer("exercise_id")
    .references(() => exercises.id)
    .notNull(),
  label_id: integer("label_id")
    .references(() => labels.id)
    .notNull(),
});

export const sets = sqliteTable("sets", {
  id: integer("id").primaryKey().notNull(),
  exercise_id: integer("exercise_id")
    .references(() => exercises.id, { onDelete: "cascade" })
    .notNull(),
  date: integer("date")
    .default(sql`(strftime('%Y-%m-%d', 'now'))`)
    .notNull(),
  kg: integer("kg").notNull(),
  reps: integer("reps").notNull(),
});

export const exercises = sqliteTable("exercises", {
  id: integer("id").primaryKey().notNull(),
  created_at: integer("created_at").default(sql`(strftime('%Y-%m-%d', 'now'))`),
  exercise_name: text("exercise_name").notNull(),
});

export const labels = sqliteTable("labels", {
  id: integer("id").primaryKey().notNull(),
  name: text("name").notNull(),
  parent_id: integer("parent_id"),
});

export const exercise_label = sqliteTable("exercise_label", {
  exercise_id: integer("exercise_id")
    .references(() => exercises.id)
    .notNull(),
  label_id: integer("label_id")
    .references(() => labels.id)
    .notNull(),
});

export const sets = sqliteTable("sets", {
  id: integer("id").primaryKey().notNull(),
  exercise_id: integer("exercise_id")
    .references(() => exercises.id, { onDelete: "cascade" })
    .notNull(),
  date: integer("date")
    .default(sql`(strftime('%Y-%m-%d', 'now'))`)
    .notNull(),
  kg: integer("kg").notNull(),
  reps: integer("reps").notNull(),
});

Note that int the sets table, there is a statement that on deletion of an exercise, the sets should also be deleted. however, when i test this, the rows in sets with the exercise id of the deleted exercise are not deleted. Am I doing something wrong?

2 Replies

Angelelz•3w ago

Is your database schema in sync with your drizzle schema? Meaning, did you apply the migration after adding the on delete?

ThomsrOP•2w ago

Yes The issue was that I was not not enabling the foreign key pragma But I also don’t know how to do that with drizzle So I used the execAsync from the expo sqlite

Gaming

Programming

On delete cascade not working