P
Prisma•6mo ago
💜Clari

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
💜ClariOP•6mo 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

Did you find this page helpful?