Are circular references acceptable in database?

circular-dependency, database, oracle

Solution

Records which point to other records are useful in a database. Sometimes these records form a cycle. This might still be useful. The only real annoyance in practice is avoiding violating the constraints.

For example, if you have a user and transaction table, the user might have a pointer to his last transaction. You need to insert the transaction first, then update the `last_transaction_id` to the correct value. While both these records exist you can't erase them, because the `user.last_transaction_id` points to `transaction.id` and `transaction.user_id` points to `user.id`. This implies that a user with no transactions has a null `last_transaction_id`. It also means that you have to null that field before you can delete the transaction.

Managing these foreign key constraints is a pain but it certainly is possible. There may be problems that arise if you add constraints to the database later which introduce new circular dependencies. You have to be careful in this situation. However, as long as one of the records in the cycle has a nullable foreign-key field, the cycle can be broken and the records can be deleted. Updates are not usually a problem as long as you insert the records in the right order.

Problem

Original source