Prisma•3mo ago

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

💜Clari•3mo ago

The migration:

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;

(shortened) schema for Notification table is

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;

ended up solving it by DROP CONSTRAINTing fkey before the alterenum transaction, then re-adding the constraint following

Gaming

Programming

Removing enum value fails on foreign key