Deletion causes error because it violates foreign key constraint when it shouldnt

Here is the relevant schema: the "one":

export const aditionalService = pgTable("aditionalService", {
  id: prefixedUlid(aditionalServicePrefix).$type<aditionalServiceID>(),

  name: varchar("name").unique().notNull(),
  price: integer("price").notNull(),

  ...defaultTimestamps,
});

export const aditionalServiceRelations = relations(
  aditionalService,
  ({ many }) => ({
    tasks: many(task,),
  }),
);

export const aditionalService = pgTable("aditionalService", {
  id: prefixedUlid(aditionalServicePrefix).$type<aditionalServiceID>(),

  name: varchar("name").unique().notNull(),
  price: integer("price").notNull(),

  ...defaultTimestamps,
});

export const aditionalServiceRelations = relations(
  aditionalService,
  ({ many }) => ({
    tasks: many(task,),
  }),
);

the "many":

export const task = pgTable("task", {
  id: prefixedUlid(taskPrefix).$type<taskID>(),

  name: varchar("name").notNull(),
  isExternal: boolean("isExternal").default(false).notNull(),

  aditionalServiceId: varchar("aditionalServiceId")
    .$type<aditionalServiceID>()
    .notNull()
    .references(() => aditionalService.id, { onDelete: 'cascade' }),

  ...defaultTimestamps,
});

export const taskRelations = relations(task, ({ one }) => ({
  aditionalService: one(aditionalService, {
    fields: [task.aditionalServiceId],
    references: [aditionalService.id],
    
  }),
}));

export const task = pgTable("task", {
  id: prefixedUlid(taskPrefix).$type<taskID>(),

  name: varchar("name").notNull(),
  isExternal: boolean("isExternal").default(false).notNull(),

  aditionalServiceId: varchar("aditionalServiceId")
    .$type<aditionalServiceID>()
    .notNull()
    .references(() => aditionalService.id, { onDelete: 'cascade' }),

  ...defaultTimestamps,
});

export const taskRelations = relations(task, ({ one }) => ({
  aditionalService: one(aditionalService, {
    fields: [task.aditionalServiceId],
    references: [aditionalService.id],
    
  }),
}));

When I attempt to delete an additonalService in theory the tasks should cascade from what I've been researching about onDelete actions and foreign key constraints. The example in the documentation seems for onDelete seems to also be a one to many so I am not sure what I'm doing wrong. https://orm.drizzle.team/docs/rqb#foreign-key-actions:~:text=In%20the%20following%20example%2C%20adding%20onDelete%3A%20%27cascade%27%20to%20the%20author%20field%20on%20the%20posts%20schema%20means%20that%20deleting%20the%20user%20will%20also%20delete%20all%20related%20Post%20records. Here is the error :

error: update or delete on table "aditionalService" violates foreign key constraint "task_aditionalServiceId_aditionalService_id_fk" on table "task"

error: update or delete on table "aditionalService" violates foreign key constraint "task_aditionalServiceId_aditionalService_id_fk" on table "task"

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

1 Reply

The cultured oneOP•11mo ago

Turns out the issue I was having was some migrations were literally not applying so the db was out of sync with the schema. My bad! (This issue is solved tho)

Gaming

Programming

Deletion causes error because it violates foreign key constraint when it shouldnt