Back-To-School Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Default values
How to auto-generate values in a database
11. IDENTITY
Summary

Instruction

Good job! There are a couple of ways to automatically generate numerical values. The first one we're going to talk about is IDENTITY, which is mainly used for generating primary key values. Here is how we'd use IDENTITY in a CREATE TABLE statement to set up a primary key column named Id:

CREATE TABLE TableName (
  Id INT PRIMARY KEY IDENTITY (1, 1) 
  ...
)

IDENTITY takes two arguments: seed (the starting value) and increment (how much is added to the previous value to make a new value). You must specify both arguments or neither.

For a non-primary column, the relevant column definition will look like this:

Amount INT IDENTITY

In the Amount column, we did not specify the seed and increment values—we used the default values. In each of these columns, the database will generate a value that starts from 1 and increases by 1 (i.e., 1, 2, 3, 4, ...). However, if you use IDENTITY with different arguments—e.g., IDENTITY(20, 2)—the values will be different (20, 22, 24, 26, ...).

Okay. In the Author table, suppose that we've added IDENTITY(9, 1) to the Id column. Now let's insert the first record:

INSERT INTO Author (FirstName, LastName, Photo)
VALUES (N'Evo', N'Black', N'imgs/black2.jpg');

And this is the result of that INSERT operation:

Id FirstName LastName Photo CreateDateTime IsActive
9 Evo Black imgs/black2.jpg 2018-10-10 12:02:22 0

Notice that you have to omit the Id column with IDENTITY; its value will be generated based on IDENTITY.

Exercise

Add a new author to the database. You know only the name, Sally Adams, and that her account is active.

Stuck? Here's a hint!

After the table name, list only the FirstName, LastName, and IsActive columns.