Adding the IDENTITY Property

3 4

When you create a table, you can specify a column as an identity column by adding the IDENTITY property to the column definition. If a column is created with the IDENTITY property, SQL Server automatically generates a row value for that column, based on a seed value and an increment value. The seed is a value that will be the identity value for the first row inserted into the table. The increment is the amount by which SQL Server will increase the identity value for successive inserts. Each time a row is inserted, SQL Server assigns the current identity value to the row's identity column. The next row inserted will receive an identity value that is an increment greater than the current highest identity value. This way, each inserted row receives a unique identity value. The identity property is useful for columns where each row in the column should have a unique ID, such as the Product_ID column. It is easier to let SQL Server generate the identity value for rows inserted than to track what value should be inserted next. An identity column is commonly used as a primary key constraint in the table to uniquely identify a row. (See Chapter 16 to learn about primary key constraints.)

For example, if you specify IDENTITY(0, 10), the first row inserted will get an identity column value of 0, the second row will get 10, the third row will get 20, and so on. The default seed and increment values, if none are specified, are (1, 1). You must specify either both parameters or none. Identity columns cannot have default values and cannot allow null values. Only one identity column is allowed per table.

By default, identity columns cannot have data inserted directly into them and they cannot be updated. If you want to reinsert a row that was deleted and want to keep the original identity value for that row, you can override the default setting by using the following statement:

 SET IDENTITY_INSERT tablename ON 

This statement will enable you to insert a row and assign the value you want for its identity column. After you have finished, you should disallow identity column inserts by running the following command:

 SET IDENTITY_INSERT tablename OFF 

SQL Server will then take the highest identity value for the column at that time and use it as the seed it must increment for the next inserted row.

Adding the IDENTITY Property to the Product_Info Table

Let's add the IDENTITY property to the Product_Info table. Instead of entering data values for the column product_ID, we'll make it an identity column and let SQL Server automatically generate the identity values to ensure uniqueness. The T-SQL code for creating the table is shown here:

 USE MyDB GO DROP TABLE Product_Info GO CREATE TABLE Product_Info ( Product_ID smallint IDENTITY(1, 1) NOT NULL, Product_Name char(20) NOT NULL, Description char(30) NULL, Price smallmoney NOT NULL, Brand_ID brand_type ) GO 

The product_ID column will now receive values starting at 1, with an increment of 1 for each successive row that is inserted into the table. Adding the IDENTITY property ensures that each product will be assigned a unique identification number without a user being required to enter one. The choice of 1 for the increment is arbitrary. Whatever increment you use, the identity values will be unique.