Drizzle Team•13mo ago

Unable to delete from many to many relationship due to FK constraint

I have the following schema, Users <> Teams - many to many usersOnTeams - table to hold this relationship I'm currently unable to delete a Team record due to the foreign key of the team still existing in the usersOnTeams table record. I know it has something to do with cascading deletes but I'm not sure how. Any help is appreciated.

// USER
export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  clerkId: text('user_id').notNull().unique(),
  email: text('email').notNull(),
  firstName: text('first_name'),
  lastName: text('last_name'),
  avatarUrl: text('avatar_url'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
})

export const usersRelation = relations(users, ({ many }) => ({
  usersToTeams: many(usersOnTeams),
  videos: many(videos),
  uploads: many(uploads),
  cuepoints: many(cuepoints),
}))

// TEAM
export const teams = pgTable('teams', {
  id: uuid('id').defaultRandom().primaryKey(),
  clerkId: text('org_id').notNull().unique(),
  name: text('name').notNull(),
  slug: text('slug').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  logo: text('logo'),
})

export const teamsRelation = relations(teams, ({ many }) => ({
  usersToTeams: many(usersOnTeams),
  videos: many(videos),
}))

// USERSONTEAMS
export const usersOnTeams = pgTable(
  'usersOnTeams',
  {
    userId: text('user_id')
      .notNull()
      .references(() => users.clerkId),
    teamId: text('team_id')
      .notNull()
      .references(() => teams.clerkId),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.teamId),
  })
)

export const usersOnTeamsRelations = relations(usersOnTeams, ({ one }) => ({
  user: one(users, {
    fields: [usersOnTeams.userId],
    references: [users.clerkId],
  }),
  team: one(teams, {
    fields: [usersOnTeams.teamId],
    references: [teams.clerkId],
  }),
}))

// USER
export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  clerkId: text('user_id').notNull().unique(),
  email: text('email').notNull(),
  firstName: text('first_name'),
  lastName: text('last_name'),
  avatarUrl: text('avatar_url'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
})

export const usersRelation = relations(users, ({ many }) => ({
  usersToTeams: many(usersOnTeams),
  videos: many(videos),
  uploads: many(uploads),
  cuepoints: many(cuepoints),
}))

// TEAM
export const teams = pgTable('teams', {
  id: uuid('id').defaultRandom().primaryKey(),
  clerkId: text('org_id').notNull().unique(),
  name: text('name').notNull(),
  slug: text('slug').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  logo: text('logo'),
})

export const teamsRelation = relations(teams, ({ many }) => ({
  usersToTeams: many(usersOnTeams),
  videos: many(videos),
}))

// USERSONTEAMS
export const usersOnTeams = pgTable(
  'usersOnTeams',
  {
    userId: text('user_id')
      .notNull()
      .references(() => users.clerkId),
    teamId: text('team_id')
      .notNull()
      .references(() => teams.clerkId),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.teamId),
  })
)

export const usersOnTeamsRelations = relations(usersOnTeams, ({ one }) => ({
  user: one(users, {
    fields: [usersOnTeams.userId],
    references: [users.clerkId],
  }),
  team: one(teams, {
    fields: [usersOnTeams.teamId],
    references: [teams.clerkId],
  }),
}))

5 Replies

monki boiOP•13mo ago

The exact error msg:

ERROR: update or delete on table "teams" violates foreign key constraint "usersOnTeams_team_id_teams_org_id_fk" on table "usersOnTeams"
DETAIL: Key (org_id)=(org_XXXXXXXXX) is still referenced from table "usersOnTeams".

ERROR: update or delete on table "teams" violates foreign key constraint "usersOnTeams_team_id_teams_org_id_fk" on table "usersOnTeams"
DETAIL: Key (org_id)=(org_XXXXXXXXX) is still referenced from table "usersOnTeams".

tomri•13mo ago

first the delete values from the referenced tables

monki boiOP•13mo ago

I see, so there is no way to do this in one query?

tomri•13mo ago

there is a way. see the below code

    userId: text('user_id')
      .notNull()
      .references(() => users.clerkId),

    userId: text('user_id')
      .notNull()
      .references(() => users.clerkId),

    userId: text('user_id')
      .notNull()

    userId: text('user_id')
      .notNull()

both does the same job on high level when you introduce relations (refer here: https://orm.drizzle.team/docs/rqb#foreign-keys) so if you just remove references(() => table.column) you can delete them in a single query apparently. but id not recommend it tho

Drizzle Queries - DrizzleORM

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

Angelelz•13mo ago

Your other option is to add onDelete: "cascade" to the foreign key constrain if that is what you want

Gaming

Programming

Unable to delete from many to many relationship due to FK constraint