I remember hearing Joel Spolski mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database.

Do people have some solid reasons as to why (to avoid a discussion in lines with Stack Overflow principals)?

Edit: "I've yet to have a reason to create a foreign key, so this might be my first reason to actually set up one."

+7  A: 

There are no good reasons not to use them... unless orphaned rows aren't a big deal to you I guess.

Matt Rogish
Why are orphaned rows a big deal?
Seun Osewa
+2  A: 

I use them, I love them and I don't have problems with them.

+13  A: 

Foreign keys are essential to any relational database model.

The model, yes. The implemention, not essential, just probably useful.
le dorfier

I always thought it was lazy not to use them. I was taught it should always be done. But then, I didnt listen to Joel's discussion. He may have had a good reason, I don't know.

It was more of an off-the-cuff remark than a discussion, though perhaps I should research precisely what he thinks on the subject independently! However I was curious about the community's opinion on this topic as well!
+2  A: 

They can make deleting records more cumbersome - you can't delete the "master" record where there are records in other tables where foreign keys would violate that constraint. You can use triggers to have cascading deletes.

If you chose your primary key unwisely, then changing that value becomes even more complex. For example, if I have the PK of my "customers" table as the person's name, and make that key a FK in the "orders" table", if the customer wants to change his name, then it is a royal pain... but that is just shoddy database design.

I believe the advantages in using fireign keys outweighs any supposed disadvantages.

Ken Ray
I tend to rarely delete things anyhow. Just mark have a "Visible/active" bit.
+1 for "I believe the advantages in using fireign keys outweighs any supposed disadvantages"
Ian Boyd
+1  A: 

The argument I have heard is that the front-end should have these business rules. Foreign keys "add unnecessary overhead" when you shouldn't be allowing any insertions that break your constraints in the first place. Do I agree with this? No, but that is what I have always heard.

EDIT: My guess is he was referring to foreign key constraints, not foreign keys as a concept.

Nope. He doesn't like actual keys!
That amazes me. There is a big difference between not liking foreign key constraints and not liking foreign keys. I'm not sure how you have a relational database without them.
Yes I was shocked when I heard him. He might have been unintentionally tongue-in-cheek however; perhaps he will post here and clarify at some stage :-)

I can see a few reasons to use foreign keys (Orphaned rows, as someone mentioned, are annoying) but I never use them either. With a relatively sane DB schema, I don't think they are 100% needed. Constraints are good, but enforcing them via software is a better method, I think.


Alex Fort
Enforcing foreign keys via software isn't easy because of multi concurrency issues. What if user A deletes the parent while user B is inserting children?
+2  A: 

I agree with the previous answers in that they are useful to mantain data consistency. However, there was an interesting post by Jeff Atwood some weeks ago that discussed the pros and cons of normalized and consistent data.

In a few words, a denormalized database can be faster when handling huge amounts of data; and you may not care about precise consistency depending on the application, but it forces you to be much more careful when dealing with data, as the DB won't be.

Santiago Palladino
Jeff makes some good points. However, Dan Chak in "Enterprise Rails" shows a way to design cache tables that are essentially a de-normalized copy of the data. Queries run fast, and if the table doesn't have to be refreshed, it works well. I find that if your data drives the behaviour (e.g. application state) of your application, you need data to be normalized as much as possible because otherwise inconsistent data leads to inconsistent application behaviour.
Jay Godse
+2  A: 

There's one good reason not to use them: If you don't understand their role or how to use them.

In the wrong situations, foreign key constraints can lead to waterfall replication of accidents. If somebody removes the wrong record, undoing it can become a mammoth task.

Also, conversely, when you need to remove something, if poorly designed, constraints can cause all sorts of locks that prevent you.

Kent Fredric

Verifying foreign key constraints takes some CPU time, so some folks omit foreign keys to get some extra performance.

How much CPU time is spent removing duplicate and inconsistent data?
Ed Guiness
Yeah, this is true. On a system I work on we have to insert 10 - 40 gigs of data at a time into a database and FK performance with and without is visible in total time it takes.
Paul Mendoza
+1  A: 

The Clarify database is an example of a commercial database that has no primary or foreign keys.

