You’re offline. This is a read only version of the page.
46
Dear Community,
Is it possible for a Dataverse table to add unique constraint as combination of two columns based on the column value?
To be more clear please find the example below:
Combination of ColumnA and ColumnB should be unique in the Table_X if ColumnB value is 1 (true).
Table_X
Share
Report-----------------------------------
|ID(key)|ColumnA(int)|ColumnB(bit)|
-----------------------------------
| 1 | 250 | 1 |
-----------------------------------
| 2 | 301 | 1 |
-----------------------------------
| 3 | 250 | 0 |
-----------------------------------
| 4 | 250 | 0 |
-----------------------------------
As you can see in the Table_X I can have only one record with the combination: ColumnA = 250 and ColumnB = 1 (record with ID=1). However I can have many records with combination of ColumnA = 250 and ColumnB = 0 (records with ID equals 3 and 4)
In SQL server, I would simple create an unique index to achieve this:
CREATE UNIQUE NONCLUSTERED INDEX UQ_INDEX_COLUMNA_COLUMNB_TRUE
ON Table_X(ColumnA, ColumnB)
WHERE ColumnB = 1;
Please note that I know how to accomplish the same goal from the application side. I'm looking here to see if it is possible to do it on the entity level. I'm also aware that I can create Keys for Entity(Tables) but I don't see how to include specific value for ColumnB.
Thank you!
Hello,
The only way I see is to create a "Conditional" alternate key (just a theory, never tried it because I never had such a scenario). Here is how it might be architected:
1. Create a new field that will serve as a placeholder for the key value.
2. Create an alternate key that will contain only the field created in step 1.
3. Create a sync plugin or real-time workflow that based on the data in the record will update the value of field 1 based on the condition. So if "Column B" equals 1, copy the value of "Column A" to the field created in step 1 and leave it blank if the condition is not met.
#1
mmbr1606
22
Super User 2025 Season 1
#2
stampcoin
17
#3
ankit_singhal
11
Super User 2025 Season 1
All responses (
Answers (
