Build Your Own ASP.NET 3.5 Website Using C# & VB (51 page)

NULL and Default Values

I’ve often heard people say that when we set a default value for a column, it doesn’t

matter whether or not we set it to accept NULLs. Many people seem to believe that

columns with default values won’t store NULL.

That’s incorrect. You can modify a record after it was created, and change any

field that will allow it to NULL. Your columns’ ability to store NULL is important

for the integrity of your data, and it should reflect the purpose of that data. A default value does make things easier when we create new rows, but it’s not as vital as is correctly allowing (or disallowing) NULL in columns.

Primary Keys

Primary keys are the last fundamental concept that you need to understand before

you can create your first data table. In the world of relational databases, each row

in a table
must
be identified uniquely by a column called a
key
, on which all database operations are based. The tables in your databases could contain hundreds or even thousands of rows of

similar data—you could have several hundred employees in your Employees table

alone. Imagine that your program needs to update or delete the record for John

Smith, and there are several people with that name in your organization. You

couldn’t rely on the database to find the record for the particular John Smith that

you were trying to work with—it might end up updating or deleting the wrong record.

We can avoid these kinds of problems only by using a system that uniquely identifies

each row in the table. The first step toward achieving this goal is to add to the table

an ID column that provides a unique for each employee, as did the Employee ID

column that we saw in
Figure 7.1
.

Remember that when we discussed this Employees table, we noted that you may

be tempted to use each employee’s username to uniquely identify each employee.

After all, that’s what the network administrator uses them for, so why shouldn’t

you? It’s true that this column uniquely identifies each row in the table, and we

call such a column a
candidate key
. However, it wouldn’t be a good idea to use this

column in our database operations for a number of reasons. Firstly, network usernames have been known to change, and such a change would wreak havoc on any database of more than a couple of tables. As we’ll see later, keys are fundamental

Licensed to [email protected]

Database Design and Development

279

to establishing relationships between tables, and these relationships rely on the fact

that keys will never change. Secondly, non-numeric keys require much more processing power than simple numeric ones. Using a nvarchar field to uniquely identify rows in your table will bring your SQL Server to a grinding halt much,

much quicker than if you chose a simple, numeric key.

The column that we choose to uniquely identify a row in a table in practice is called

the
primary key
. In the case of our Employee table, the Employee ID will always

be unique, so it would be a suitable primary key.

Multi-column Keys

To make the concept of keys easier to understand, we kept the definition simple,

although, technically, it’s not 100% correct. A key isn’t necessarily formed by a

single column—it can be formed by two or more columns. If the key is made up

of multiple columns, the set of values in those columns must be unique for any

given record. We’ll see an example of such a key in a moment.

Although we usually refer to
primary keys
as if they were columns, technically

they’re
constraints
that we apply to the existing columns of a table. Constraints

impose restrictions on the data we can enter into our tables, and the primary key

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.

Licensed to [email protected]

280

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

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 IDENTITY_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 guarantee the column remains unique.

Creating the Employees Table

In this section, we’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, rightclick
Tables
, and select
Add New Table
, as shown in
Figure 7.8
. Figure 7.8. Adding a new table in Visual Web Developer

If you prefer SQL Server Management Studio, you need to follow a similar procedure.

Expand the
Dorknozzle
database node, right-click
Tables
, and select
New Table…
, as illustrated in
Figure 7.9
.

Licensed to [email protected]

Database Design and Development

281

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.

Licensed to [email protected]