idea Maintain foreign key value if parent record in origin table is deleted in Data Management Ideas

Maintain foreign key value if parent record in origin table is deleted

MollyMoore — Mon, 06 May 2024 14:47:33 GMT

If a parent record in an origin table is deleted, the foreign key of the related feature turns to null. This has been a huge headache for our organization and it would be hugely helpful to have an option to disable this database behavior. Here are a few issues we have had to deal with:

We have some reference geodatabases that get recurring updates where all features are replaced in the origin table on a monthly basis with updated data. We have a child table that tracks changes in origin data during each update period. This enables us to select a record in the origin table and view the related records to see all the changes that have occurred to that element over time. However, when we delete and append the data in origin table, all the foreign keys in the changes database become null. This is incredibly frustrating and we have had to workaround this for years.
We have a field collection database with several relationships to tables and other features. Sometimes, an origin feature may accidentally get deleted by our field staff. They can recreate the origin feature, but all the foreign keys in related records are null and it's extremely difficult and time consuming to try to match up records.

Please consider an option to disable the default behavior of nullifying foreign keys when the origin record is deleted!

Re: Maintain foreign key value if parent record in origin table is deleted

LanceKirby2 — Sun, 04 Aug 2024 19:39:20 GMT

I would really like to see this functionality.

Re: Maintain foreign key value if parent record in origin table is deleted

Kevin_MacLeod — Wed, 11 Sep 2024 16:13:52 GMT

Yes. At least an optional behavior. For example when you create a related table and populate the foreign key with the point layer's GUID. Can't delete the point until you delete the table, if you set foreign key to not allow nulls. And if you do allow; it will update it and null it out. I bet if they sat down with full stack GIS dev there would be so many things like this. Low effort high impact. To add the toggle is a few lines of C#, to point to a "Null foreign keys" boolean option. If False, simply don't fire the nulling line of code.