Home

An Identity Column

   

Introduction

One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Category Item Name Size Unit Price
Women Long-sleeve jersey dress Large 39.95
Boys Iron-Free Pleated Khaki Pants S 39.95
Men Striped long-sleeve shirt Large 59.60
Women Long-sleeve jersey dress Large 45.95
Girls Shoulder handbag   45.00
Women Continental skirt Petite 39.95

Imagine that you want to change the value of an item named Long-sleeve jersey dress. Because you must find the item programmatically, you can start looking for an item with that name.

This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named Women. In the same way, there are too many records that have a Large value in the Size column, same problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve shirt.

To solve the problem of uniquely identifying a record, you can create a particular column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user does not have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column.

You cannot create an identity column on an existing table, only on a new table.

Visually Creating an Identity Column

To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column resembles that of the table but is singular. Also, by habit, the name of the column ends with _id, Id, or ID.

After specifying the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

Practical LearningPractical Learning: Creating an Identity Column

  1. In the top section of the table, click ReceiptNumber
  2. In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes
  3. Click Identity Seed and type 1000
     
    Visually Creating an Identity Column
  4. Save the table

Creating an Identity Column Using SQL

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

private void btnDatabase_Click(object sender, EventArgs e)
{
    using (SqlConnection connection =
	new SqlConnection("Data Source=(local);" +
			  "Database='Exercise1';" +
			  "Integrated Security=yes;"))
    {
	SqlCommand command =
	    new SqlCommand("CREATE TABLE StoreItems( " +
		 	   "StoreItemID int IDENTITY(1, 1) NOT NULL, " +
			   "Category varchar(50), " +
			   "[Item Name] varchar(100) NOT NULL, " +
			   "Size varchar(20), " +
			   "[Unit Price] money);",
			   connection);
	connection.Open();
	command.ExecuteNonQuery();

	MessageBox.Show("A new table named StoreItems has been crated.");
    }
}

Published on Thursday 03 January 2008


Previous Copyright © 2007 FunctionX, Inc. Next