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

And I'm trying to implement an api call to delete a 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"

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.

3 Replies

Andrii Sherman•2y ago

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 onUpdate as well and all possible values for it 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•2y ago

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 😄 Works 👍

Andrii Sherman•2y ago

Great! so it's just MySQL bug

Gaming

Programming

How to delete with cascade?