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

is a particular kind of constraint. When the primary key constraint is set on a

column, the database will refuse to store duplicate values in that column.

Constraints in general, and primary keys in particular, represent a means by

which the database can maintain the integrity and consistency of data.

Primary keys composed of a single column, such as Employee ID, are frequently

used in conjunction with the IDENTITY property. The primary key constraint

guarantees that duplicate values cannot be inserted into the table. The IDENTITY

property helps us by always generating a new value that hasn’t already been used

in the primary key.

Primary Keys and the IDENTITY Property

Using the IDENTITY property for a column doesn’t mean we can avoid

specifying a primary key. It’s true that the IDENTITY property always generates

unique values, but it doesn’t necessarily enforce them.

For example, say we have a table with a number of columns, one of which

has the IDENTITY property set. This table contains three records that are

likely to contain the automatically generated values 1, 2, and 3 in the

IDENTITY column. Provided the INDENTITY_INSERT property for this table

is enabled (by default it’s disabled, but it’s quite easy to enable), it’s quite

simple to insert another record with the value 2. The IDENTITY column will

continue to generate unique values (4, 5, 6, and so on), but it doesn’t guar-

antee the column remains unique.

Creating the Employees Table

In this section, I’ll show you how to use both Visual Web Developer and SQL

Server Management Studio, but this time we’ll create a new data table. If you’re

using Visual Web Developer, expand the database node in Database Explorer,

right-click Tables, and select Add New Table, as shown in Figure 7.8.

Figure 7.8. Adding a new table in Visual Web Developer

267

Creating the Employees Table

If you prefer SQL Server Management Studio, you need to follow a similar pro-

cedure. Expand the Dorknozzle database node, right-click Tables, and select New

Table…, as illustrated in Figure 7.9.

Figure 7.9. Adding a new table with SQL Server Management

Studio

The window that appears as the result of the above procedures is shown in Fig-

ure 7.10—it looks the same in both Visual Web Developer and SQL Server

Management Studio. The main editing window lets you specify the column’s

three main properties: Column Name, Data Type, and Allow Nulls. To set additional

properties, you need to use the Column Properties pane.

To add the IDENTITY property to a column, locate the Identity Specification row

in the Column Properties pane and expand it. This will reveal the (Is Identity) drop-

down list, which should be set to Yes for an IDENTITY column, as Figure 7.10

indicates.

To set a column as the primary key, we can select Table Designer > Set Primary

Key, or click the little golden key icon in the Table Designer toolbar while the

column is selected. When a column is set as a primary key, a little golden key

appears next to it, as Figure 7.11 illustrates.

268

Chapter 7: Database Design and Development

Figure 7.10. Specifying column properties

Figure 7.11. The Employees table

269

Creating the Employees Table

Now, let’s create a table called Employees by adding the columns described in

Table 7.1.

Table 7.1. The structure of the Employees table

Primary

Key

Allow NullsIdentitySQL Data TypeColumn Name

YesNoYesintEmployeeID

NoNoNointDepartmentID

NoNoNonvarchar(50)Name

NoNoNonvarchar(50)Username

NoYesNonvarchar(50)Password

NoYesNonvarchar(50)Address

NoYesNonvarchar(50)City

NoYesNonvarchar(50)State

NoYesNonvarchar(50)Zip

NoYesNonvarchar(50)HomePhone

NoYesNonvarchar(50)Extension

NoYesNonvarchar(50)MobilePhone

After entering this information, press Ctrl-S to save the table. When you’re asked

to name the table, type Employees and click OK. When you’re done, your table

will resemble Figure 7.11.

After you create the table, you’ll see it appear under the Tables node in the Object

Explorer (or Database Explorer in Visual Web Developer). SQL Server Management

Studio prepends dbo. to the table’s name; dbo is the default “database owner”

user. Don’t worry about this for now—we’ll explore the topic of database users

in some detail later.

If you close the table designer window, you can open it later by right-clicking the

Employees table and selecting Open Table Definition in Visual Web Developer,

or Modify in SQL Server Management Studio. You’ll be taken back to the screen

that shows the structure of the table (shown in Figure 7.11).

270

Chapter 7: Database Design and Development