💜Clari Comments - Answer Overflow

💜Clari

•Created by 💜Clari on 7/31/2024 in #help-and-questions

Removing enum value fails on foreign key

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

5 replies

PPrisma

•Created by 💜Clari on 7/31/2024 in #help-and-questions

Removing enum value fails on foreign key

(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;

5 replies

PPrisma

•Created by 💜Clari on 7/31/2024 in #help-and-questions

Removing enum value fails on foreign key

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;

5 replies

Gaming

Programming