cascading update on subset from composite foreign-keys

user_1652954 0
Viewed 130

i have the below posted tables.As shown, the table GridCell has all the rows as composite primay-key, and they are foreign-keys in table GridCellOpDependentParticular. the same applies on the table OpDependentParticular. For the table GridCell, the initial values for the two columns geometryOfCellRepresentativeToTreatment and geometryOfCellRepresentativeToBuffer are set to POLYGON EMPTY or 010300000000000000. after the creation of the table i will update the latter two columns as follows:

sql = '''
        UPDATE GridCell set 
            geometryOfCellRepresentativeToTreatment = ST_GeomFromGeoJSON(fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON)
        WHERE 
            geometryOfCellRepresentativeToTreatment = 'POLYGON EMPTY' and fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON <> '' and fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON IS NOT NULL;

        UPDATE GridCell SET
            geometryOfCellRepresentativeToBuffer = ST_GeomFromGeoJSON(fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON)
        WHERE 
           geometryOfCellRepresentativeToBuffer = 'POLYGON EMPTY' and fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON <> '' and fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON IS NOT NULL;
        '''.format()
    self.cur.execute(sql)
    self.conn.commit()

what i want to achieve, is to make the two columns fk_gridCell_geometryOfCellRepresentativeToTreatment geometry and fk_gridCell_geometryOfCellRepresentativeToBuffer geometry in table GridCellOpDependentParticular automatically updated after i execute the aforementioned update-statement.

i searched for an answer but did not find answer for updating only some columns among composite foreign-keys without specifying the all the foreign-keys

code:

