fhanna Posts - Answer Overflow

fhanna

•Created by fhanna on 4/11/2024 in #help

Generating migrations after pull tries to recreate existing FK constraints with different name

Hi there! I'm starting to implement Drizzle at work. I introspected our database and generate table models without a problem. After that, to test out migrations, I deleted some columns and decided to create my first migration. The problem is that the migration created, beign the first one, besides trying to deleted the columns is also trying to drop all existing FOREIGN KEY constraints and recreating them with a new name. Example: 0001_absent_punisher.sql

ALTER TABLE "v1"."users" DROP CONSTRAINT "users_company_id_fkey";
ALTER TABLE "v1"."users" DROP CONSTRAINT "users_building_id_fkey";
ALTER TABLE "v1"."users" DROP CONSTRAINT "users_device_token_fkey";

-- ...

DO $$ BEGIN
 ALTER TABLE "v1"."users" ADD CONSTRAINT "users_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "v1"."sites"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "v1"."users" ADD CONSTRAINT "users_device_token_devices_token_fk" FOREIGN KEY ("device_token") REFERENCES "v1"."devices"("token") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "v1"."sites" ADD CONSTRAINT "sites_company_id_companies_id_fk" FOREIGN KEY ("company_id") REFERENCES "v1"."companies"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

-- ...

ALTER TABLE "v1"."users" DROP CONSTRAINT "users_company_id_fkey";
ALTER TABLE "v1"."users" DROP CONSTRAINT "users_building_id_fkey";
ALTER TABLE "v1"."users" DROP CONSTRAINT "users_device_token_fkey";

-- ...

DO $$ BEGIN
 ALTER TABLE "v1"."users" ADD CONSTRAINT "users_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "v1"."sites"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "v1"."users" ADD CONSTRAINT "users_device_token_devices_token_fk" FOREIGN KEY ("device_token") REFERENCES "v1"."devices"("token") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "v1"."sites" ADD CONSTRAINT "sites_company_id_companies_id_fk" FOREIGN KEY ("company_id") REFERENCES "v1"."companies"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

-- ...

As you can see, it's first dropping FK constraints like users_company_id_fkey and recreating it by the name of users_site_id_sites_id_fk. Is this expected? Could I somehow specify the FK constraint object name in the table model to avoid this?

1 replies

Gaming

Programming