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

Expanding the Table View submenu gives you more options for displaying your

table. If you choose Standard, you’ll see a full-blown version of the table definition;

as Figure 7.21 shows, you can change the table structure directly in the diagram!

The diagraming features provided for free are extremely powerful and useful.

Figure 7.21. The standard table view

Implementing Relationships in the Dorknozzle

Database

Every table in the Dorknozzle database has a relationship with another table. To

create a foreign key using the diagram, click the gray square to the left-hand side

of the column for which you want to create the foreign key, and drag it over the

table to which you want it to relate.

Let’s give it a try. Start by dragging the DepartmentID column of the Employees

table over the DepartmentID column of the Departments table, as illustrated in

Figure 7.22.

Figure 7.22. Creating a link between Employees and Departments

284

Chapter 7: Database Design and Development

The designer will open a dialog that shows the details of the new foreign key,

like the one shown in Figure 7.23.

Figure 7.23. Adding a foreign key

Ensure that your data matches that shown in Figure 7.23, and click OK. A new

dialog like the one shown in Figure 7.24 will appear, allowing you to tweak

numerous options that relate to the new foreign key. Leave the default options

as they are for now (though we’ll discuss them shortly), and click OK to finish

up.

Figure 7.24. Editing the foreign key options

285

Implementing Relationships in the Dorknozzle Database

After creating the foreign key, make a quick test to ensure that the relationship

is indeed enforced. Try adding an employee, but set the person’s DepartmentID

to 123. You should see an error like the one pictured in Figure 7.25.

Figure 7.25. The foreign key disallowing the addition of invalid

data

If you tried to delete a department with which employees were associated, you’d

generate a similar error.

Table 7.15 shows the foreign keys that we need to establish in the Dorknozzle

database. In our project, the foreign key column has the same name as its corres-

ponding primary key column. Go ahead and create all the foreign keys outlined

in Table 7.15.

Table 7.15. The relationships in the Dorknozzle database

Foreign KeyPrimary Key

DepartmentID in the table EmployeesDepartmentID in the table Departments

EmployeeID in the table HelpDeskEmployeeID in the table Employees

CategoryID in the table HelpDeskCategoryID in the table HelpDeskCat-

egories

SubjectID in the table HelpDeskSubjectID in the table HelpDeskSub-

jects

StatusID in the table HelpDeskStatusID in the table HelpDeskStatus

When it’s complete, your relationship diagram should resemble Figure 7.26. After

you add the relationships, save your changes by selecting File > Save Dorknozzle.

When you’re asked to confirm the changes to the database tables you’re altering,

click Yes.

286

Chapter 7: Database Design and Development