The Problem - multi-column uniqueness
Sometimes you have a scenario where the records in your table need to be unique in terms of multiple field values. For instance, I might have a Thing record which is linked to a particular Task and has a Type, and was created by a particular User.
What if I want to enforce the rule that only 1 record can exist in the table for a particular combination of Task, Type, and User?
The business rule approach
There's multiple ways to solve this problem. The first thing I thought of is a before-insert business rule which checks the whole database with a GlideRecord query to see if any other record matches those 3 field values, and then aborts the action if it finds one.
Turns out this is pretty bad on performance for particularly large tables0 and/or where many transactions are going on and frequently.
The other issue is that it doesnt prevent updates, like changing a Thing of Type A to Type B, but a record for Task, Type and User already exists with type B. But if we change it to be before-update and before-insert, now we need to do the full DB query every time a record is updated. Even worse for performance.
Let the database do the work
Luckily, we can actually enforce this at the Database layer with something called a Unique Table Index.
Creating one is very simple: Open up the Table [sys_db_object] record for your Thing table. Scroll to the bottom to find Database Indexes and click New.
Then in the page that pops up, add all the fields you want to enforce the Unique constraint across and click Unique Index over on the left.
This will spin up an indexing job and email you when the index has finished being created.
Now any time the database detects a duplicate across these fields, it will abort the action before any kind of save can be made and this error will be displayed, similar to when you make a single field Unique using the checkbox on the Dictionary [sys_dictionary] record:
Warning before implementing this
This is an irreversible change, if you make the index using the wrong fields or find out later you don't want it to work this way, your only way out is to clone the instance. Make sure you're very sure that this is how you want your app to behave before applying the index.