The funny thing is, the technical documentation goes to great lengths to explain how tables are related, what columns to use to join them etc.

In other words, they could have joined the tables with explicit declarations (DRI) but they chose not to.

Consequently, the Clarify database is full of inconsistencies and it underperforms.

But I suppose it made the developers job easier, not having to write code to deal with referential integrity such as checking for related rows before deleting, adding.

And that, I think, is the main benefit of not having foreign key constraints in a relational database. It makes it easier to develop, at least that is from a devil-may-care point of view.

Ed Guiness
The code to handle a failed referential integrity check is a lot smaller than the code to handle inconsistent data.
Jay Godse
@Jay agree! Don't think I'm advocating this approach.
Ed Guiness
+7  A: 

@imphasing - this is exactly the kind of mindset that causes maintenance nightmares.

Why oh why would you ignore declarative referential integrity, where the data can be guaranteed to be at least consistent, in favour of so called "software enforcement" which is a weak preventative measure at best.

Ed Guiness
+13  A: 

to quote Joe Celko,

"like a size 26 thong, just because you can does not mean you should!"

I'm sure there are plenty of applications where you can get away with it, but its' not the best idea. You can't always count on your application to properly manage your database, and frankly managing the database should not be of very much concern to your application.

If you are using a relational database then it seems you ought to have some relationships defined in it. Unfortunately this attitude (you don't need foreign keys) seems to be embraced by a lot of application developers who would rather not be bothered with silly things like data integrity (but need to because their companies don't have dedicated database developers). Usually in databases put together by these types you are lucky just to have primary keys ;)

++ for the quote!
Ed Guiness
Man this answer is really bad... </sarcasm>
I really don't get people who don't have FKs in their database. The last time I worked with someone who didn't have it, he said "no, we enforce that in the application." Except I did a survey of all the customer databases and found most of them did have orphans...
That usually seems to be the case. I think you could get away with enforcing ONLY in the database (as long as your users don't mind runtime exceptions) but to have both is really the only way to go.
+1  A: 

I have heard this argument too - from people who forgot to put an index on their foreign keys and then complained that certain operations were slow (because constraint checking could take advantage of any index). So to sum up: There is no good reason not to use foreign keys. All modern databases support cascaded deletes, so...

I believe that the real reason FKs constraints are not used by some (most, from my perspective) is sheer laziness under the pretense that they can defend their laziness with their performance savings argument.I firmly believe that the vast majority of the Stupidity Expense our company incurs is due to lack enforcement of FK constraints and the ripple effect that this has through a company.Lack of Unique Keys is the other thing that drives me nuts next to 2000+ line stored procedures with 12 levels of nested IFs and random indenting, but I'll stop now.
@George - Hmm...this looks like a job security feature if the application doesn't cause the company to go bankrupt.
Jay Godse
+7  A: 

I always use them, but then I make databases for financial systems. The database is the critical part of the application. If the data in a financial database isn't totally accurate then it really doesn't matter how much effort you put into your code/front-end design. You're just wasting your time.

There's also the fact that multiple systems generally need to interface directly with the database - from other systems that just read data out (Crystal Reports) to systems that insert data (not necessarily using an API I've designed; it may be written by a dull-witted manager who has just discovered VBScript and has the SA password for the SQL box). If the database isn't as idiot-proof as it can possibly be, well - bye bye database.

If your data is important, then yes, use foreign keys, create a suite of stored procedures to interact with the data, and make the toughest DB you can. If your data isn't important, why are you making a database to begin with?

Nice insight. I would argue that the data is this important for every application that actually gets used. The only thing that differs is the consequences of corrupt data. They are high for your kind of app...
Jay Godse
+66  A: 

Reasons to use Foreign Keys:

  • you won't get Orphaned Rows
  • you can get nice "on delete cascade" behavior, automatically cleaning up tables
  • knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality.
  • FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance
  • they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc
  • someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented

Reasons not to use Foreign Keys:

  • you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn
  • by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. (Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing). This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state (but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?).
  • sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it
  • you are just being lazy :-)

I think (I am not certain!) that most established databases provide a way to specify a foreign key that is not enforced, and is simply a bit of metadata. Since non-enforcement wipes out every reason not to use FKs, you should probably go that route if any of the reasons in the second section apply.

