Duplicate of 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?
Please see this question: http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys
P.S. 4Gigs is cute, not large :-)
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.
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.
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)
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.
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.
Foreign keys also help to keep your database clean, as you can have the database do a cascading drop.
Integrity may not be a problem today, but that's the exact attitude that makes it a problem tomorrow or two weeks from now.