Rule 8: Unique ID When Inserted

[Previous] [Next]

Each horse will be given a unique ID when the horse is added to the database. This ID value won't be part of the information about the horse; it will only be the horse's identifier.

Let's finish by taking a look at the last rule of this simple application. The rule addresses the unique ID given to a horse when it's added to the database.

At least for the time being, we'll implement rule 8 in the simplest way possible. We'll make the HorseId column an identity column. We won't stick to this solution to the bitter end, but we'll start there.

SQL Server treats an identity column in such a way that each new row will get the next available value. When you declare the column, you must tell SQL Server which number should be the very first number issued—the seed—and then how numbers should be incremented for each insert—the increment.

The easiest way to make a primary key column into an identity column is to use a diagram in Visual Database Tools. Just access the table's column properties and select the column's Identity check box. Then type in an Identity Seed value and the Identity Increment value. Figure 4-18 shows you the results.

Figure 4-18. The HorseId column is now an identity column. The next value to be used is 1713 (Identity Seed). The value will be incremented by 1 for each new horse inserted.