Very nice compare/contrast summary!
Mitchel Sellers
Good list! The DBMs won't check consistency for "R" part of CRUD, so I'd take that part out. Also, it's probably a wash because in your app you do the same thing the DBMS does: you'll check and make sure the parent ID is valid before CRD and that is actually slower than having the DBMs do it!
Matt Rogish
Good point, Matt, but as far as doing the same thing the DB is doing in your app -- you probably shouldn't if you have the DB doing it. Either you trust the DB and use it properly, or it quickly devolves in a key-value store. If you need key-value stores, there are better options :-).
What if someone deletes the parent while you're inserting children? Right now when I submit "add comment" -- if you have already deleted your answer, this comment is now an orphan. FKs would've prevented it. Also, I could just change the parentID to be anything I want. Someone needs to check. :)
Matt Rogish
Precisely -- it should be the DB's job, since it's the only one that can guarantee transactionality in the face of multiple concurrent clients.
Oh I thought you were arguing that NO ONE needed to check, I misread your comment!! You are right :)
Matt Rogish
Watch out for those "on delete cascade" situations - it can run slow on large databases
Well thought-out answer! I was wondering - what's a better Key-Value store for 500M records that can be accessed by concurrent users than a database?
David B
David B -- check out BerkeleyDB (still free, but now owned by Oracle)
+1 Excellent answer -- the second reason not to use FK constraints could be thought of "makes it harder to break consistency" which actually sounds like a *good* thing!
Bill Karwin
Another reason in favor of foreign keys is that in most RDBMS implementations, declaring a foreign key implicitly creates an index on column(s) that would be most likely to benefit from an index.
Bill Karwin

