-- Restore original table names DROP TABLE "plotMetadata"; -- name conflicts with PlotMetadata and has less data ALTER TABLE "PlotMetadata" RENAME TO "plotMetadata"; ALTER TABLE "PlotObservations" RENAME TO "plotObservations"; -- Delete rows that do not satisfy foreign key constraints DELETE FROM stems WHERE "PlotObsID" IN ( SELECT stems."PlotObsID" FROM stems LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."PlotObsID" WHERE "plotObservations"."PlotObsID" IS NULL ); -- Enable cascading deletes CREATE INDEX ON "plotMetadata" (project_id); ALTER TABLE "plotMetadata" ADD FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE ON DELETE CASCADE; CREATE INDEX ON "plotObservations" ("PlotID"); ALTER TABLE "plotObservations" ADD FOREIGN KEY ("PlotID") REFERENCES "plotMetadata"("PlotID") ON UPDATE CASCADE ON DELETE CASCADE; CREATE INDEX ON stems ("PlotObsID"); ALTER TABLE stems ADD FOREIGN KEY ("PlotObsID") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE; -- Remove private data that should not be publicly visible DELETE FROM "plotMetadata" WHERE "AccessCode" = 1; -- Ensure globally unique column names when tables are joined ALTER TABLE "lookup_MethodCode" RENAME "Description" TO "lookup_MethodCode_Description";