Inferring Foreign Key Constraints in Entity Framework Models

Foreign key constraints are one of the most important components of relational database models. At the database level they are key to maintaining data integrity and prevents applications and users from writing invalid data or from orphaning data by inadvertently deleting data referenced from other tables.

When reverse-engineering entity relationships in modelling tools and OR mappers, foreign key constraints serve yet another important role; they also describe the relationship between tables/entities. Without foreign key constraints, OR mappers can’t generate navigation properties between entities, and will not know in what order* it should process inserts, updates, and deletes for various tables.

* = e.g. generating order detail records before the main order record has been created.

Databases with no FK constraints

Unfortunately, there are lots of real-world databases that - although they are created and deployed in RDBMSes – lack foreign key constraints. The reasons for omitting FK constraints vary, but it is not uncommon that the reasons stated are based on misconceptions, misunderstandings, or lack of information. Sometimes they are omitted by programmers that can’t get their head around proper order of inserts/deletes, sometimes they are omitted “for performance reasons”, sometimes noone can remember anymore why they were omitted. Whatever the reason, relational databases with some or all FK constraints missing are out there and they’re fairly common.

Adding FK constraints to legacy databases

One way to tackle FK-less databases is simply to add the missing FK constraints. Simple solution to data integrity issues, and makes OR mappes happy, right? Unfortunately it is not that simple. The real problem with FK-less databases is the applications behind them. If there are tens-of-thousands, hundreds-of-thousands, or millions of lines of code that interact with those databases you can be certain that some of it will break if you just add all the missing FK constraints. There’s almost certainly code that do inserts/deletes in the wrong order, and there’s a fairly good chance that there is junk data or orphaned data that will break the FK constraint already during creation, and so on.

Inferring FK constraints

Since FK constraints are key to generating the associations in OR mappers such as Entity Framework, I decided to add a feature to infer FKs. Instead of creating them in the database, I use table and column names and types to deduce what might be a FK candidate in the database. After that, the inferred FKs are displayed in the Model Comparer’s list of missing FK constraints.

The user can choose and pick both real and inferred foreign key constraints and add them to the model selectively, and generate model-level associations and navigation properties without actually creating the FKs in the database.

The tool also allows inferred FK constraints to be materialized into SQL-DDL scripts, but that part is entirely optional.

Demonstration

The following set of screenshots demonstrate how the FK inference feature in the Model Comparer for EFv4 can be used to infer FKs in a database and to add selected keys to the EF4 model.

Step 1 – open a EFv4 model in Visual Studio 2010, and bring up the Model Comparer:

Step 2 – Click on Settings to open the settings dialog. Go to the Infer Foreign Keys tab.

Select the type of objects you want to infer FKs for; tables and/or views. Although views can not have FK constraints in the database it is perfectly fine to infer FKs and use them to add associations to views. This makes it possible to have navigation properties to/from views.

There are four different name matching options controlled by two checkboxes: whether PK members contain the name of the entity or not, whether entity names and member names are separated with underscores or not. Changing the checkboxes updates the table example in the middle of the dialog between the four different naming conventions, to make it easy to verify that the settings match the desired naming conventions.

 

Step 3 – with the Infer Foreign Key Constraints for Tables setting and/or the Infer Foreign Key Constraints for Views setting enabled, the model comparer will infer FK constraints where they don’t exist in the database. Inferred FK constraints start with the FKI prefix and has a different icon than existing FK constraints to make it easy to differentiate between inferred and real FK constraints.

 

Step 4 (optional) – The Report view also show all inferred keys. If generating a lot of keys the report view is a more eye-friendly way to go through them in order to determine which ones are valid/desired in the model.

 

Step 5 – Select the FK constraints that you want to use for generating associations in the model. Click Update SSDL to add them to the storage model (SSDL) in the Entity Framework 4 model.

 

Step 6 (optional) – The SSDL-to-DB tree will now show the newly added association. This allows inferred FKs to be materialized as SQL-DDL-scripts if desired. If not, select them and click Ignore. If you want to generate a SQL-DDL script, allowing the inferred FKs to be added to the database, select the FKs and click on Update Database.

 

Step 7 (optional) – If you generated SQL-DDL in Step 6, the SQL-DDL script will appear in a separate SQL editor in the Visual Studio instance that the Model Comparer belongs to. This allows you to review, edit, and save the script before applying to the database.

 

Step 8 – Select the new association(s) in the SSDL-to-CSDL tree and click Update CSDL to add them to the conceptual layer of the model. This will result in navigation properties added between the involved entities.

Step 9 – Close the model comparer and return to Visual Studio. Continue coding. Enjoy.

 

The Huagati Model Comparer

The Model Comparer is a feature in the Huagati DBML/EDMX Tools add-in for Visual Studio 2010. The add-in can be downloaded from http://huagati.com/dbmltools/ , and time-limited free trial licenses are available from the same site.

Screencasts showcasing some of the basic functionality in the Model Comparer for EFv4 is available at http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html and http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html