13

I'm working on a database-first ASP.NET MVC application. Looking at the database the foreign keys are very inconsistent, basically they are only there if it was specified in the SQL scripts that created the tables. In most cases they are not there.

However, looking in the edmx model, I can see that it is aware of the foreign keys i.e. it has correctly identified the navigation properties.

My question is, does the missing foreign keys in the actual database have an effect on the sql genereted by Entity Framework? By effect I mean negative impact on performance.

I can't really figure out if it matters or not.

Just to clarify, in the database I'm expanding tables and looking for the red key which indicates a foreign key. I'm also looking in the subfolder: "Keys".

Force444
  • 3,321
  • 9
  • 39
  • 77

2 Answers2

5

Negative impact on performance.

I can think of two effects of the presence of foreign keys.

  1. A tiny negative impact on inserts and updates, because the keys have to be checked. However, compared to everything else taking place in one complete database roundtrip this effect is totally negligible. Absolutely no reason to refrain from using them. It will never ever outweigh the benefits of data integrity.
  2. A tremendous performance gain when foreign keys are set up with cascaded deletes and updates.

In short, there is no reason for deliberately omitting foreign keys.

Of course, legacy can't always be undone overnight. If there is any room for changes in the database schema, I'd go for it. If not, you may consider manually adding common associations in the edmx model. These associations will not be erased by updating the model from the database.

Community
  • 1
  • 1
Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
1

Yes, you need those foreign keys.
I do not know Entity Framework, but I know some SQL Server.
You can get much better execution plans if you have Foreign Keys.
Kendra Little has a good video about Foreign keys here.

You also need to make sure that they are active and trusted. Brent Ozar writes about that here.

Henrik Staun Poulsen
  • 13,154
  • 4
  • 23
  • 26