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:

image_thumb4

*right click* -> Relationships...

image_thumb5

Add -> Tables and Column Specification:

image_thumb7

Select table/column:

image_thumb9

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

Option B: With an database diagram

image_thumb10

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

image_thumb13

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

image_thumb16

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

Result:
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