Answered

Add unique constraint to column in Dataverse Table

(0) Share

Report

Posted on by Hagru

Hello!

I would like to add a Unique constraint to a custom column in my Contact table. The reason I want to do this is because they will have their own Customer ID coming from another system through an integration, and we don't want to create new contacts with the same Customer ID as another already existing Contact.

The custom field I would like to add this constraint to is a "Whole number", and I would like it to stay that way.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (1)

All responses (6)

Answers (1)

Verified answer

Guido Preite 1,490 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

You just need to create an Alternate Key https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-alternate-keys-portal

in this way you will not have duplicates in the column you defined as alternate key

Was this reply helpful? Yes No
Hagru 8 on at

Like (0)

Report
Copy link

Link copied!

Hi @GuidoPreite !
So just by creating a new key, selecting the column, and give it a name, I will make a unique constraint to that column?

Was this reply helpful? Yes No
Guido Preite 1,490 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

yes, that is the purpose of alternate keys, to ensure a set of fields (1 or more than 1) is unique, just as warning, make sure the column you are choosing for the alternate key doesn't have already duplicates or the alternate key will not be activated.

Was this reply helpful? Yes No
Hagru 8 on at

Like (0)

Report
Copy link

Link copied!

Perfect! Thank you very much, I will keep that in mind and check for already duplicate instances.
One last thing, as it is not a required field, and a lot of the contacts won't have data in the field, will it stop me from creating multiple contacts with that field being empty?

Was this reply helpful? Yes No
Guido Preite 1,490 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

empty is not considered a unique value for Alternate Keys, so you can have multiple rows with an empty value, but when filled they will be unique.

Was this reply helpful? Yes No
Hagru 8 on at

Like (0)

Report
Copy link

Link copied!

Perfect, thank you!

Was this reply helpful? Yes No