One time when an FK might cause you a problem is when you have historical data that references the key (in a lookup table) even though you no longer want the key available.
Obviously the solution is to design things better up front, but I am thinking of real world situations here where you don't always have control of the full solution.
For example: perhaps you have a look up table customer_type that lists different types of customers - lets say you need to remove a certain customer type, but (due to business restraints) aren't able to update the client software, and nobody invisaged this situation when developing the software, the fact that it is a foreign key in some other table may prevent you from removing the row even though you know the historical data that references it is irrelevant.
After being burnt with this a few times you probably lean away from db enforcement of relationships.
(I'm not saying this is good - just giving a reason why you may decide to avoid FKs and db contraints in general)

If I understand what you are trying to say, I think my answer to that would be to logically delete the record in the lookup table or archive the no longer relevant historical data and archive the lookup record too.
+3  A: 

"They can make deleting records more cumbersome - you can't delete the "master" record where there are records in other tables where foreign keys would violate that constraint."

It's important to remember that the SQL standard defines actions that are taken when a foreign key is deleted or updated. The ones I know of are:

  • ON DELETE RESTRICT - Prevents any rows in the other table that have keys in this column from being deleted. This is what Ken Ray described above.
  • ON DELETE CASCADE - If a row in the other table is deleted, delete any rows in this table that reference it.
  • ON DELETE SET DEFAULT - If a row in the other table is deleted, set any foreign keys referencing it to the column's default.
  • ON DELETE SET NULL - If a row in the other table is deleted, set any foreign keys referencing it in this table to null.
  • ON DELETE NO ACTION - This foreign key only marks that it is a foreign key; namely for use in OR mappers.

These same actions also apply to ON UPDATE.

The default seems to depend on which SQL server you're using.

R. Bemrose
+1  A: 

This is a duplicate of:

Eric Z Beard
yikes sorry. Didn't see that...
+1  A: 

To me, if you want to go by the ACID standards, it is critical to have foreign keys to ensure referential integrity.

+1  A: 

I have to second most of the comments here, Foreign Keys are necessary items to ensure that you have data with integrity. The different options for ON DELETE and ON UPDATE will allow you to get around some of the "down falls" that people mention here regarding their use.

I find that in 99% of all my projects I will have FK's to enforce the integrity of the data, however, there are those rare occasions where I have clients that MUST keep their old data, regardless of how bad it is....but then I spend a lot of time writing code that goes in to only get the valid data anyway, so it becomes pointless.

Mitchel Sellers

Just to clarify, the relations in a relational database are the tables themselves. Foreign keys are called references. So, the key component in a relational database are the tables, not the foreign keys.

See Wikipedia.

Christian Davén
+1  A: 

Additional Reason to use Foreign Keys: - Allows greater reuse of a database

Additional Reason to NOT use Foreign Keys: - You are trying to lock-in a customer into your tool by reducing reuse.

+1  A: 

How about maintainability and constancy across application life cycles? Most data has a longer lifespan than the applications that make use of it. Relationships and data integrity are much too important to leave to the hope that the next dev team gets it right in the app code. If you haven't worked on a db with dirty data that doesn't respect the natural relationships, you will. The importance of data integrity will then become very clear.


I'll echo what Dmitriy said, but adding on a point.

I worked on a batch billing system that needed to insert large sets of rows on 30+ tables. We weren't allowed to do a data pump (Oracle) so we had to do bulk inserts. Those tables had foreign keys on them, but we had already ensured that they were not breaking any relationships.

Before insert, we disable the foreign key constraints so that Oracle doesn't take forever doing the inserts. After the insert is successful, we re-enable the constraints.

PS: In a large database with many foreign keys and child row data for a single record, sometimes foreign keys can be bad, and you may want to disallow cascading deletes. For us in the billing system, it would take too long and be too taxing on the database if we did cascading deletes, so we just mark the record as bad with a field on the main driver (parent) table.

+1  A: 

I also think that foreign keys are a necessity in most databases. The only drawback (besides the performance hit that comes with having enforced consistence) is that having a foreign key allows people to write code that assumes there is a functional foreign key. That should never be allowed.

For example, I've seen people write code that inserts into the referenced table and then attempts inserts into the referencing table without verifying the first insert was successful. If the foreign key is removed at a later time, that results in an inconsistent database.

You also don't have the option of assuming a specific behavior on update or delete. You still need to write your code to do what you want regardless of whether there is a foreign key present. If you assume deletes are cascaded when they are not, your deletes will fail. If you assume updates to the referenced columns are propogated to the referencing rows when they are not, your updates will fail. For the purposes of writing code, you might as well not have those features.

If those features are turned on, then your code will emulate them anyway and you'll lose a little performance.

So, the summary.... Foreign keys are essential if you need a consistent database. Foreign keys should never be assumed to be present or functional in code that you write.

+5  A: 

This is an issue of upbringing. If somewhere in your educational or professional career you spent time feeding and caring for databases (or worked closely with talented folks who did), then the fundamental tenets of entities and relationships are well-ingrained in your thought process. Among those rudiments is how/when/why to specify keys in your database (primary, foreign and perhaps alternate). It's second nature.

If, however, you've not had such a thorough or positive experience in your past with RDBMS-related endeavors, then you've likely not been exposed to such information. Or perhaps your past includes immersion in an environment that was vociferously anti-database (e.g., "those DBAs are idiots - we few, we chosen few java/c# code slingers will save the day"), in which case you might be vehemently opposed to the arcane babblings of some dweeb telling you that FKs (and the constraints they can imply) really are important if you'd just listen.

Most everyone was taught when they were kids that brushing your teeth was important. Can you get by without it? Sure, but somewhere down the line you'll have less teeth available than you could have if you had brushed after every meal. If moms and dads were responsible enough to cover database design as well as oral hygiene, we wouldn't be having this conversation. :-)

Ed Lucas
+1  A: 

I echo the answer by Dmitriy - very well put.

For those who are worried about the performance overhead FK's often bring, there's a way (in Oracle) you can get the query optimiser advantage of the FK constraint without the cost overhead of constraint validation during insert, delete or update. That is to create the FK constraint with the attributes RELY DISABLE NOVALIDATE. This means the query optimiser ASSUMES that the constraint has been enforced when building queries, without the database actually enforcing the constraint. You have to be very careful here to take the responsibility when you populate a table with an FK constraint like this to make absolutely sure you don't have data in your FK column(s) that violate the constraint, as if you do so you could get unreliable results from queries that involve the table this FK constraint is on.

I usually use this strategy on some tables in my data mart schema, but not in my integrated staging schema. I make sure the tables I am copying data from already have the same constraint enforced, or the ETL routine enforces the constraint.

