[Qgis-developer] PostGIS edition and foreign key

[Bernhard Ströbl]

Hi,

well, I tested and it is as you assumed: QGIS deletes both datasets and 
then issues an INSERT containing the new dataset. Therefore your foreign 
key constraint throws the error. I see two workarounds:
1) as proposed before: change the foreign key behaviour. If that is not 
possible
2) triggers might help you; possible outline:
a) preparation: add a field to operations and name it e.g. "was_parcel"
b) trigger 1, before delete:
  update operations set was_parcel = parcel_id, set parcel_id = NULL 
WHERE parcel_id = OLD.id (assuming parcel_id is the field with the 
foreign key constraint)
c) trigger 2, after insert:
  update operations set parcel_id = was_parcel, set parcel = NULL WHERE 
parcel = NEW.id

Bernhard

Am 04.03.2015 10:36, schrieb Thibaut Gheysen:
> Hi,
>
> I do the merge interactively by selecting the 2 parcels and clicking on
> the button "merge selected features" from the advanced digitizing
> toolbar. On the "merge features attributes" window, I select the
> attributes of the parcel A as attributes of the new/merged parcel. Like
> that the new/merged parcel as the same primary key than the past parcel A.
>
> Regarding the possibility to create triggers to clean the merge, I have
> to think about it but seems a little bit complex given the number of
> tables linked to parcel table (not only operation one) and also the fact
> that during an editing session we can make multiple merge on the same time.
>
> Regarding the type of foreign key, I have well "ON DELETE NO ACTION".
> Defining the foreign key to "ON DELETE CASCADE" or "ON DELETE SET NULL"
> is unfortunately not possible.
>
> Best regards,
> Thibaut.
>
>
>
> 2015-03-03 12:10 GMT+01:00 Bernhard Ströbl <bernhard.stroebl at jena.de
> <mailto:bernhard.stroebl at jena.de>>:
>
>     Hi,
>
>     the question is how you define your foreign key [1], namely what you
>     put for "ON DELETE" (you have either NO ACTION or RESTRICT, which
>     throw the error you receive) If you set ON DELETE CASCADE the
>     operation would be deleted, too, probably not what you want. Maybe
>     you should set ON DELETE SET NULL (make sure that the field can be
>     NULL!). Afterwards you reconnect the operation and the new parcel.
>
>     Bernhard
>
>
>     [1]
>     http://www.postgresql.org/__docs/current/static/ddl-__constraints.html#DDL-__CONSTRAINTS-FK
>     <http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK>
>
>     Am 03.03.2015 11:14, schrieb Thibaut Gheysen:
>
>         Hi,
>
>         I would like to have your opinion in order to simplify as much as
>         possible our current workflow.
>
>         We have a table parcel and a table operation. The table operation
>         include a foreign key to the table parcel. We have 2 parcels (A
>         and B)
>         but only parcel A has operations link on it. I would like to
>         merge the 2
>         parcels by keeping the attributes of the parcel A. The merge is
>         doing
>         properly but impossible to save the edit (Provider errors:
>         PostGIS error
>         while deleting feature: ERROR: update or delete table "parcel"
>         violates
>         foreign key constraint"fk_parcel" on table "operation"). I guess
>         this is
>         due to the fact the Qgis first delete the 2 parcels (A and B) before
>         creating a new one (merged one).
>         This behaviour oblige us to export the table parcel into a
>         shapefile and
>         make the modifications on it. Once done, we import the shapefile
>         into
>         PostGIS and update the parcel geometry base on it.
>
>         Is there any chance to find an easier workflow, without developing a
>         plug-in ?
>
>         Thank for your help.
>
>         Best regards,
>         Thibaut.
>
>
>
>
>
>
>
>         _________________________________________________
>         Qgis-developer mailing list
>         Qgis-developer at lists.osgeo.org
>         <mailto:Qgis-developer at lists.osgeo.org>
>         http://lists.osgeo.org/__mailman/listinfo/qgis-__developer
>         <http://lists.osgeo.org/mailman/listinfo/qgis-developer>
>
>
>         __________ Information from ESET Mail Security, version of virus
>         signature database 11260 (20150303) __________
>
>         The message was checked by ESET Mail Security.
>         http://www.eset.com
>
>
>
>
>     __________ Information from ESET Mail Security, version of virus
>     signature database 11260 (20150303) __________
>
>     The message was checked by ESET Mail Security.
>     http://www.eset.com
>
>
>
>     _________________________________________________
>     Qgis-developer mailing list
>     Qgis-developer at lists.osgeo.org <mailto:Qgis-developer at lists.osgeo.org>
>     http://lists.osgeo.org/__mailman/listinfo/qgis-__developer
>     <http://lists.osgeo.org/mailman/listinfo/qgis-developer>
>
>
>
>
> _______________________________________________
> Qgis-developer mailing list
> Qgis-developer at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-developer
>
>
> __________ Information from ESET Mail Security, version of virus signature database 11265 (20150304) __________
>
> The message was checked by ESET Mail Security.
> http://www.eset.com
>



__________ Information from ESET Mail Security, version of virus signature database 11266 (20150304) __________

The message was checked by ESET Mail Security.
http://www.eset.com