| 1 |
6167
|
aaronmk
|
-- Restore original table names
|
| 2 |
|
|
ALTER TABLE "PlotMetadata" RENAME TO "plotMetadata";
|
| 3 |
|
|
ALTER TABLE "PlotObservations" RENAME TO "plotObservations";
|
| 4 |
|
|
|
| 5 |
|
|
-- Delete rows that do not satisfy foreign key constraints
|
| 6 |
8067
|
aaronmk
|
DELETE FROM stems WHERE "plotobs_id" IN (
|
| 7 |
|
|
SELECT stems."plotobs_id"
|
| 8 |
6167
|
aaronmk
|
FROM stems
|
| 9 |
8067
|
aaronmk
|
LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."plotobs_id"
|
| 10 |
6167
|
aaronmk
|
WHERE "plotObservations"."PlotObsID" IS NULL
|
| 11 |
|
|
);
|
| 12 |
|
|
|
| 13 |
6162
|
aaronmk
|
-- Enable cascading deletes
|
| 14 |
|
|
|
| 15 |
|
|
CREATE INDEX ON "plotMetadata" (project_id);
|
| 16 |
|
|
ALTER TABLE "plotMetadata" ADD FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
| 17 |
|
|
|
| 18 |
|
|
CREATE INDEX ON "plotObservations" ("PlotID");
|
| 19 |
|
|
ALTER TABLE "plotObservations" ADD FOREIGN KEY ("PlotID") REFERENCES "plotMetadata"("PlotID") ON UPDATE CASCADE ON DELETE CASCADE;
|
| 20 |
|
|
|
| 21 |
8067
|
aaronmk
|
CREATE INDEX ON stems ("plotobs_id");
|
| 22 |
|
|
ALTER TABLE stems ADD FOREIGN KEY ("plotobs_id") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE;
|
| 23 |
6163
|
aaronmk
|
|
| 24 |
|
|
-- Remove private data that should not be publicly visible
|
| 25 |
|
|
DELETE FROM "plotMetadata" WHERE "AccessCode" = 1;
|
| 26 |
6166
|
aaronmk
|
|
| 27 |
|
|
-- Ensure globally unique column names when tables are joined
|
| 28 |
|
|
ALTER TABLE "lookup_MethodCode" RENAME "Description" TO "lookup_MethodCode_Description";
|
