Circular dependencies on Foreign Key constraints (Oracle)
Sometimes it's valid to have tables that reference one another. For example:
- A photo table stores photos, each of these photos belongs in a folder
- A folder table, but each folder has mandatory (not null) "cover" photo
So both tables reference one another, but how do you create the tables, and how do you insert data?
- If you try and create either of the tables, the other would need to exist in order to create the table with the foreign key constraint. The solution is to firstly create both tables without the foreign key constraints and secondly "alter" the tables to add the foreign key constraints.
- If you try and insert a photo, the folder would need to exist; if you try and insert a folder then its cover photo would already need to exist. The solution here is to create the foreign keys with "initially deferred deferrable"; then the foreign key constraints are checked at commit-time rather than insert-time. You can insert the rows, in any order, and only when you do a commit will the system check that the constraints are not violated.
There's a perfect explanation here: http://infolab.stanford.edu/~ullman/fcdb/oracle/or-triggers.html