If 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:
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.