Cannot drop index 'PRIMARY' - planetscale

when I try to push to planetscale I get the error:

Cannot drop index 'PRIMARY': needed in a foreign key constraint (errno 1553) (sqlstate HY000)

Cannot drop index 'PRIMARY': needed in a foreign key constraint (errno 1553) (sqlstate HY000)

In order to successfully push I need to drop all the tables before pushing. I'm using the Foreign key constraints Beta at planetscale. When running push with the verbose flag, i see the following:

Warning  You are about to execute current statements:
ALTER TABLE `petLog_usersToPets` DROP PRIMARY KEY;
ALTER TABLE `petLog_petRecommendedWeights` MODIFY COLUMN `createdAt` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `petLog_petWeights` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());
ALTER TABLE `petLog_pets` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());
ALTER TABLE `petLog_users` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());
ALTER TABLE `petLog_usersToPets` ADD PRIMARY KEY(`userId`,`petId`);

Cannot drop index 'PRIMARY': needed in a foreign key constraint Sql: "alter table petLog_usersToPets drop primary key"

Warning  You are about to execute current statements:
ALTER TABLE `petLog_usersToPets` DROP PRIMARY KEY;
ALTER TABLE `petLog_petRecommendedWeights` MODIFY COLUMN `createdAt` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `petLog_petWeights` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());
ALTER TABLE `petLog_pets` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());
ALTER TABLE `petLog_users` MODIFY COLUMN `createdAt` timestamp DEFAULT (now());
ALTER TABLE `petLog_usersToPets` ADD PRIMARY KEY(`userId`,`petId`);

Cannot drop index 'PRIMARY': needed in a foreign key constraint Sql: "alter table petLog_usersToPets drop primary key"

I'm running drizzle-kit": 0.20.13. my tables:

const mysqlTable = mysqlTableCreator((name) => `petLog_${name}`);

// truncated for brewity
export const usersTable = mysqlTable('users', {
    id: varchar('id', {
        length: 255,
    }).primaryKey(),
        //...rest
});

export const petsTable = mysqlTable('pets', {
    id: varchar('id', {
        length: 255,
    }).primaryKey(),
        //...rest
});

export const usersToPetsTable = mysqlTable(
    'usersToPets',
    {
        userId: varchar('userId', {
            length: 255,
        })
            .notNull()
            .references(() => usersTable.id),
        petId: varchar('petId', {
            length: 255,
        })
            .notNull()
            .references(() => petsTable.id),
    },
    (table) => ({
        pk: primaryKey({ columns: [table.userId, table.petId] }),
    }),
);

const mysqlTable = mysqlTableCreator((name) => `petLog_${name}`);

// truncated for brewity
export const usersTable = mysqlTable('users', {
    id: varchar('id', {
        length: 255,
    }).primaryKey(),
        //...rest
});

export const petsTable = mysqlTable('pets', {
    id: varchar('id', {
        length: 255,
    }).primaryKey(),
        //...rest
});

export const usersToPetsTable = mysqlTable(
    'usersToPets',
    {
        userId: varchar('userId', {
            length: 255,
        })
            .notNull()
            .references(() => usersTable.id),
        petId: varchar('petId', {
            length: 255,
        })
            .notNull()
            .references(() => petsTable.id),
    },
    (table) => ({
        pk: primaryKey({ columns: [table.userId, table.petId] }),
    }),
);

3 Replies

Kyrre Gjerstad•9mo ago

I suspect there may be an issue with how drizzle-kit and planetscale interfaces. When I run pnpm drizzle-kit push:mysql --config=drizzle.config.ts immediately after a successful push, it wants to perform the following action, even though there are no schema changes.

ALTER TABLE `petLog_usersToPets` DROP PRIMARY KEY;
ALTER TABLE `petLog_usersToPets` ADD PRIMARY KEY(`userId`,`petId`);

ALTER TABLE `petLog_usersToPets` DROP PRIMARY KEY;
ALTER TABLE `petLog_usersToPets` ADD PRIMARY KEY(`userId`,`petId`);

Kyrre Gjerstad•9mo ago

for anyone searching for the same, this patch worked for me: https://github.com/drizzle-team/drizzle-orm/issues/1428#issuecomment-1916722940 would be great to have an official fix for this though!

GitHub

[BUG]: ER_WRONG_AUTO_KEY - Drizzle Kit not detecting primary keys ·...

What version of drizzle-orm are you using? 0.28.6 What version of drizzle-kit are you using? 0.19.13 Describe the Bug Drizzle-kit isn't introspecting the correct primary keys from planet scale....

elverly•6mo ago

Thank you so much! This has been incredibly helpful

Gaming

Programming

Cannot drop index 'PRIMARY' - planetscale