TSQL2SDAY Diagramming Relationships in SQL Server

For this month’s T-SQL Tuesday, we had a guest host Rob Farley  and we were asked to write a blog post about relationships:

As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!

Rules

As before, any blogger can–and should–feel free to participate in this event. In order to make the event slightly more international, I’m changing the time range from PST-based to UTC-based. So the rules are:

  • Your post must go live between 00:00:00 UTC and 23:59:00 UTC on Tuesday, February 9, 2010
  • Your post must link back to this one, and it’s recommended that you clearly identify the post as a T-SQL Tuesday post
  • You are responsible for ensuring that a trackback or comment appears here so that I can find the posts

My Submission

For my post in this month’s TSQL2SDAY submission I chose to discuss SQL Server Diagrams.  This month’s focus was on Relationships so I figured the Diagramming in SQL Server would be a great topic to discuss.  A lot of people are aware of the capability to add relationships through foreign keys etc but I always found it much more tedious then it can be if we just used the Digraming features in SQL Server.  This makes things much more easier to apply relationships and to see how all the tables are relating as a whole.  It is also a free Entity Relationship modeling tool.  Let’s discuss the basics. 

1) First if you never used the diagrams before it will quickly ask you to install some needed files.  These will create some system tables that are used to hold the diagram information.  They used to be called Davinci Tables but I am not sure what they are called any more. 

2) Next we just right click on Diagrams and Select Create New Diagram.

3)From here we add the tables we are interested in setting up relationships for and then hit ok.

For my example we have two simple tables one part table and one order table.  Review them below:

Next we click on the ID in the Part Table and hold down on the mouse and drag the ID field to the Order table.  This will bring up the following screen shot where we select the Primary Key table and the foreign Key table and the field we are using for the relationship like below:

Next we specify The cascading Update or Deletes if we want that to occur and select whether we want the relationship to be checked on creation.

 Finally we can see the completed relationship and we can save the diagram.

.

This is a much easier way of adding relationships to our databases using a GUI tool that makes it a snap.  Try it out and see how much easier it is.

Trackbacks

  1. [...] John Dunleavy demonstrated (complete with screenshots – something I should put more of in my posts) how foreign keys can be made so easily using the Diagram part of Management Studio. It’s not something I do much of, but I have to admit that reading posts like John’s can often inspire me to changing my ways. [...]

  2. [...] John Dunleavy demonstrated (complete with screenshots – something I should put more of in my posts) how foreign keys can be made so easily using the Diagram part of Management Studio. It’s not something I do much of, but I have to admit that reading posts like John’s can often inspire me to changing my ways. [...]

%d bloggers like this: