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

I have the following schema,

Users

Users

Users

Users <>

Teams

Teams

Teams

Teams

many to many
usersOnTeams - table to hold this relationship

m currently unable to delete a

Team

Team

Team

Team record due to the foreign key of the team still existing in the

usersOnTeams

usersOnTeams

usersOnTeams

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],
  }),
}))

// 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],
  }),
}))

monki boiOP•11/9/23, 6:44 AM

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•11/9/23, 7:03 AM

first the delete values from the referenced tables

monki boiOP•11/9/23, 7:11 AM

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

tomri•11/9/23, 7:41 AM

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)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•11/9/23, 11:03 AM

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

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

Similar Threads

Similar Threads

Similar Threads