Prevent duplicate records apart from blank/null values

Ryan Anderson

I want to prevent duplicate records in a table but allow for blank/null values of those fields. It seems with a unique index, it includes null values.

So where I have email | phone fields in my table, and I want to allow a user add either or, the null values are considered a duplicate.

Michael Udinski

Hey @Ryan Anderson - Unique indexes should allow for multiple null values, I just confirmed this with a couple of different tests. "Blank" or an empty string, however, is not the same as null. Your database field would need to have nullable set to yes and take in a value of null not an empty string... Multiple empty strings will conflict with a unique index

Michael Udinski

I would also recommend implementing this logic into your function stack as well... A good example of this is with the out-of-the-box /signup API endpoint, which first uses Get Record to look up a record based on a value and enforces that the return variable = null (in other words, enforcing the record doesn't already exist before continuing with the rest of the logic).