I have set up a spatial database in PostgreSQL in which I have some data/tables that need to be linked by M to N relations. For those occasions I have two tables that contains geometry and a pivot table that contains foreign keys of both tables.

I tried to set up QGIS (3.10) in the way the manual insist for working with M to N relations. I have loaded the relations in project properties and I can see it in the attribute form when I identify the feature. I have already created features in one layer and now I would like to create features in other layer and link it. When I try to set up the link in the attribute form of the new feature I encounter an error:

ERROR: insert or update on table "park_usek" violates foreign key constraint "fk_park" DETAIL: Key (fk_park)=(18) is not present in table "park_pol".

I understand that it's because the foreign key in the pivot table can't be directed to the ID of the new feature as it hasn't yet been created. Is there any way I can set QGIS and/or PostgreSQL (via PgAdmin4) so it should fire this only after the creation of the feature or something like this? I understand that I can probably write a script which would handle this, is there is an easier solution?

  • 1
    Constraints can be declared as DEFERRABLE, but that works only if all inserts happen inside the same transaction. – CL. May 29 '20 at 8:18
  • Thank you @CL, it definitely sent me in the right direction. What I needed was to declare the constraints as DEFERRABLE INITIALLY DEFERRED. This way the error don't fire and the relation is declared correctly in pivot table. – PavelP Jun 2 '20 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.