Unique Keys what you can do and what you cannot?

The unique key functionality is something brand new and released in Spring Release 2019. It was briefly discussed on Soren’s blog (link here) but I decided to check and write you little more about what I found.

Unique Key – the idea

When creating the secondary key you may add an additional parameter called Unique. If you do so, the system will not allow users to have exact the same values in the fields which are in the key. In other words it works the same as the primary key.

As you can see below I created two keys – one primary for the field Code and the second one for the Description field. I marked it as unique. It means that I will not be able to have two records with the same Description.

And this is what the user will see when will try to have the same Descriptions in both rows. But remember the check will be done when inserting or modifying the record. Not when you will leave from the field.

Also if you would try to add the record from the code with not unique values, then the system will show the error. And it does not matter if the table is temporary or not.

If you are curious how the SQL statement looks like when inserting the record then here is what I can see from the debugger.

INSERT INTO “Cronusdk”.dbo.”CRONUS Danmark A_S$My Table with Unique Key$1011e5ed-d61d-4644-ad51-39805525ee0d” (“Code”,”Description”,”Open”,”Type”) VALUES (@0,@1,@2,@3) SELECT @@DBTS

Multiple unique keys

There is possibility to have more than one unique key in the table. Also remember that you can have more fields in one key with the unique value. Then the system will allow to insert all unique combinations in such fields.

However adding a new key, after first publish, with a parameter unique will not be allowed with the schemaUpdateMode Synchronize. So at least you will need to perform ForceSync.

Empty value is still a value

That may be some inconvenience using the unique keys. The check of the unique combination is done just after insert the record to the table. It means that if you have the record with empty values in your unique key already, then user just after putting the value in the primary key, will see the error that there is already a record with the same unique values.

Case sensitive

If you will create the unique key based on the text field then it will also be not possible to have the same value in the field if only letter case is different

Not supported in Table Extensions

And at the end some bad news. You cannot create unique keys in the table extensions. What does it means for you? We cannot create a unique value in standard tables neither for our own added fields nor for standard fields.

This means that you can use this functionality only in your own tables.