Drizzle Team•3mo ago

PlanetScale drizzle-kit push: Cannot drop index: needed in a foreign key constraint

Whenever I run drizzle-kit push against PlanetScale, drizzle drops and recreates primary key constraints, foreign key constraints, unique constraints etc. and keeps running into issues doing so. I've created a minimal example to illustrate the issue. In this example, the first drizzle-kit push goes through, all subsequent pushes throw an error without even changing the schema at all.

DROP INDEX `unique_name` ON `table1`;
ALTER TABLE `table1` ADD CONSTRAINT `unique_name` UNIQUE(`table2_id`,`name`);

Cannot drop index 'unique_name': needed in a foreign key constraint (errno 1553)

DROP INDEX `unique_name` ON `table1`;
ALTER TABLE `table1` ADD CONSTRAINT `unique_name` UNIQUE(`table2_id`,`name`);

Cannot drop index 'unique_name': needed in a foreign key constraint (errno 1553)

import { foreignKey, mysqlTable, unique, varchar } from 'drizzle-orm/mysql-core'

export const table1 = mysqlTable(
  'table1',
  {
    id: varchar('id', { length: 256 }).primaryKey(),
    table2Id: varchar('table2_id', { length: 256 }).notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  (table) => ({
    table2Fk: foreignKey({
      columns: [table.table2Id],
      foreignColumns: [table2.id],
      name: 'table1_table2_fk',
    }).onDelete('cascade'),
    uniqueName: unique('unique_name').on(table.table2Id, table.name),
  })
)

export const table2 = mysqlTable('table2', {
  id: varchar('id', { length: 256 }).primaryKey(),
})

import { foreignKey, mysqlTable, unique, varchar } from 'drizzle-orm/mysql-core'

export const table1 = mysqlTable(
  'table1',
  {
    id: varchar('id', { length: 256 }).primaryKey(),
    table2Id: varchar('table2_id', { length: 256 }).notNull(),
    name: varchar('name', { length: 256 }).notNull(),
  },
  (table) => ({
    table2Fk: foreignKey({
      columns: [table.table2Id],
      foreignColumns: [table2.id],
      name: 'table1_table2_fk',
    }).onDelete('cascade'),
    uniqueName: unique('unique_name').on(table.table2Id, table.name),
  })
)

export const table2 = mysqlTable('table2', {
  id: varchar('id', { length: 256 }).primaryKey(),
})

2 Replies

BEEIRLOP•3mo ago

This is prob. related https://discord.com/channels/1043890932593987624/1172329418195226666/1172329418195226666 but should've been fixed a while ago?

Mads•3mo ago

I experience the same.

Gaming

Programming

PlanetScale drizzle-kit push: Cannot drop index: needed in a foreign key constraint