Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

isting departments in the Department table. However, as with primary keys, just

having the correct fields in place doesn’t mean that our data is guaranteed to be

correct.

For example, try setting the DepartmentID field for one of the employees to 123.

SQL Server won’t mind making the change for you, so if you tried this in practice,

you’d end up storing invalid data. However, after we set the foreign keys correctly,

SQL Server will be able to ensure the integrity of our data—specifically, it will

forbid us to assign employees to nonexistent departments, or to delete departments

with which employees are associated.

The easiest way to create foreign keys using Visual Web Developer or SQL

Server Management Studio is through database diagrams, so let’s learn about

them.

Using Database Diagrams

To keep the data consistent, the Dorknozzle database really should contain quite

a few foreign keys. The good news is that you have access to a great feature called

database diagrams, which makes it a cinch to create foreign keys. You can define

the table relationships visually using the database diagrams tool in Visual Web

Developer or SQL Server Management Studio, and have the foreign keys generated

for you.

Database diagrams weren’t created specifically for the purpose of adding foreign

keys. The primary use of diagrams is to offer a visual representation of the tables

in your database and the relationships that exist between them, to help you to

design the structure of your database. However, the diagrams editor included in

Visual Web Developer and SQL Server Management Studio is very powerful, so

you can use the diagrams to create new tables, modify the structure of existing

tables, or add foreign keys.

Let’s start by creating a diagram for the Dorknozzle database. To create a database

diagram in Visual Web Developer, right-click the Database Diagrams node, and

select Add New Diagram, as shown in Figure 7.15.

The process is similar in SQL Server Management Studio, which, as Figure 7.16

illustrates, has a similar menu.

The first time you try to create a diagram, you’ll be asked to confirm the creation

of the database structures that support diagrams. Select Yes from the dialog,

which should look like the one shown in Figure 7.17.

280

Chapter 7: Database Design and Development

Figure 7.15. Creating a database diagram with Visual Web

Developer

Figure 7.16. Creating a database diagram with SQL Server

Management Studio

Figure 7.17. Adding support for database diagrams

281

Using Database Diagrams

Figure 7.18. Adding tables to the diagram

Next, a dialog like the one in Figure 7.18 will ask you which of your database

tables you want included in the diagram. If you’re working with a database that

comprises many tables, you may want to have diagrams built to represent specific

pieces of functionality, but we want to create a diagram that includes all the

tables in our database.

Click Add until all the tables are added to the diagram. As you click Add, the

tables will be removed from the list and will appear in the diagram. Once you’ve

added all the tables, click Close. You’ll see a window in which all the tables are

clearly displayed—something like Figure 7.19.

You’ll probably need to tweak their positioning and dimensions so they fit nicely

into the window. The zooming feature may prove useful here! Select File > Save

Diagram1 (or similar) to save your new diagram. Enter Dorknozzle for the dia-

gram’s name.

Now, if you right-click any table in the diagram, you’ll gain access to a plethora

of possibilities, as Figure 7.20 reveals. This menu, along with the other diagram-

ming features, are identical in Visual Web Developer and SQL Server Management

Studio.

282

Chapter 7: Database Design and Development

Figure 7.19. Visualizing data tables using a diagram

Figure 7.20. The many features of the diagram editor

283

Using Database Diagrams