Cascade delete

I has an issue a couple weeks ago where I needed to import older records into an existing database. I had already planned ahead and left "room" in the primary key serial numbers for the older records, my only challenge was to get the older set of records BEFORE the newer ones.

Importing the older records will put them after the new ones. I therefore needed to (1) export the newer records, (2) delete them, then (3) import the older records, finally (4) import the newer ones again. Easy.

Except for the cascading delete.

Relationships in FMP can be set up to automatically delete child records when the parents are deleted. I like this feature because it enhances data integrity. However this is one instance where it needs to be worked around.

(I know other developers who insist that cascade delete is a Really Bad Thing because it's deleting data, but the data that's being deleted will be a record with a key value that points to a deleted master record: IOW it's dead data anyway. If the deletion of the master record was unintended the it needs to be recovered from a backup, and the deleted related records and be restored at the same time too.)

The step 2 above is where the cascading delete will automatically delete all the child records. In the case of this database this was several table's worth of data. I could have gone through and unchecked the cascade delete option from all the relationships. done the import, then added it again... but that was too stupid.

The solution was to NOT delete the records, but instead over-write them. Doing this is as easy as selecting the option in the Import dialog to "update existing records" and to add remaining records as new records.

Naturally, I tried the process first on a backup copy of the database, and it worked as advertised. It was a simple matter to export, delete, import the old then import the new. Much faster than changing the cascade delete option and much less error prone.