0

I use foreign keys at work. But we pretty much manually manage our tables and we always make sure that we always have a parent entry in another table for a child entry that references it by its Id. We insert, update and delete the parent and child entities in the table in the same transaction.

So why should we still keep those foreign keys? They slow the database down when inserting new entities in the database and may be one of the reasons we get deadlocks from time to time.

Are they actually used by Sql Server for other things? Like gathering better statistics or is their only purpose to keep data integrity?

Alecu
  • 2,627
  • 3
  • 28
  • 51
  • 1
    check this out. It answers all your doubts: https://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design – Prabhat G Oct 31 '17 at 08:36
  • 1
    Possible duplicate of [Are foreign keys really necessary in a database design?](https://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design) – Prabhat G Oct 31 '17 at 08:36

2 Answers2

2

You shouldn't. Drop constraints with their foreign keys.

Carniv0re YT
  • 43
  • 7
1

Checks at the Database lever are the last integrity barrier protecting your data. For performance issues you might want to remove foreign keys but you might end up having to maintain a partially corrupted DB what ends up being a nightmare.

Can Foreign key improve performance

Foreign key constraint improve performance at the time of reading data but at the same time it slows down the performance at the time of inserting / modifying / deleting data.

In case of reading the query, the optimizer can use foreign key constraints to create more efficient query plans as foreign key constraints are pre declared rules. This usually involves skipping some part of the query plan because for example the optimizer can see that because of a foreign key constraint, it is unnecessary to execute that particular part of the plan.

Allan
  • 12,117
  • 3
  • 27
  • 51