MapThat – Cannot Create New Record – Unique Index

By David Crowther

Question:

In MapThat, when I create a new record with the same Plot ref / Feature ID, it fails to save the record – what is the cause of this?

Answer:

In this previous News article and video, we introduce the Adopting option within the MapThat Drawing Tools.

https://www.cadlinecommunity.co.uk/hc/en-us/articles/360018198278-MapThat-Drawing-Tools-Adopt

Adopting enables you to control the options for users when they create a new record with the same Plot Reference or Feature ID as an existing record:

• should it update the geometry of an existing feature based on the Plot Ref / Feature ID typed.
• or ask the user to change the Plot Ref / Feature ID to a new value.
• or create a new record with the same Plot Ref / Feature ID as the user typed.

However, if the user chooses to Create a new record using the same Plot ref / Feature ID and there are Indexes on that field, MapThat will not allow you to save that feature.

The error message in the ERROR LOG table will say –

Cannot insert duplicate key row in object 'dbo.lcc_planning_apps' with unique index 'lcc_planning_apps_plotref_UNIX'. The duplicate key value is (1111112).

The statement has been terminated.

The reason for this error is that the Database Table may have an INDEX on the Plot Ref/ Feature ID field. This will force unique value constraints on the field, so you cannot create a new record with the same value.

If you do wish your users to create new records with the same Plot Ref / Feature ID, then you can drop this unique constraint.

USE [YOUR_DATABASE]

GO

/****** Object:  Index [lcc_planning_apps_plotref_UNIX]    Script Date: 10/7/2021 11:26:28 AM ******/

DROP INDEX [lcc_planning_apps_plotref_UNIX] ON [dbo].[lcc_planning_apps]

GO

This will remove the Index from the Database Table.

If you now re-try to create a new record using the same Plot Ref / Feature ID, the new record will be inserted as there are no Unique Constraints on that field anymore.