1

I am developing a QGIS plugin, in which I want the user to be able to add/delete features from some layers. Here is the code I'm using to delete a feature with id = trayId

try:
   self.traysLayer.startEditing()
   self.traysLayer.deleteFeatures([trayId])
   self.traysLayer.commitChanges()
except Exception:
   self.iface.messageBar().pushMessage("Error", "The selected tray can't be deleted because it is in use.")

The problem is sometimes the feature to be deleted is referenced by another table using foreign key, so I'm getting the following PostGIS error:

ERROR: update or delete on table "tray" violates foreign key constraint

I have tried adding try/except to handle all exceptions, but it doesn't succeed to handle that error, I am still getting the same error.

How can I handle such error?

1

In the Postgres DB, you can define the constraint to cascade the deletions: when you delete from table tray, it also deletes from tableB.

This instruction should be set on the foreign table

CREATE TABLE tableB (
    id serial PRIMARY KEY,
    trayId integer REFERENCES tray (trayId) ON DELETE CASCADE
);

Alternatively, you can do this yourself. Before deleting from tray, check for existence of related data and delete it by hand

self.relatedTable.deleteFeatures([trayId])
| improve this answer | |
  • Thanks for your reply. But I want to prevent the user from deleting trays if it is in use by the other table. – devfaz Jan 19 at 5:37
  • Add to that, manually check for existence of related data will be costly (performance wise). – devfaz Jan 19 at 5:43
  • Either you want to enforce a foreign key or you want good performance. You can't have it both ways. – Vince Jan 19 at 12:43
  • @devfaz Please clarify: either you want to allow the deletion (using cascade delete is the solution) or you want to prevent it (having an error as it is stated in the OP is a solution, or else you have to check for data existence first).. so what is wrong and what are you looking for? – JGH Jan 19 at 14:23
  • @JGH What is wrong is that when the code fails to delete a record QGIS shows a long error message, then it doesn't role back the deletion command (it leave the trays layer in the Modified State), so no further modifications can be done on that layer until the user put the layer back to the Normal State. What I'm looking for is to 1st prevent the deletion, then show my own error message, then put the trays layer in the Normal State. – devfaz Jan 19 at 14:41

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.