I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added.
Obviously dams have to cross a river to work so I would like to add a constraint to enforce this. Following the examples here https://spatialdbadvisor.com/postgis_tips_tricks/127/how-to-apply-spatial-constraints-to-postgis-tables I tried adding the following constraint to check that any new dam polyline crosses a river polyline:
ALTER TABLE weirs2 ADD CONSTRAINT river_intersect CHECK (ST_Intersects(afrivs.geom,weirs2.geom)=true);
However this gives me the following error message.
ERROR: missing FROM-clause entry for table "afrivs"
I also tried framing it as a SELECT query to include a FROM clause then get
syntax error at or near "SELECT".
Based on this https://dba.stackexchange.com/questions/149466/missing-from-clause-entry-with-constraint-on-value-from-type-in-postgresql, I tried putting the table names in () but then get:
ERROR: column "afrivs" does not exist
What am I missing?
Using POSTGIS 2.4.4 r16526 on PostgreSQL 10.6.