How to delete with cascade?

I'm using postgres with the following schema (reduced to the important parts):

export const worlds = pgTable('worlds', {
  id: uuid('id').defaultRandom().primaryKey()
})
export const users_to_worlds = pgTable(
  'users_to_worlds',
  {
    userId: varchar('user_id', { length: 32 })
      .references(() => users.id)
      .notNull(),
    worldId: uuid('world_id')
      .references(() => worlds.id)
      .notNull(),
  },
  (table) => {
    return {
      pk: primaryKey(table.userId, table.worldId),
    }
  }
)

export const worlds = pgTable('worlds', {
  id: uuid('id').defaultRandom().primaryKey()
})
export const users_to_worlds = pgTable(
  'users_to_worlds',
  {
    userId: varchar('user_id', { length: 32 })
      .references(() => users.id)
      .notNull(),
    worldId: uuid('world_id')
      .references(() => worlds.id)
      .notNull(),
  },
  (table) => {
    return {
      pk: primaryKey(table.userId, table.worldId),
    }
  }
)

export const worlds = pgTable('worlds', {
  id: uuid('id').defaultRandom().primaryKey()
})
export const users_to_worlds = pgTable(
  'users_to_worlds',
  {
    userId: varchar('user_id', { length: 32 })
      .references(() => users.id)
      .notNull(),
    worldId: uuid('world_id')
      .references(() => worlds.id)
      .notNull(),
  },
  (table) => {
    return {
      pk: primaryKey(table.userId, table.worldId),
    }
  }
)

export const worlds = pgTable('worlds', {
  id: uuid('id').defaultRandom().primaryKey()
})
export const users_to_worlds = pgTable(
  'users_to_worlds',
  {
    userId: varchar('user_id', { length: 32 })
      .references(() => users.id)
      .notNull(),
    worldId: uuid('world_id')
      .references(() => worlds.id)
      .notNull(),
  },
  (table) => {
    return {
      pk: primaryKey(table.userId, table.worldId),
    }
  }
)

And I'm trying to implement an api call to delete a

world

world

world

world. Due to the reference to the world in the users_to_worlds I get the error:

Error: update or delete on table "worlds" violates foreign key constraint "users_to_worlds_world_id_worlds_id_fk" on table "users_to_worlds"

Error: update or delete on table "worlds" violates foreign key constraint "users_to_worlds_world_id_worlds_id_fk" on table "users_to_worlds"

Error: update or delete on table "worlds" violates foreign key constraint "users_to_worlds_world_id_worlds_id_fk" on table "users_to_worlds"

Error: update or delete on table "worlds" violates foreign key constraint "users_to_worlds_world_id_worlds_id_fk" on table "users_to_worlds"

I believe what I want to use is a CASCADE delete where everything referencing a world is deleted when I delete a world. Is this possible through Drizzle? I can't seem to find anything on this.

Andrii Sherman•5/24/23, 1:07 PM

You can add CASCADE
We didn't update web docs with it, thanks for pointing that out!

userId: varchar('user_id', { length: 32 }).references(() => users.id, { onDelete: 'cascade' }).notNull(),

userId: varchar('user_id', { length: 32 }).references(() => users.id, { onDelete: 'cascade' }).notNull(),

This object has onUpdateonUpdate as well and all possible values for it

Andrii Sherman•5/24/23, 1:08 PM

Also if you will just add those and generate migrations you may not see migrations files for it. We have this issue for MySQL, which I almost fixed and soon well deploy to latest

You can try it with Postgres and if it won't generate new migration - just ping me I'll fix it for Postgres as well

https://github.com/drizzle-team/drizzle-kit-mirror/issues/56

iqrowOP•5/24/23, 1:11 PM

ALTER TABLE "users_to_worlds" DROP CONSTRAINT "users_to_worlds_user_id_users_id_fk";
DO $$ BEGIN
 ALTER TABLE "users_to_worlds" ADD CONSTRAINT "users_to_worlds_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "users_to_worlds" DROP CONSTRAINT "users_to_worlds_world_id_worlds_id_fk";
DO $$ BEGIN
 ALTER TABLE "users_to_worlds" ADD CONSTRAINT "users_to_worlds_world_id_worlds_id_fk" FOREIGN KEY ("world_id") REFERENCES "worlds"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "users_to_worlds" DROP CONSTRAINT "users_to_worlds_user_id_users_id_fk";
DO $$ BEGIN
 ALTER TABLE "users_to_worlds" ADD CONSTRAINT "users_to_worlds_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "users_to_worlds" DROP CONSTRAINT "users_to_worlds_world_id_worlds_id_fk";
DO $$ BEGIN
 ALTER TABLE "users_to_worlds" ADD CONSTRAINT "users_to_worlds_world_id_worlds_id_fk" FOREIGN KEY ("world_id") REFERENCES "worlds"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Looks good to me, but I don't read migrations normally

iqrowOP•5/24/23, 1:14 PM

Works

Andrii Sherman•5/24/23, 1:16 PM

Great!

Andrii Sherman•5/24/23, 1:17 PM

so it's just MySQL bug

How to delete with cascade?

Similar Threads

Similar Threads

Similar Threads