Should I use foreign keys? [duplicate]

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 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.

Answer 5

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

Answer 6

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