Share
Report
48Dear 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
-----------------------------------
|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!
All responses (
Answers (
