Microsoft Visual C# 2005 Express Edition: Build a Program Now! (33 page)

the matching primary key table. The yellow key indicates from which table the primary key is coming.
the Color table, a process within

SQL Server 2005 would prevent

this by validating that there are

I rearranged the diagram so that the two tables are close together. You can rearrange your tables any way
no “kids” left behind in the Listing

you want by dragging them by the title bar (i.e., where the table name is displayed). This is sometimes nec
table before allowing the deletion
essary to do when you create relationships so that you do not end up with an unusual-looking diagram. I
to occur in the Color table.

suggest that you put your Listing table in the middle of your other tables because it will be easier to create relationships this way. You can also rearrange the tables on your diagram at any time by right-clicking anywhere except on a table on the diagram’s designer surface and selecting
Arrange Tables
. You can also have the labels for every relationship appear on the diagram by right-clicking the diagram’s designer surface and selecting
Show Relationship Labels
, as shown in Figure 8-12.
Chapter 8: Managing the Data

143

C08622299.indd 143

C08622299.indd 143

10/24/05 4:02:37 PM

10/24/05 4:02:37 PM

Figure 8-12

Completed CarTracker database dia-

gram

11 Now create the other FK relationships by using either Figure 8-2 or the following table.
Column

Primary Key Table

Foreign Key Table

MakeID

Make

Listing

Table 8-4

CarTypeID

CarType

Listing

List of foreign key relationships to create

When finished, the content of your diagram should resemble the content shown in Figure 8-12. Make sure
You can always go back and review

your relationships are arranged properly by looking at where the infinity symbols and yellow keys are locat
the properties of any relationship
ed and also by looking at the previous table for verification.

by double-clicking on the line or

by right-clicking and selecting

Properties
from the context-sensi-

tive menu.

12 Click the
Save All
button or press
Ctrl+Shift+S
to commit the changes to the database. Click
Yes
when asked if you want to save.

144

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 144

C08622299.indd 144

10/24/05 4:02:38 PM

10/24/05 4:02:38 PM

Entering Data in SQL Server Tables Using Visual Studio

Now that you have created all of your tables and relationships, you’ll start inserting data in your tables and verifying that your constraints ensure the data integrity of your database.
While typing your data,

Let’s start by adding data in all tables. You’ll first add rows into the Color table.
look to the table’s left-most

area in the table data grid and

you’ll see a small black triangle

TO ENTER DATA IN SQL SERVER TABLES USING VISUAL STUDIO

, a pencil icon

To start entering rows in the Color table, right-click the
Color
table in the Database Explorer and 1

both a star and a small black

select
Show Table Data
. Your designer surface should have a grid like the one shown in Figure 8-13.
triangle

or a star

.
The pencil indicates

that you’re making a modifica-

tion to the row. The star indi-

cates a new row and the small

black triangle indicates the cur-

rent row.

Figure 8-13

Empty Color table in the table data grid

Let’s add the first color. Click in the
Color Name
field, type
Dark Blue
, and then press the
Tab
key to 2 go to the next column. Type
true
in the
Metallic
field. Because that column type is a bit, its values can only be either true or false because a bit type is a binary type. When you’re done, press the
Tab
key to go to the next row.

Chapter 8: Managing the Data

145

C08622299.indd 145

C08622299.indd 145

10/24/05 4:02:39 PM

10/24/05 4:02:39 PM

Add three more car colors—
Red
,
Silver
, and
Black
—and set Red as
Metallic
and the other 3 two colors as
Non-metallic
(i.e., false). When done, the table should look like the one shown in Figure 8-14.

4 Add the following data to the Make and the CarType tables.

Figure 8-14

Color table with four new rows of data

Make Table

MakeName

Country

GoodRoadster

Germany

For all columns that you

SmallCar

France

created as an identity, don’t

BigSUV

USA

type the data because the field

will automatically be generated

ReliableCar

Japan

by SQL Server 2005 Express

Edition whenever the row is

created in the table. If you try

CarType Table

to type data in an Identity col-

CarTypeName

NumberOfDoors

umn, you will not be allowed

to do so. When in an Identity

Roadster

2

column, it states that the cell is

SUV

5

read-only near the navigation

bar at the bottom of the Table

Hatchback

5

Designer.

Sedan

4

Coupe

2

You might not have realized that by giving a type to your data, you actually added data integrity verification to your database. Try modifying one of the Color rows by changing the Metallic column to read
Helloworld
instead of true or false. You’ll get an error message telling you that the Metallic field is of type Boolean. To show how data integrity is preserved using the foreign key constraints, you’ll add two Listing rows. You will enter more rows when using your Windows Form application.

5 Right-click on the
Listing
table, select
Show Table Data
, and add the following two rows.
146

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 146

C08622299.indd 146

10/24/05 4:02:40 PM

10/24/05 4:02:40 PM