Closely related to: https://softwareengineering.stackexchange.com/q/45447/48651
I asked a question on SO and it led me to ask this here.
If I’m faced with a choice of having a circular reference or just not enforcing the restraint, which is the better choice?
In my particular case I have customers and addresses. I want an address to have a reference to a customer and I want each customer to have a default billing address id and a default shipping address id.
I might query for all addresses that have a certain customer ID or I might query for the address with the ID that matches the default shipping or billing address ids.
I’m not sure yet how the constraints (or lack of) will effect the system as my application and it’s data age.
6
First, if you find yourself building a system where table A references table B and table B references table A, that often implies that you’ve violated normalization rules in building your data model. Very, very occasionally, you may do this because you’ve measured and benchmarked and the denormalization solves a particular performance problem. The vast majority of the time, though, it means that you really want to reconsider the data model.
In this case, I’d strongly suggest reconsidering the data model. A customer has one or more addressed. An address does not have a customer. It doesn’t make data model sense for an address table to have a reference to a customer or a vendor table. It may make sense for a customer table to have a reference to an address table but it would generally make more sense to build a mapping table between the customer and address tables (and between vendor and address) as outlined in your StackOverflow question.
If you are building an OLTP system, I’d strongly suggest creating foreign keys to enforce any referential integrity constraints that your data has. If you don’t, I can guarantee that someone will eventually create data inconsistencies (orphaned children, references to non-existent children, etc.) Yes, creating foreign keys adds some overhead from a performance standpoint. But assuming you care about the data making sense, you’ll have to check those constraints somewhere and it’s unlikely that you can do it more efficiently than the database can.
0
Should all foreign table references use foreign key constraints?
Short answer “No” or “Not necessarily”
Long answer.
Consider how your data relates to each other and in what circumstances you want to prevent a change. (Like not letting an address get deleted if it is still assigned to another entity.) Let that guide you in deciding IF you should use the FK constraint.