Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to hear some "real life experience" suggestions if foreign key restrictions are good or bad thing to enforce in DB.
I would kindly ask students/beginners to refrain from jumping and answering quickly and without thinking.
At the beginning of my career I thought that stupidest thing you can do is disregard the referential integrity. Today, after few projects I'm thinking differently. Quite differently.

What do you think? Should we enforce foreign key restrictions or not?

Please explain your answer.

share

migration rejected from stackoverflow.com Jan 8 at 2:27

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as primarily opinion-based by MetaFight, Scant Roger, GlenH7, Snowman, MichaelT Jan 8 at 2:27

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

5  
Are you really asking whether foreign keys are bad? – alex Feb 8 '11 at 13:35
1  
@Alex, thanks for feedback. Yes I am. In your profile it says you are 25, meet me in 7 years for a bear and a friendly chat, I'll ask you if you still think the same way as you do now :). Cheers – This is it Feb 8 '11 at 13:37
9  
How many projects can you have done and yet ask both this and stackoverflow.com/questions/4932625/… ? Good grief! – Tony Andrews Feb 8 '11 at 13:38
3  
@This is it - I hope I don't have to work on any of your old projects... – hunter Feb 8 '11 at 13:42
2  
@This is it: I'm not 25, and I'll ask you the same question: Are you really asking whether foreign keys are bad? Your attitude is condescending, and you don't seem to have a clue. Down vote. – quentin-starin Feb 8 '11 at 15:48
up vote 43 down vote accepted

I would add as an initial disclaimer that when you say DB and this question has tags indicating several platforms, that we are talking about a traditional relation database - i.e. a well-defined system which manages data in tables, columns and rows, according to Codd et al. This is a well-defined paradigm with well-understood boundaries.

If you are talking about a bunch of spreadsheets or files in a folder (many people do call this a database), or any similar non-relational database (perhaps under the umbrella term NoSQL), none of this has to apply.

I would always begin a project that uses an RDBMS as an RDBMS by using foreign key constraints. You can always relax constraints later. I would also normalize first, and denormalize as necessary.

It's far easier to relax a constraint later or refactor tables if you are starting from something where there are guarantees.

Similarly, I'm going to try as best as I can to identify the proper data types, ranges and nullability of columns at the beginning. I'm going to default columns to NOT NULL until there is evidence that the model requires NULL. I'm going to have a primary key on all tables, and where there isn't an obvious choice (either natural or surrogate, depending on the design philosophy), I'm going to add a surrogate, so that it will be at least possible to uniquely identify a row among otherwise identical rows if I have to do a DELETE operation.

These things can all be relaxed later, but adding constraints later is going to require you to figure out what was wrong in the data before you can apply a constraint.

These are just a few of the basic rules of thumb which keep your database on track as you start development. Refactoring from one structured design to another structured design (of whatever quality or philosophy) is a lot easier than refactoring from unstructured.

In my experience, an evolving database design will outlive many front-end applications over its lifetime (sometimes several simultaneously), so it makes sense for it to provide a boundary of services which are always consistent and uniform. For this reason, it is unwise to expose tables directly or to treat the database as simple persistence for any things which gets thrown at it. If you expect the database to just hold anything with any rules which may vary over time, the value of the database itself is lowered, and it will have issues with data quality, uncertainty about semantics and all associated issues surrounding that.

share
    
Wow, this is why I love this site. Smart people giving smart answers to "questionable" questions!!! Thank you! – This is it Feb 8 '11 at 15:45
4  
And I will store dates in date or datetime column type and not varchar! I will use decimal (10,2) in mysql and avoid float! – shantanuo Feb 11 '11 at 6:51

As always, "it depends". Typically they're good for RI and for performance (since most DBs keep an index on the FK that is used for joins). In a data warehouse, they're often not enforced because the data is sliced and diced so many different ways (e.g., historical sales data may refer to products that are no longer sold). In high-performance applications, the overhead of enforcing RI at run time may be unacceptable. In general, though, it's better to have and enforce them.

share
1  
Typically in a data warehouse, products which are no longer sold will still be stored in an expired dimension, and enforced RI is still realistically possible for databases of multi-TB size with appropriate dimensional designs. – Cade Roux Feb 8 '11 at 15:31

My opinion is:

If you are working with not-relational DB, temporary tables, staging areas or bulk import tables I suggest not to use foreign keys. Checking constraints will slow down your processes and it's not necessary if you plan not to preserve data.

BUT ... If you are working with a standard relational DB ... well ... working without foreign key is FOOLISH!!!

I had been involved in lots of project where I was expected to migrate an existing database to a new architecture. Everytime I dealt with DBs with no foreign key, I found unreliable data, corrupted relationship, orphans ...

Expecting to control everything through classes and code, is UTOPIC. Maybe it can work in the first software releases or with small projects. On big projects or whenever another programmer will need to rework your code, he will have to be a genius to preserve all the required FK in each page via code!

Moreover having a detailed model with all FK active is important because:

  • The system will warn you if a bug in your code is going to corrupt data forever
  • Each programmer can count on a reliable ER model
share

Generally No. The only time I'd use them is if disparate code bases are hitting the database, and you want to ensure consistency across applications. Otherwise I prefer to keep the storage logic in the app's classes and control everything through code.

share

@CadeRoux and @Michael have great guidelines; here's some feedback after about eight years of development using Oracle, SQL Server, MySQL and PostgreSQL: Use foreign keys!

Pros:

  • Faster development since you won't have to include special clauses in SQL statements to exclude garbage data. Need to summarize line items? Sorry, you'll have to join with the orders table because some line items reference deleted orders. You really don't want to work on optimizing 100+-line queries.
  • Faster queries for the same reason as above.
  • More useful generated DB schemas with tools like SchemaSpy.
  • Do you really want to be the one to maintain a manually edited document containing all the semantics which your schema could contain?

Cons:

  • More work to create the DB schema, also known as "planning ahead."
  • Less flexibility, aka. "stable interface."
share

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