Menu
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".
443
The obvious problem with the lack of foreign keys is that a database can't enforce referential integrity and if it wasn't taken care of properly at the higher level then this might lead to inconsistent data (child rows without corresponding parent rows).
When a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key will be set to NULL . The column must allow NULL or this update will fail.
Note that foreign keys are not mandatory, and a table may have no foreign keys. Conversely, every column in a table may have a foreign key constraint.
No foreign key propertyWhile it is recommended to have a foreign key property defined in the dependent entity class, it is not required.
Negative impact on performance.
I can think of two effects of the presence of foreign keys.
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.
78
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.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
