Advantages and disadvantages of using Foreign Keys

Question

Generally, I know that using foreign keys can help to maintain data integrity. But I am not sure whether the use of foreign keys can cause performance problems, especially when there are large amout data imported.

Answer 1

As you said, Foreign Keys are used to maintain the data integrity ... There may be minor performance overhead because of Foreign Keys ..because before inserting data into the Child table, the process has to check whether the corresponding Parent Key is there in the parent table or not ....

So, in some of the applications, where the data entry is from the Screens-Dropdowns, then the foreign key from the parent table can be removed , as the data is from drop-downs, there is NO possibility for inconsistant data ....

If  any ORM tools like hibernate etc is used, then foreign keys are very much required to maintain the relation and generate the Child VOs(Value Objects) ...

So, depends on the sitution cautious decision has to be taken

---

-- Reddy Balaji C. ##Mark as answer if it solves the issue

Answer 2

As a general rule, FKs are not an issue. And if it would be, there are many ways to handle it, whether it be with INDEXes, or even temporarily disabling the FK CONSTRAINT.

The benefits of FKs and relational integrity are at the very core of relational databases, and should not be avoided (even when there are performance concerns!)

Answer 3

From the perspective of database design, you should be consistently using foreign keys.  Foreign keys help prevent some forms of database pollution.  The performance hit for maintaining foreign key relationships is small.  There will be situations in which large amounts of data is inserted in a single DDL command that warrant temporary disabling of foreign key constraints; however, that is not the same thing as leaving out the foreign keys all toghether for "normal" tables.

Now, if what you are talking about is an import table in which you are staging data that will ultimately end up in a different designated target table, I consider that a different matter.  In this case, do not include the foreign key because the purpose of this table is different than a "normal" table.

As a database administrator or database designor, avoid designing tables in which responsibility for maintaining foreign key relationships is relegated to the application.  What normally happens in this situation is that on paper the application group will claim responsibility for data entegrity -- until a problems occur.  At that time, you as the database administrator will suddenly become responsible for cleaning the mess up.  Also understand that while the development group may claim to be responsible, they won't really be accountable for the problem; the accountability will fall on you.  I cannot think of a single time in which I have witnessed the application group being accountable for one of these kinds of problems.

Answer 4

The benefits of FKs and relational integrity are at the very core of relational databases, and should not be avoided (even when there are performance concerns!)

Just adding emphasis to what Brian is saying; I completely agree.

Answer 5

Hi Catherine,

As Kent and Brian said, referential integrity is the very important part of the system. It helps you a lot to keep the data clean, minimize the bugs and helps optimizer in quite a few cases.

I'm not going to repeat what other said and going to play the role of Devil' advocate :) There are a few things you need to keep in mind when you think about referential integrity.

1. It introduces additional load to the system. Every time when you insert the data to the detail (referencing) table, it should check existence of the master data row. Every time when you delete master row, it should perform cascade actions on detail table. This is usually not an issue unless you have huge transactional tables with hundreds inserts per second and/or deletion of the referenced tables. But it always has own price.

2. You cannot implement partition switch (for example sliding window scenario) in the case if your partitioned table referenced by other tables. Obviously you can workaround it in one way or another but it would require non-trivial code in place

3. With referential integrity you need to be sure that data is inserted in specific and correct order (referenced row first, referencing rows after that). Frankly this is the way it should be although there are some exceptions from that rule. For example, in our system we have 2 transactional entities that collect data from the different sources and one transactional entity is referencing another. We cannot (always) guarantee that referenced row would be inserted prior referencing row. Again that is the exception but it happens in some cases.

I would suggest that unless you have those specific cases always create FK. And if you don't want to have FK in production, still create them on DEV/QA - it will help to locate some bugs on the early stages.

I blogged about it some time ago - you can see a couple other specific examples there: http://aboutsqlserver.com/2011/01/20/referential-integrity-part-1-foreign-keys/

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 6

Hi,

Does FK relationship helps performance in Queries when joining the 2 tables using the same fields in the FK?

Answer 7

Can you elaborate on your question and show sample of a query you have in mind? You need to make sure you have an index on the FK in the "child" table. Creating PK/FK relationship does not automatically add an index on the FK.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 8

Its simple. You should always include foreign keys.

Application will never make referential integrity. Yes you can use drop down etc. but you still can add already deleted values.

Performance? This is common myth that foreign keys slow database. Bad database design leads to better performance right? No.

All this because even Microsoft programmers think that database is just junkyard and referential integrity, data validation and order by should be done in application level which is just pure ignorance of common sense.