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:
- An example of creating / connecting a local database in MS SQL Server, which is located in a separate “*.mdf”-file”;
- An example of creating a table in the local database of the MS SQL Server system, which is located in a separate “* .mdf” file.
Contents
Search other websites:
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
- 001 – An example of creating/connecting local Microsoft SQL Server database located in the “*.mdf” file
- 002 – An example of creating a table in the local Microsoft SQL Server database located in the “*.mdf” file
- 004 – Creating a “one-to-many” relationship between tables in Microsoft SQL Server database
- 005 – An example of creating the “*.mdf” file of local database Microsoft SQL Server in MS Visual Studio
- 006 – The “Connection String” string of connection with database. An example of using in applications