Monday, February 06, 2006

Circular FK References

Circular foreign key references are evil. And they sneak in without being obvious, until you decide to do something big like reload a table.

In our case, a sale line points to stock, a stock item points to the order it came in on. So far, so good. Both FKs are nullable, as a sale line might not have been assigned to stock yet, and the stock item might be independent of an order still online.

But then the order line was referring to a sale that that line was ordered for. It was nullable, too, as not all orders are for written sales. Therefore, all three rows can be created w/o difficulty, then linked up, making it impossible to disconnect.

In our case, we solved it by reversing the direction of the third link. Now, the sale line refers to the order line that will satisfy that sale, if any. The links are then Sale->Stock->Order and Sale->Order, and no loop exists. Life is good once again.

But it snuck in there. Perhaps it would be amusing to write something to detect such loops, just in case...


Post a Comment

<< Home