Software Development Assistant

The final stage with lookup tables is to create a relationship between the main table and the lookup table. Unfortunately there is no facility to view an Entity-Relationship diagram in the Visual Studio Server Explorer. Either install and use SQL Server Management Studio 2012 or amend the Table definition in the code window.

To connect to you database using SQL Server Management studio, enter (LocalDb)/v11.0 as the server name, and use Windows authentication. This will work on your PC at home where you have Admin rights. If the database does not appear in the Databases folder, right click on the Databases folder and Attach your database file.

The view in the Object Explorer will look similar to Visual Studio's Server Explorer, exept that there is an extra folder for Database Diagrams. To add a diagram right click on this folder. The first time you do this you will be asked to add extra components needed to create diagrams. Say Yes, then add a new diagram, you will then be prompted to say which tables you wish to add to the diagram.

To create the relationship, drag the Primary Key from the lookup table (Grapes) to the Foreign Key in the main table (Wines). A relationship window then opens to confirm the name of the relationship and the fields involved. The name can be made more readable, in this case calling it FK_Wine_MainGrape is better than FK_Wines_Grapes

To ensure that the Foreign Key always contains a valid value (a Primary Key from the Grapes table), a key Contstraint must be added by openint the INSERT and DELETE rules and changing their settings from no action to cascade. The relationship has now been established (providing your data in the two tables is correct!)

If you examin the main table definition in Visual Studio 2012, you should see the sql contraint has been added to the code. If you do not have access to SQL Server Management Studio, then adding the constraint in code view in Visual Studio should have the same affect.