Drizzle Team•3mo ago

Update primary key from integer to text

Hi everyone, Im currently working on authorization schemas for my project. I have migrated and pushed a schema where all models have id (pk) as integers (see image of schema). I now want to change all these from integer to text. When I try to run

pnpm drizzle-kit push

pnpm drizzle-kit push

I get the following ALTER:

ALTER TABLE "Permission" ALTER COLUMN "id" SET DATA TYPE text;
ALTER TABLE "Group" ALTER COLUMN "id" SET DATA TYPE text;
ALTER TABLE "Group" ALTER COLUMN "parent_id" SET DATA TYPE text;
ALTER TABLE "GroupPermissions" ALTER COLUMN "group_id" SET DATA TYPE text;
ALTER TABLE "GroupPermissions" ALTER COLUMN "permission_id" SET DATA TYPE text;
ALTER TABLE "UserPermissions" ALTER COLUMN "permission_id" SET DATA TYPE text;
DO $$ BEGIN
 ALTER TABLE "UserGroups" ADD CONSTRAINT "UserGroups_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "UserGroups" ADD CONSTRAINT "UserGroups_group_id_Group_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."Group"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "Permission" ALTER COLUMN "id" SET DATA TYPE text;
ALTER TABLE "Group" ALTER COLUMN "id" SET DATA TYPE text;
ALTER TABLE "Group" ALTER COLUMN "parent_id" SET DATA TYPE text;
ALTER TABLE "GroupPermissions" ALTER COLUMN "group_id" SET DATA TYPE text;
ALTER TABLE "GroupPermissions" ALTER COLUMN "permission_id" SET DATA TYPE text;
ALTER TABLE "UserPermissions" ALTER COLUMN "permission_id" SET DATA TYPE text;
DO $$ BEGIN
 ALTER TABLE "UserGroups" ADD CONSTRAINT "UserGroups_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "UserGroups" ADD CONSTRAINT "UserGroups_group_id_Group_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."Group"("id") ON DELETE cascade ON UPDATE cascade;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

and when I try to accept these changes I get the following error:

Key columns "permission_id" and "id" are of incompatible types: integer and text.

Key columns "permission_id" and "id" are of incompatible types: integer and text.

How do I fix this?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Update primary key from integer to text