I am focusing on a database that tracks files and dependencies in projects. Briefly, I've two primary tables the PROJECTS table lists project names along with other qualities, the FILES table lists files. Every file entry ways to some project like a foreign key set to CASCADE, therefore if I remove a task record in the database, all of the file records disappear too. To date, so great.
Now I've one more DEPENDENCIES table. Each record within the dependency table is 2 files, indicating the first file is dependent around the second. Again they are foreign secrets, the very first is set to CASCADE (therefore if I remove personal files entry, this record is erased), but the second reason is set to limit (so I'm not permitted to remove personal files entry if every other files rely on it). Again, everything appears good.
Regrettably it appears I'm able to no more remove a task having a single SQL remove statement! The remove attempts to cascade-remove the files, but when these come in the DEPENDENCIES table, the RESTRICT foreign key prevents the remove (despite the fact that that record within the dependencies table is going to be removed since the other column is CASCADE). The only real workaround I've would be to calculate a precise to remove the files so no dependency record constraints are violated, and take away the file records individually before trying to get rid of the project.
Can there be in whatever way to setup my database schema so just one SQL remove in the projects table will properly cascade another removes? I am using Firebird 2.1, but I'm not sure in the event that makes any difference - it appears like there needs to be a means to make this happen?
You cannot control an order of deletion via a cascading down foreign key, however, you may have the ability to design a trigger on
PROJECTS to remove rows in
FILES owed for this project and are generally indexed by
DEPENDENCIES as determined by other
FILES. Turn it into a
BEFORE DELETE trigger, therefore it should execute prior to the cascading down effects.
Something similar to 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
Then when you remove a task, this removes all of the "child" files of the project which are determined by other files, which cascades to remove rows in
DEPENDENCIES so that all the rest of the files have the freedom of dependencies. Your deletion from the project are now able to cascade to remove these files.
I've not examined this and my Firebird syntax might be rusty, but possibly it'll enable you to get began.
Clearly, please test this on the copy of the data, not the live data!
Does the machine support deferred constraints, where the constraint check could be deferred until a commit point?
Maybe that's just an Oracle factor though.