Creating an autoincrement field (counter) in the MS SQL Server database table, which is located in the “*.mdf” file

Creating an autoincrement field (counter) in the MS SQL Server database table, which is located in the “*.mdf” file

This topic shows how to create a counter field (a unique field) in the table of the Microsoft SQL Server database that is located in the file “MyDataBase.mdf”.

This topic is based on the previous topics:

 


Content


The task

The files “MyDatabase.mdf” and “MyDataBase.ldf” of the database are designed to work in the Microsoft SQL Server relational database management system are given. Archive with files can be downloaded here.

The database contains one table named ‘Source’. The ‘Source’ table contains the following fields:

  • ID_Source – integer type (int);
  • Name – type ‘string’ of 50 characters;
  • Address – type of ‘string’ of 100 characters.

In the task you need to make the field ID_Source unique counter. When adding a new record to the table, the value of the field should be increased by 1 (autoincremental field), that is, be unique.

 

Intstructions

1. Connecting the “*.mdf” file of the database to MS Visual Studio

Run the MS Visual Studio.

Using the Server Explorer utility, you need to connect the previously created files “MyDataBase.mdf” and “MyDataBase.ldf”. Archive with files can be downloaded here.

It is recommended that the files be placed in the same directory.

An example of adding a ready (previously created) “*.mdf”-file of a local database to MS Visual Studio is described in detail in the article:

After connecting the database, the “MyDataBase.mdf” database will be displayed in the Server Explorer window (Figure 1).

The database contains one ‘Source’ table (Figure 2), which contains fields in accordance with the condition of the task.

Figure 1. Database ‘MyDataBase.mdf’ in the ‘Server Explorer’ window

 

Figure 2. The ‘Source’ table

 

2. Setting the ID_Source field as a counter. The “Identity Column” property

In accordance with the condition of the task, the ID_Source field must be unique. Modern databases support the uniqueness of fields. This means that when a new record is added to the database, a new unique value is automatically generated. Typically, when you add a new record for an integer type, the new unique value is incremented by 1 relative to the previous unique value (optional). It is impossible to manually or programmatically change the value of field records, which is a counter (a unique value). All this work is done by the database management system.

To set the ID_Source field unique, you need to do the following:

  • expand the ‘Tables’ tab in the ‘MyDataBase.mdf’ database (Figure 3);
  • In the tab Tables make a right click “mouse” and from the context menu call the command “Open Table Definition” (Figure 3). As a result, the window for defining table fields opens;
  • activate the ID_Source field and set the “Identity Column = ID_Source” property in the “Properties” property window (Figure 4);
  • save and close the ‘Source’ table.

 

Figure 3. The command “Open Table Definition”

 

Figure 4. Setting the Identity_Column property to ID_Source value

After the performed actions, the ID_Source field will automatically generate a unique integer value.

Now you can use this table in your projects.

 

3. Filling a table with data

After you set the ID_Source field in the table as a unique counter, you can programmatically or manually fill the table with data (records).

To fill a table with data (records), you need to perform the following actions:

  • call the context menu with the right mouse click on the Source table and select “Show Table Data” from the context menu (Figure 5). As a result, a window opens for entering data into the table (Figure 6);
  • enter data into the Source table. Because the ID_Source field is a counter, you can not enter data into this field. You can enter data only in the ‘Name’ and ‘Address’ fields. The value of the ‘Source’ field will be generated automatically.

 

Figure 5. The command “Show Table Data”

 

Figure 6. Entering data into the ‘Source’ table

 


Related topics