Database Diagrams in SSMS

SSMS draws a database's tables and the foreign keys between them. Make a diagram of Northwind, read its keys and relationship lines, and know when to draw one.

Journeyman · Database · SQL · Updated

After this lesson you can

  • Create a database diagram in SSMS and read its tables, keys and relationships

Key takeaways

  • In SSMS, right-click Database Diagrams under a database and choose New Database Diagram to see its tables and foreign keys.
  • A key symbol beside a column marks the primary key, and each line between two tables is a foreign key relationship.
  • The key end of a line is the one side and the ∞ end is the many side, so Customers to Orders reads one customer, many orders.
  • Saving a diagram you've edited changes the database, so build from scripts and use a diagram to sketch and check your design.

A script lists the tables one after another. A diagram shows them side by side, with lines for the foreign keys between them, so you can see how a database fits together at a glance.

Making a diagram

  1. In SSMS's Object Explorer, open Databases, then Northwind.
  2. Right-click Database Diagrams and choose New Database Diagram.
  3. The first time, SSMS asks to create the objects it needs to store diagrams. Click Yes.
  4. In Add Table, select the tables you want (hold Ctrl to pick several, or select them all), click Add, then Close.
  5. SSMS lays out the tables. Drag them around until the lines are easy to follow.
  6. Save it (Ctrl+S) with a name, such as Northwind Overview. It appears under Database Diagrams.

If Database Diagrams gives an error about the database's owner, you can still read the structure in Object Explorer: each table's Keys folder lists its primary and foreign keys.

Reading a diagram

You see It means
A box A table, with its columns listed
A key symbol beside a column That column is (part of) the primary key
A line between two boxes A foreign key relationship
A key at one end of the line The one side: the table whose primary key is referred to
An infinity symbol (∞) at the other end The many side: the table that holds the foreign key

So the line from Customers (key end) to Orders (∞ end) reads: one customer, many orders. OrderDetails has two lines, one to Orders and one to Products: it's the associative table that makes orders and products many-to-many.

Hover over a line to see its foreign key's name, such as FK_Orders_Customers.

Designing with a diagram

You can also design in a diagram: add a table, add columns, and drag from a primary key column onto another table's column to make a foreign key. Saving the diagram changes the database.

That's handy for sketching, but this course builds databases from scripts, which can be read, kept in Git and run again. A good habit: design with a diagram or on paper, then write the script, then make a diagram from the finished database to check it matches your design.

When to draw one

  • Before you build: sketch the tables and lines on paper or in a drawing tool, to check every subject has a table and every link has a key.
  • When you meet a new database: a diagram of its tables is the fastest way to learn which joins you'll need.
  • When you explain a design: a picture of the tables and lines says more than the script.

Check yourself

1 question to try on the lesson page.

So what

A diagram turns a list of tables into a map. Before writing a join across five tables, a look at the diagram shows you the path.

Key terms

Cheat sheets