Cannot drop primary key

I needed to change a table from a single field PK to a composite PK, using the following syntax

, (table) => {
  return {
    pk: primaryKey(table.playerID, table.teamID, table.competitionID, table.seasonID),
  };
});

, (table) => {
  return {
    pk: primaryKey(table.playerID, table.teamID, table.competitionID, table.seasonID),
  };
});

I ran the drizzle kit generate, and it spat out

ALTER TABLE `teamStats` DROP PRIMARY KEY;
ALTER TABLE `teamStats` ADD PRIMARY KEY(`competitionID`,`seasonID`,`teamID`);

ALTER TABLE `teamStats` DROP PRIMARY KEY;
ALTER TABLE `teamStats` ADD PRIMARY KEY(`competitionID`,`seasonID`,`teamID`);

but this doesn't work. I tried runnign it manually, with the addition of SET foreign_key_checks = 0; at the start, but no luck. A quick google suggests the FK needs removing, but drizzle kit isn't adding that in. Using drizzle kit 0.19.12

11 Replies

mr_pabloOP•16mo ago

FYI, the 3 fields used for the composite OK, are also FKs, if that wasn't obvious 🙂 To make it work, I have to edit the SQL file, and add a drop constraint for each FK before dropping the PK, then afterwards, re add them. I believe Drizzle Kit should understand this @Andrew Sherman

Andrii Sherman•16mo ago

is it sqlite?

mr_pabloOP•16mo ago

mysql i think drizzle kit also needs to add the foreign_key_checks statements

Andrii Sherman•15mo ago

good, agree, would be grat if you can create an issue for that so I can easily track it and get into work

mr_pabloOP•15mo ago

will do Any idea about the FK issue? i'll make 2 issues, one for the foreign_key_checks issue and one about the need to drop FK's and re add them @Andrew Sherman https://github.com/drizzle-team/drizzle-orm/issues/1144 and https://github.com/drizzle-team/drizzle-orm/issues/1143

Andrii Sherman•15mo ago

Perfect I’ll take a look at it after I’ll finish batch api and a few more things in backlog

mr_pabloOP•15mo ago

Thank you!

Andrii Sherman•15mo ago

JSON with postgresjs is very important

mr_pabloOP•15mo ago

😛

Andrii Sherman•15mo ago

Some strange bug happening there

mr_pabloOP•15mo ago

appreciate you taking your time to respond and look into it

Gaming

Programming

Cannot drop primary key