20 February 2009 HowTo, Relationships, SQL, Tips Robert Muehsig

image_thumb3If you create a relationship between to SQL tables, you get many benefits. The most important benefit (for me) is the integrity of your data. Besides the database-world there is another huge benefit: The relationships are used by many O/R mappers to create a structured object model. You could create such realtionships via different dialogs in the SQL Management Studio / Visual Studio or just do a "Drag´n´Drop" from one table to another one.

Relationships & O/R Mappers 
Linq2Sql & ADO.NET Entity Framework are both O/R mappers built in the .NET Framework (but you could use NHibernate or others as well)  and create entities based on a database. If you have relationships in your database, you will find these relationships later in you object model. To create such realationships in you SQL Database you have 2 (or 3) options:

Option A: With dialogs

Select table -> column -> modify:


*right click* -> Relationships...


Add -> Tables and Column Specification:


Select table/column:


Tada: Relationship created - but you need some clicks to do it. Now option B:

Option B: With an database diagram


2 tables and the red line marks the relationship we wants to add:


... just click on the primary key and drag it on the foreign key::


Now you´ll see a dialog to specify the relationship.

Option B saves more time and is much more "visual" - option C would be: Write the SQL queries manual.

Written by Robert Muehsig

Software Developer - from Saxony, Germany - working on primedocs.io. Microsoft MVP & Web Geek.
Other Projects: KnowYourStack.com | ExpensiveMeeting | EinKofferVollerReisen.de