Removing enum value fails on foreign key
I'm getting the below error when i try to remove an enum value from the database
Error: P3006
Migration `20240731215417_drop_rqi_enum_val` failed to apply cleanly to the shadow database.
Error:
db error: ERROR: foreign key constraint "Notification_certificationType_profileId_fkey" cannot be implemented
DETAIL: Key columns "certificationType" and "code" are of incompatible types: "CertificationType" and "CertificationType_new".
0: sql_schema_connector::validate_migrations
with namespaces=None
at schema-engine/connectors/sql-schema-connector/src/lib.rs:312
1: schema_core::state::DevDiagnostic
at schema-engine/core/src/state.rs:270
Error: P3006
Migration `20240731215417_drop_rqi_enum_val` failed to apply cleanly to the shadow database.
Error:
db error: ERROR: foreign key constraint "Notification_certificationType_profileId_fkey" cannot be implemented
DETAIL: Key columns "certificationType" and "code" are of incompatible types: "CertificationType" and "CertificationType_new".
0: sql_schema_connector::validate_migrations
with namespaces=None
at schema-engine/connectors/sql-schema-connector/src/lib.rs:312
1: schema_core::state::DevDiagnostic
at schema-engine/core/src/state.rs:270
1 Reply
The migration:
(shortened) schema for Notification table is
ended up solving it by
BEGIN;
CREATE TYPE "CertificationType_new" AS ENUM (); -- truncated bc long
ALTER TABLE "SpecialtyCertification"
ALTER COLUMN "certificationType"
TYPE "CertificationType_new"
USING ("certificationType"::text::"CertificationType_new");
ALTER TABLE "NurseCertification"
ALTER COLUMN "code"
TYPE "CertificationType_new"
USING ("code"::text::"CertificationType_new");
ALTER TABLE "Notification"
ALTER COLUMN "certificationType"
TYPE "CertificationType_new"
USING ("certificationType"::text::"CertificationType_new");
DROP TYPE "CertificationType";
ALTER TYPE "CertificationType_new"
RENAME TO "CertificationType";
COMMIT;
BEGIN;
CREATE TYPE "CertificationType_new" AS ENUM (); -- truncated bc long
ALTER TABLE "SpecialtyCertification"
ALTER COLUMN "certificationType"
TYPE "CertificationType_new"
USING ("certificationType"::text::"CertificationType_new");
ALTER TABLE "NurseCertification"
ALTER COLUMN "code"
TYPE "CertificationType_new"
USING ("code"::text::"CertificationType_new");
ALTER TABLE "Notification"
ALTER COLUMN "certificationType"
TYPE "CertificationType_new"
USING ("certificationType"::text::"CertificationType_new");
DROP TYPE "CertificationType";
ALTER TYPE "CertificationType_new"
RENAME TO "CertificationType";
COMMIT;
CREATE TABLE "Notification" (
id uuid NOT NULL,
type "NotificationType" NOT NULL,
"certificationType" "CertificationType",
"licenseId" uuid,
"profileId" uuid NOT NULL
);
ALTER TABLE ONLY "Notification"
ADD CONSTRAINT "Notification_pkey" PRIMARY KEY (id);
CREATE UNIQUE INDEX "Notification_certificationType_profileId_key" ON "Notification" USING btree ("certificationType", "profileId");
ALTER TABLE ONLY "Notification"
ADD CONSTRAINT "Notification_certificationType_profileId_fkey" FOREIGN KEY ("certificationType", "profileId") REFERENCES "NurseCertification"(code, "nurseProfileId") ON UPDATE CASCADE ON DELETE RESTRICT;
CREATE TABLE "Notification" (
id uuid NOT NULL,
type "NotificationType" NOT NULL,
"certificationType" "CertificationType",
"licenseId" uuid,
"profileId" uuid NOT NULL
);
ALTER TABLE ONLY "Notification"
ADD CONSTRAINT "Notification_pkey" PRIMARY KEY (id);
CREATE UNIQUE INDEX "Notification_certificationType_profileId_key" ON "Notification" USING btree ("certificationType", "profileId");
ALTER TABLE ONLY "Notification"
ADD CONSTRAINT "Notification_certificationType_profileId_fkey" FOREIGN KEY ("certificationType", "profileId") REFERENCES "NurseCertification"(code, "nurseProfileId") ON UPDATE CASCADE ON DELETE RESTRICT;
DROP CONSTRAINTing fkey before the alterenum transaction, then re-adding the constraint following