40 lines
2.4 KiB
PL/PgSQL
40 lines
2.4 KiB
PL/PgSQL
-- PostgreSQL 17: reuse investmoz.provincia/distrito; do not create duplicate masters.
|
|
-- Run after 001_mne_schema.sql and the investmoz territorial DDL/import.
|
|
BEGIN;
|
|
SELECT pg_advisory_xact_lock(290926, 170001);
|
|
ALTER TABLE mne.locations ADD COLUMN IF NOT EXISTS province_id integer;
|
|
ALTER TABLE mne.locations ADD COLUMN IF NOT EXISTS district_id integer;
|
|
|
|
-- A composite key prevents selecting a district belonging to another province.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_distrito_id_provincia
|
|
ON investmoz.distrito(id, provincia_id);
|
|
|
|
DO $links$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM mne.locations WHERE province_id IS NULL) THEN
|
|
RAISE EXCEPTION 'Existing locations require an explicit province mapping before this migration';
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid='mne.locations'::regclass AND conname='fk_locations_province') THEN
|
|
ALTER TABLE mne.locations ADD CONSTRAINT fk_locations_province
|
|
FOREIGN KEY (province_id) REFERENCES investmoz.provincia(id)
|
|
ON UPDATE CASCADE ON DELETE RESTRICT;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid='mne.locations'::regclass AND conname='fk_locations_district') THEN
|
|
ALTER TABLE mne.locations ADD CONSTRAINT fk_locations_district
|
|
FOREIGN KEY (district_id, province_id) REFERENCES investmoz.distrito(id, provincia_id)
|
|
ON UPDATE CASCADE ON DELETE RESTRICT;
|
|
END IF;
|
|
-- These existing administrative tables contain Mozambique only.
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid='mne.locations'::regclass AND conname='ck_locations_territory_country') THEN
|
|
ALTER TABLE mne.locations ADD CONSTRAINT ck_locations_territory_country CHECK(country_code='MZ');
|
|
END IF;
|
|
END
|
|
$links$;
|
|
ALTER TABLE mne.locations ALTER COLUMN province_id SET NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS ix_locations_territory ON mne.locations(country_code, province_id, district_id);
|
|
COMMENT ON COLUMN mne.locations.province_id IS 'Existing master: investmoz.provincia.id (integer).';
|
|
COMMENT ON COLUMN mne.locations.district_id IS 'Existing master: investmoz.distrito.id; must belong to province_id.';
|
|
COMMENT ON COLUMN mne.locations.province_code IS 'Legacy optional field retained for compatibility; province_id is the authoritative relation.';
|
|
COMMENT ON COLUMN mne.locations.district_code IS 'Legacy optional field retained for compatibility; district_id is the authoritative relation.';
|
|
COMMIT;
|