SQL – Adding Unique Constraints

by David Crowther

Question:

I would like to enforce unique values in my SQL Table. Is this possible and how is it best to do it?

Answer:

Enforcing unique values within your SQL Table columns is good practice as it can maintain the integrity of your data. For example, if each Plot Reference in your SQL table must have a unique Plot Reference number, you don’t want to allow users to create records with the same value twice.

The best way to enforce unique values is to create an INDEX against that field in the Table and here is how you can do it.

Find the SQL table in your Database and open the INDEXES folder.

There are likely some existing Indexes, such as an index for the Primary Key field and if it’s a spatial table it should have a spatial index.

By right clicking on the Indexes folder you can choose New Index > Non-Clustered Index.

In the New Index window, check the table name is your chosen table and then rename index so that it makes more sense to you. We recommend naming it after the Tablename_FieldName_UNIQUEINDEX e.g.  lcc_planning_apps_plotref_UNIX.

Tick the Unique tick box option and then choose Add.

This then allows you to tick to choose the field/s that should have a unique value. Here we are ticking the Plot_Ref field.

Choose OK and the Plot_Ref field has been added to the New Index options.

Choose OK again and the new INDEX will be added to the SQL Table.

If you now try to create a new record that has an existing value in the Plot_Ref field, try this in QGIS or via our webGIS e.g. MapThat and now because there is a unique constraint, the database will refuse to create the record.

By creating a Unique Constraint Index on your Table, you have now ensured that your users must enter a unique value for specific fields.