Mike McAllister

Like many things, it's a tradeoff. It's a question of where you want to do the work to verify the data integrity:

(1) use a foreign key (a single point to configure for a table, feature is already implemented, tested, proven to work)

(2) leave it to the users of the database (possible multiple users/apps updating the same table (s) meaning more potential points of failure and increased complexity in testing).

It's more efficient for the database to do (2), easier to maintain and less risk with (1).

Jen A
The efficiency argument is overrated. Rule #1 is data integrity. If performance suffers because of FK constraints, change it. Rarely is that the reason.
+1  A: 

Bigger question is: would you drive with a blindfold on? That’s how it is if you develop a system without referential constraints. Keep in mind, that business requirements change, application design changes, respective logical assumptions in the code changes, logic itself can be refactored, and so on. In general, constraints in databases are put in place under contemporary logical assumptions, seemingly correct for particular set of logical assertions and assumptions.

Through the lifecycle of an application, referential and data checks constraints police data collection via the application, especially when new requirements drive logical application changes.

To the subject of this listing - a foreign key does not by itself "improve performance", nor does it "degrade performance" significantly from a standpoint of real-time transaction processing system. However, there is an aggregated cost for constraint checking in HIGH volume "batch" system. So, here is the difference, real-time vs. batch transaction process; batch processing - where aggreated cost, incured by constraint checks, of a sequentially processed batch poses a performance hit.

In a well designed system, data consistency checks would be done "before" processing a batch through (nevertheless, there is a cost associated here also); therefore, foreign key constraint checks are not required during load time. In fact all constraints, including foreign key, should be temporarily disabled till the batch is processed.

QUERY PERFORMANCE - if tables are joined on foreign keys, be cognizant of the fact that foreign key columns are NOT INDEXED (though the respective primary key is indexed by definition). By indexing a foreign key, for that matter, by indexing any key, and joining tables on indexed helps with better performances, not by joining on non-indexed key with foreign key constraint on it.

Changing subjects, if a database is just supporting website display/rendering content/etc and recording clicks, then a database with full constraints on all tables is over kill for such purposes. Think about it. Most websites don’t even use a database for such. For similar requirements, where data is just being recorded and not referenced per say, use an in-memory database, which does not have constraints. This doesn’t mean that there is no data model, yes logical model, but no physical data model.

jasbir L
Well, I don't know why inserting 3 double newlines in place of blanks and changing two words counts as '67% is Jonathan Leffler', but I don't think I had done anything like that much work on it. The main text was contributed by @jay (user 183837).
Jonathan Leffler
I just assumed that paragrahps won't work here as is the case in most other sites. So, I put it all as one, using bolds for flow change.
jasbir L

One good principle of data structure design is to ensure that every attribute of a table or object be subject to a well-understood constraint. This is important because if you or your program can count on valid data in the database, you are less likely to have program defects caused by bad data. You also spend less time writing code to handle error conditions, and you are more likely to write error-handling code up front.

In many cases these constraints can be defined at compile-time, in which case you can write a filter to ensure that the attribute always falls within range, or the attempt to save the attribute fails.

However, in many cases these constraints can change at run-time. For example, you may have a "cars" table that has "colour" as an attribute which initially takes on the values, say, of "red", "green" and "blue". It is possible during the execution of the program to add valid colours to that initial list, and new "cars" added may take on any colour in the up-to-date list of colours. Furthermore, you usually want this updated list of colours to survive a program restart.

To answer your question, it turns out that if you have a requirement for data constraint that can change at run-time, and those changes must survive a program restart, foreign keys are the simplest and most concise solution to the problem. The development cost is the addition of one table (e.g. "colours", a foreign key constraint to the "cars" table, and an index), and the run-time cost is the extra table lookup for the up-to-date colours to validate the data, and this run-time cost is usually mitigated by indexing and caching.

If you don't use foreign keys for these requirements, you must write software to manage the list, look valid entries, save it to disk, structure the data efficiently if the list is large, ensure that any updates to the list don't corrupt the list file, provide serial access to the list in case there are multiple readers and/or writers, and so on. i.e. You need to implement a lot of RDBMS functionality.

Jay Godse