DT
Drizzle Team•12mo ago
The Cultured One

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 One
The Cultured OneOP•12mo 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)

Did you find this page helpful?