QGIS Widgets – Using Unique ID's with SQL Server Tables

By David Crowther

• Question:

Which is the best way to manage Unique IDs within QGIS when working with SQL Server Database Tables? 

Answer:

In a recent White Paper I started to explore the use of Widgets within QGIS to manage the maintenance of your attribute data within QGIS:

https://www.cadlinecommunity.co.uk/hc/en-us/articles/360000225998-Working-with-Widgets-in-QGIS-Part-1 

In addition, I created another FAQ detailing how to use Widgets in QGIS to create Unique ID values when working with data in a PostGIS Database:

https://www.cadlinecommunity.co.uk/hc/en-us/articles/360000030557-QGIS-Widgets-Auto-Add-Unique-ID 

However, this FAQ provides details of how to use Widgets in QGIS to create Unique ID values when working with data in a SQL Server Database. 

Step 1: Widget Type

Firstly, in the Layer Properties, choose the Attributes Form and select the field which will contain the unique ID value, in this case the ogr_fid. 

Then from the list of available Widgets choose the Widget Type to be ‘Hidden’. 

If required you can also give the Unique ID field an Alias e.g. Unique_ID, so that when viewed in the Attribute Table the user will better know its purpose. 

Within the Layer Properties also untick the Editable Box. This means that the Unique ID value will now not be presented to the user when they create a new feature in the map, and the value will also not be editable when viewed in the full Attribute Table. 

Step 2: SQL Table Design

Secondly, you will need to make a Design change to the Table within the SQL Database. Here you will set the Unique ID field, in this case the ogr_fid field, to have Identity Specification > Is Identity = YES 

Step 3: Draw a New Feature

Having made the changes to the layer in your QGIS Project and the Design changes to the Table in the SQL Database, you can now digitise a new feature. 

When you have completed the new feature notice that the Edit/Insert Attributes Form will now not list the Unique ID field, as it was made Hidden.

Instead, as soon as the User Saves the Layer, the SQL Database Table will be updated with a new record using the attributes as entered by the User, but the Unique ID value is now handled by the Database where the next available unique value is inserted automatically…. In this case ogr_fid 45.

Each time the Users create new records the Unique ID value is inserted by the Database using the next sequential value.