Circular parent/child relationship in database

I have a parent with many children, which is fine. Against each parent I am trying to store a default_child. I want to make the default_child mandatory however I can not think how to do this.

Creating the parent first would mean violating the mandatory default_child condition. Creating the child first would mean vi开发者_如何学Pythonolating it's FK condition.

How do others deal with this? Thanks!

Constraints can typically be marked "deferrable", which means you can ask the database to defer checking them until commit time. That lets you resolve situations like this one where a constraint needs to be violated temporarily while building up the transaction, but will be satisfied again by the time you're ready to commit.

Parents must exist before they have children. True in life; true in databases.

If you want to ensure that every item you insert into the table gets a child by default, then do this using a trigger which executes immediately after the parent record is inserted.

Be careful, though: If your parent and child records are in the same table, then every time you insert a child, the trigger will fire again, creating a child of that child; which will trigger the insertion of a child of the child of the child...

Not sure if every DBMS does this, but I'd expect that I can break invariants inside of a transaction and that it just needed to be right at the commit point.

It's a fundamental limitation of SQL that it cannot support this. The nearest you can get is to disable one or more of the constraints temporarily - for instance by using a deferrable constraint.

A possible compromise is to remove the referencing column to its own table so it references both of your current tables. Then you only insert a row to this new table after the others have been populated. The constraint would still be optional of course - it's just that you wouldn't need a nullable column to implement it.