create table if not exists GridCell(
    fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON jsonb,
    fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON jsonb,
    geometryOfCellRepresentativeToTreatment geometry,
    geometryOfCellRepresentativeToBuffer geometry,
    fk_site_selectedSiteID text,
    fk_OpIndependentParticular_isTreatment boolean,
    fk_OpIndependentParticular_isBuffer boolean,
    fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge float8,
    fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge float8,
    foreign key (fk_site_selectedSiteID) references Site(selectedSiteID),
    foreign key (fk_OpIndependentParticular_isTreatment,fk_OpIndependentParticular_isBuffer,fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge) 
        references OpIndependentParticular(isTreatment,isBuffer,distanceFromCPOfTreatmentToNearestEdge,distanceFromCPOfBufferToNearestEdge),
    primary key (fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,geometryOfCellRepresentativeToTreatment,
    geometryOfCellRepresentativeToBuffer,fk_site_selectedSiteID,fk_OpIndependentParticular_isTreatment,fk_OpIndependentParticular_isBuffer,fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
    fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge)
    /*
     * UNIQUE (fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,geometryOfCellRepresentativeToTreatment,geometryOfCellRepresentativeToBuffer),
     * UNIQUE (fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,geometryOfCellRepresentativeToTreatment,geometryOfCellRepresentativeToBuffer,fk_site_selectedSiteID),
     * */
)
create table if not exists OpDependentParticular(
    AoCForCellsRepresentativeToTreatment float8,
    AoCForCellsRepresentativeToBuffer float8,
    AvgHPerWindowRepresentativeToTreatment float8,
    AvgHPerWindowRepresentativeToBuffer float8,
    primary key(AoCForCellsRepresentativeToTreatment,AoCForCellsRepresentativeToBuffer,AvgHPerWindowRepresentativeToTreatment,
    AvgHPerWindowRepresentativeToBuffer)
)
create table if not exists GridCellOpDependentParticular(
    fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON jsonb,
    fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON jsonb,
    fk_gridCell_geometryOfCellRepresentativeToTreatment geometry,
    fk_gridCell_geometryOfCellRepresentativeToBuffer geometry,
    fk_gridCell_fk_site_selectedSiteID text,
    fk_gridCell_fk_OpIndependentParticular_isTreatment boolean,
    fk_gridCell_fk_OpIndependentParticular_isBuffer boolean,
    fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge float8,
    fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge float8,
    foreign key (fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,
    fk_gridCell_geometryOfCellRepresentativeToTreatment,fk_gridCell_geometryOfCellRepresentativeToBuffer,fk_gridCell_fk_site_selectedSiteID,
    fk_gridCell_fk_OpIndependentParticular_isTreatment,fk_gridCell_fk_OpIndependentParticular_isBuffer,fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
    fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge) references GridCell(fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,
    fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,geometryOfCellRepresentativeToTreatment,geometryOfCellRepresentativeToBuffer,fk_site_selectedSiteID,fk_OpIndependentParticular_isTreatment,
    fk_OpIndependentParticular_isBuffer,fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge),
    
    fk_OpDependentParticular_AoCForCellsRepresentativeToTreatment float8,
    fk_OpDependentParticular_AoCForCellsRepresentativeToBuffer float8,
    fk_OpDependentParticular_AvgHPerWindowRepresentativeToTreatment float8,
    fk_OpDependentParticular_AvgHPerWindowRepresentativeToBuffer float8,
    foreign key (fk_OpDependentParticular_AoCForCellsRepresentativeToTreatment,fk_OpDependentParticular_AoCForCellsRepresentativeToBuffer,
    fk_OpDependentParticular_AvgHPerWindowRepresentativeToTreatment,fk_OpDependentParticular_AvgHPerWindowRepresentativeToBuffer) references OpDependentParticular(
    AoCForCellsRepresentativeToTreatment,AoCForCellsRepresentativeToBuffer,AvgHPerWindowRepresentativeToTreatment,AvgHPerWindowRepresentativeToBuffer),
    
    primary key (fk_gridCell_fourCornersOfKeyWindowRepresentativeToTreatmentAsGeoJSON,fk_gridCell_fourCornersOfKeyWindowRepresentativeToBufferAsGeoJSON,
    fk_gridCell_geometryOfCellRepresentativeToTreatment,fk_gridCell_geometryOfCellRepresentativeToBuffer,fk_gridCell_fk_site_selectedSiteID,fk_gridCell_fk_OpIndependentParticular_isTreatment,
    fk_gridCell_fk_OpIndependentParticular_isBuffer,fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfTreatmentToNearestEdge,
    fk_gridCell_fk_OpIndependentParticular_distanceFromCPOfBufferToNearestEdge,fk_OpDependentParticular_AoCForCellsRepresentativeToTreatment,fk_OpDependentParticular_AoCForCellsRepresentativeToBuffer,
    fk_OpDependentParticular_AvgHPerWindowRepresentativeToTreatment,fk_OpDependentParticular_AvgHPerWindowRepresentativeToBuffer)
);
postgis composite-primary-key postgresql foreign-keys
1 Answers

What you're looking for is a FOREIGN KEY ... ON UPDATE CASCADE. Consider this table t1 with the following record:

CREATE TABLE t1 (
  gid int,
  geom geometry(point,4326) DEFAULT 'POINT EMPTY',
  PRIMARY KEY (gid,geom)
);

INSERT INTO t1 VALUES (42,'POINT(1 1)');

Now on table t2 create a foreign key, but this time declare it as ON UPDATE CASCADE:

CREATE TABLE t2 (
  gid int PRIMARY KEY,
  t1_gid int,
  t1_geom geometry(point,4326),
  FOREIGN KEY (t1_gid,t1_geom) 
    REFERENCES t1 (gid,geom)
    ON UPDATE CASCADE
);

INSERT INTO t2 VALUES (0,42,'POINT(1 1)');

Now if you change the value of the composite primary key (or part of it) on t1 ...

UPDATE t1 SET gid = 1
WHERE gid = 42;

... it will directly reflect on t2:

SELECT gid,t1_gid,ST_AsText(t1_geom) FROM t2;

 gid | t1_gid | st_astext  
-----+--------+------------
   0 |      1 | POINT(1 1)

Demo: db<>fiddle

user_22753880
Related