Conflicting CASCADE and RESTRICT foreign key requirements?

Question

I'm working on a database that tracks files and dependencies in projects. Briefly, I have two main tables; the PROJECTS table lists project names and other properties, the FILES table lists files. Every file entry points to a project as a foreign key set to CASCADE, so if I delete a project record from the database, all the file records disappear as well. So far, so good.

Now I have an additional DEPENDENCIES table. Each record in the dependency table is two files, specifying that the first file depends on the second. Again these are foreign keys, the first is set to CASCADE (so if I delete a file entry, this record is deleted), but the second is set to RESTRICT (so I am not allowed to delete a file entry if any other files depend on it). Again, everything seems good.

Unfortunately it seems I can no longer delete a project with a single SQL delete statement! The delete tries to cascade-delete the files, but if any of these appear in the DEPENDENCIES table, the RESTRICT foreign key prevents the delete (even though that record in the dependencies table will be removed because the other column is CASCADE). The only workaround I have is to calculate an exact order to delete the files so none of the dependency record constraints are violated, and remove the file records one at a time before attempting to remove the project.

Is there any way to set up my database schema so a single SQL delete from the projects table will correctly cascade the other deletes? I'm using Firebird 2.1, but I don't know if that makes any difference - it seems like there ought to be a way to make this work?

Answer 1

Does the system support deferred constraints, in which the constraint check can be deferred until a commit point?

Maybe that's just an Oracle thing though.

Answer 2

You can't control the order of deletion through a cascading foreign key, but you may be able to design a trigger on PROJECTS to delete rows in FILES that belong to this project and are also listed in DEPENDENCIES as dependent on other FILES. Make it a BEFORE DELETE trigger, so it should execute before the cascading effects.

Something like this:

CREATE TRIGGER Del_Child_Files FOR PROJECTS
BEFORE INSERT
AS BEGIN
  FOR SELECT F.FILE_ID FROM FILES F JOIN DEPENDENCIES D 
      ON F.FILE_ID = D.CHILD_ID
    WHERE F.PROJECT_ID = OLD.PROJECT_ID
    INTO :file_id
  DO
    DELETE FROM FILES WHERE FILE_ID = :file_id;
  DONE
END

So when you delete a project, this deletes all the "child" files of a project that are dependent on other files, and this cascades to delete rows in DEPENDENCIES so all the remaining files are free of dependencies. Your deletion of the project can now cascade to delete these files.

I haven't tested this and my Firebird syntax may be rusty, but perhaps it'll get you started.

Obviously, please test this on a copy of your data, not the live data!