24

Possible Duplicate:
What’s wrong with foreign keys?

I use MS Sql Server with a large database about 4 GB data.

I search around the web why I should use foreign keys. by now I only indexed the keys used to join tables. Performance is all fine, dataintegrety is no problem.

Should I use foreign keys? Will I get even more performance with foreign keys?

Community
  • 1
  • 1

8 Answers8

16

Foreign key's don't actually improve performance, in fact they incur a small performance penalty on all write operations, to ensure that the constraint is followed.

The reason why you want to use these is to prevent destructive write operations. If you don't have them, buggy code, or a bad sql statement can remove rows that are expected to be there.

Dana the Sane
  • 14,762
  • 8
  • 58
  • 80
  • FKs can't protect you from code that is bad. They can only protect you from deleting parent side of a foreign key relationship if there are dependent children. – Mahdi Yusuf Jan 05 '22 at 22:06
15

Integrity may not be a problem today, but that's the exact attitude that makes it a problem tomorrow or two weeks from now.

Tom H
  • 46,766
  • 14
  • 87
  • 128
6

A foreign key is primarily a tool for enforcing database integrity, which is unrelated to speed of execution.

If you have already optimized your index design, then you probably have these indexes already installed, at least as non-unique indexes. So I wouldn't expect any performance change just from installing foreign keys (whicb don't even necessarily involve an index.)

I'd be a little suspicious of your complacency about the optimization of your design, though, if you don't already have this concept nailed.

Read the documentation for Foreign Keys with the goal of understanding what they do to enforce integrity (it's worth knowing about in any case.) Then see if that doesn't answer your question more completely.

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • 1
    "unrelated to speed of execution" is not entirely accurate for inserts (I know you know but Tuan might not) – SquareCog Mar 01 '09 at 03:03
  • Thx - I filled out the answer a bit, in a fashion that should end up the same place. – dkretz Mar 01 '09 at 03:06
  • good argement about insert. i have good view of structure. as i said integrety is no problem. i need raw speed. –  Mar 01 '09 at 03:12
4

In MySQL you can disable Foreign Key's with SET FOREIGN_KEY_CHECKS=0

ajtrichards
  • 29,723
  • 13
  • 94
  • 101
Mr Bumbles
  • 41
  • 1
4

What was not mentioned in the older question that SquareCog linked to earlier - yes, foreign key constraints can be a pain when doing data cleanup, batch updates, test data generation, or any type of operation where you bypass the normal sequence of things. But - you can always drop your foreign key constraints before you do something like this, and then recreate them again later (if you have your database objects scripted properly, this is hardly any extra work).

I used to be lazy, but have come around to depending on foreign key constraints. There are still situations where you can't have them - like in cross-database relationships.

cdonner
  • 37,019
  • 22
  • 105
  • 153
4

There is one feature/constraint which Foreign Keys bring to your system, which has not been mentioned so far. That is commit/transaction logic (that's what I call it anyway). With Foreign Keys enabled, all of the rows for an update, in all the tables affected need to be there for the commit to work (not throw a SQL error that the Foreign Key Constraints have been violated).

If you have a body of code, which works and "plays fast and loose", with commits/transactions. Then you could be in for some remediation, to get things working with FK's in the schema.

Also, Oracle at least, lets you disable constraints(not just drop/remove). So you can switch them on/off easily. Handy, when you want to do some bulk operations either with out the overhead of the constraints, or to do some "surgery" on the data which has intermediate states which would fail the constraints.

Aussie Craig
  • 772
  • 4
  • 5
  • Both of those are mentioned in the link I posted, but I'll vote for you anyway. The "commit/transaction logic" is officially referred to as ACID guarantees (the "C" being for consistent, as in, after a transaction is finished the data is consistent with declared constraints). – SquareCog Mar 02 '09 at 18:44
2

Foreign keys also help to keep your database clean, as you can have the database do a cascading drop.

Milhous
  • 14,473
  • 16
  • 63
  • 82
1

Foreign keys make data integrity better, performance, somewhat slower when deleting/inserting/updating.
In my last company we decided to keep integrity/connections in the BL, as it makes changes in the BL simpler (think hundreds of millions of records). If you have a small app, I see no reason why not do it in the data layer (db)

Itay Moav -Malimovka
  • 52,579
  • 61
  • 190
  • 278
  • What does BL stand for? – usefulBee Oct 17 '17 at 19:14
  • 1
    Business layer, Data layer handles the code that talks directly to the db. BL takes that data and implements the business rules using that data, and sends it to the UI. It is a somewhat old way to look at a multy tiered software. Today we have more than three tiers/layers and we have widely distributed system to add on this. – Itay Moav -Malimovka Oct 18 '17 at 01:34
  • By saying, "we decided to keep integrity/connections in the BL," are you referring to Code First approach or what else? – usefulBee Oct 18 '17 at 16:16
  • "we" refers to the Software Architect team . But, If i Understand your question, then yes, we maintained the integrity of the data via the code itself, not in the DB. BTW, I am not saying/claiming that was a correct approach. – Itay Moav -Malimovka Oct 19 '17 at 18:06
  • Thank you. By code I meant Code First approach introduced by Entityframework http://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx – usefulBee Oct 19 '17 at 19:03