TLDR: It depends if the field is required or not!

Ensuring data integrity is a crucial aspect of database management, and avoiding duplicate values in specific fields is a common challenge. In OutSystems, developers often wonder whether they can rely on database indexes to enforce uniqueness.

For this example, lets consider our Contact entity, and is expected that our code does not accept the same Email address multiple times, such that each Contact has it’s own email address and Contacts cannot share the same email address.

In this scenario we could think that we can enforce that rule by creating a Unique Index associated with EmailAddress field such as:

This will work if EmailAddress is a required field because no multiple empty EmailAddress fields would exist.

Let’s consider that EmailAddress is not a required field:

Now let’s test it by adding 2 new contacts, both without Email Address filled in:

Adding the first contact will work, but trying to add the second contact we’ll receive a database error, and checking Service Center you’ll see the following exception detail:

This happens because the unique index is also valid for empty values, such that only one record could exist as an empty string.

Database indexes are powerful tools for improving query performance, but they’re not always the best solution for enforcing uniqueness. When designing your OutSystems applications, it’s essential to understand how indexes work and when to use other constraints like unique indexes or business logic validations..