0

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
  • Without more information, one can't say easily. Different problems require different solutions. One problem may be best served with the circular reference and the other less constraints on the table.
    – user40980
    May 6, 2014 at 19:21
  • 1
    This may be common knowledge in the database world, but what's the problem with circular references at the constraint level?
    – user7043
    May 6, 2014 at 19:35
  • @delnan, I only ran into a problem when trying to rebuild my database. I'm building / testing / redesigning and when I had a circular reference in my creation script, it could not run because it tried to constrain to a table that did not yet exist. The work around is either to add the constraint later, or remove the circular reference.
    – TecBrat
    May 6, 2014 at 19:59
  • How could you ever create the first record in such a system? May 6, 2014 at 20:58
  • @LorenPechtel, null is allowed in the constrained column, so I can create a user without an address. But, while an address is constrained to a user, then that address cannot be deleted. (writing this comment might have actually sparked a better understanding for me, of how to decide when and where to use the constraints)
    – TecBrat
    May 7, 2014 at 15:13

2 Answers 2

4

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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.