32 lines
1.2 KiB
SQL
32 lines
1.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "EyeColor" AS ENUM ('Blue', 'Green', 'Brown', 'Hazel', 'Gray', 'Amber');
|
|
|
|
-- AlterTable: CitizenshipApplication
|
|
ALTER TABLE "CitizenshipApplication" DROP COLUMN IF EXISTS "birthRegion";
|
|
|
|
ALTER TABLE "CitizenshipApplication"
|
|
ALTER COLUMN "height" TYPE INTEGER USING "height"::integer;
|
|
|
|
ALTER TABLE "CitizenshipApplication"
|
|
ALTER COLUMN "eyeColor" TYPE "EyeColor" USING "eyeColor"::"EyeColor";
|
|
|
|
-- AlterTable: IdentityDocument — add address, allow multiple documents per citizenship
|
|
ALTER TABLE "IdentityDocument" ADD COLUMN "address" TEXT;
|
|
|
|
UPDATE "IdentityDocument" AS d
|
|
SET "address" = a."address"
|
|
FROM "Citizenship" AS c
|
|
JOIN "CitizenshipApplication" AS a ON a."id" = c."applicationId"
|
|
WHERE d."citizenshipId" = c."id";
|
|
|
|
ALTER TABLE "IdentityDocument" ALTER COLUMN "address" SET NOT NULL;
|
|
|
|
DROP INDEX IF EXISTS "IdentityDocument_citizenshipId_key";
|
|
|
|
CREATE INDEX "IdentityDocument_citizenshipId_issuedAt_idx" ON "IdentityDocument"("citizenshipId", "issuedAt");
|
|
|
|
-- AddForeignKey: CitizenshipApplication.regionId -> Region
|
|
ALTER TABLE "CitizenshipApplication"
|
|
ADD CONSTRAINT "CitizenshipApplication_regionId_fkey"
|
|
FOREIGN KEY ("regionId") REFERENCES "Region"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|