Read Build Your Own ASP.NET 3.5 Website Using C# & VB Online
Authors: Cristian Darie,Zak Ruvalcaba,Wyatt Barnett
Tags: #C♯ (Computer program language), #Active server pages, #Programming Languages, #C#, #Web Page Design, #Computers, #Web site development, #internet programming, #General, #C? (Computer program language), #Internet, #Visual BASIC, #Microsoft Visual BASIC, #Application Development, #Microsoft .NET Framework
and the DepartmentID column in the Employees table, are perfect candidates for
the application of a foreign key constraint. Take a look at the examples shown in
The DepartmentID column in the Employees table references the DepartmentID
primary key in the Departments table. Notice that the DepartmentID primary key
in the Departments table is unique, but the DepartmentID foreign key within the
Employees table may repeat.
As they stand, these tables already have an established relationship, and all the data
in the DepartmentID column of the Employees table correctly matches existing 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.
Licensed to [email protected]
Database Design and Development
293
Table 7.13. The Departments table’s primary key
DepartmentID (Primary Key)
Department
1
Accounting
2
Administration
3
Business Development
4
Customer Support
5
Executive
6
Engineering
7
Facilities
8
IT
9
Marketing
10
Operations
Table 7.14. The Employees table referencing records from the Departments
table
Emp’ID
Dep’tID
Name
U’name
P’word
City
State
M’Phone
(Primary
Key)
1
5
Zak
zak
zak
San
CA
555-555-5551
Ruvalcaba
Diego
2
9
Jessica
jessica
jessica
San
CA
555-555-5552
Ruvalcaba
Diego
3
6
Ted
ted
ted
San
CA
555-555-5555
Lindsey
Diego
4
6
Shane
shane
shane
San
CA
555-555-5554
Weebe
Diego
5
9
David
david
david
San
CA
555-555-5553
Levinson
Diego
6
1
Geoff Kim
geoff
geoff
San
CA
555-555-5556
Diego
Licensed to [email protected]
294
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
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 il-
lustrates, 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.
Figure 7.17. Adding support for database diagrams
Licensed to [email protected]
Database Design and Development
295
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.18. Adding tables to the diagram
Licensed to [email protected]
296
Build Your Own ASP.NET 3.5 Web Site Using C# & VB
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 diagram’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 diagramming
features, are identical in Visual Web Developer and SQL Server Management Studio.
Figure 7.20. The many features of the diagram editor
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 diagramming features provided for free are extremely powerful and useful.
Licensed to [email protected]