Cannot drop index 'idx': needed in a foreign key constraint

So I had this table with its migration in the database:

export const userProfile = mysqlTable('user_profile', {
        id: int("id").primaryKey().autoincrement()
    userId: int('user_id'),
    profilePicture: text('profile_picture').notNull(),
    updatedAt: datetime('updated_at'),
    bio: text('bio').notNull(),
    age: int('age').notNull(),
}, (userProfile) => ({
     userIdIndex: uniqueIndex("user_id_index").on(userProfile.userId)
}));

export const userProfile = mysqlTable('user_profile', {
        id: int("id").primaryKey().autoincrement()
    userId: int('user_id'),
    profilePicture: text('profile_picture').notNull(),
    updatedAt: datetime('updated_at'),
    bio: text('bio').notNull(),
    age: int('age').notNull(),
}, (userProfile) => ({
     userIdIndex: uniqueIndex("user_id_index").on(userProfile.userId)
}));

Then modified it to the following: ( dropped id and made the userId the primaryKey )

export const userProfile = mysqlTable('user_profile', {
    userId: int('user_id')
              .primaryKey()
          .references(() => users.id),,
    profilePicture: text('profile_picture').notNull(),
    updatedAt: datetime('updated_at'),
    bio: text('bio').notNull(),
    age: int('age').notNull(),
})

export const userProfile = mysqlTable('user_profile', {
    userId: int('user_id')
              .primaryKey()
          .references(() => users.id),,
    profilePicture: text('profile_picture').notNull(),
    updatedAt: datetime('updated_at'),
    bio: text('bio').notNull(),
    age: int('age').notNull(),
})

When I'm trying to apply the migration, I get the error: "Cannot drop index 'user_id_idx': needed in a foreign key constraint" How can this type of thing be safely done? And is it a drizzle problem?

1 Reply

SebastianOP•17mo ago

This was the SQL code that was throwing the error: DROP INDEX user_id_idx ON user_profile;--> statement-breakpoint ALTER TABLE user_profile ADD PRIMARY KEY (user_id);--> statement-breakpoint ALTER TABLE user_profile DROP COLUMN id;--> statement-breakpoint Changing the order of the statements makes the script run successfully ALTER TABLE user_profile DROP COLUMN id;--> statement-breakpoint ALTER TABLE user_profile ADD PRIMARY KEY (user_id);--> statement-breakpoint DROP INDEX user_id_idx ON user_profile;--> statement-breakpoint

Gaming

Programming

Cannot drop index 'idx': needed in a foreign key constraint