Use of Unique Index in Fabric Warehouse

[Rohitb]

In fabric data warehouse, what is the significance of having Unique constraint with Not Enforced enabled? Even after creating this I am able to insert duplicates within the table

https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints

https://stackoverflow.com/questions/54513136/when-we-should-use-not-enforced-with-foreign-key-constr...

[mikeburek]

Interestingly, in Azure Synapse Analytics Dedicated Pools, I can create incorrect results when I enter duplicate data into a PRIMARY KEY NOT ENFORCED or UNIQUE NOT ENFORCED.

But I cannot create incorrect results in Fabric Warehouse.

Since we cannot see execution plans for Fabric Warehouse, I can't tell if the execution engine really uses this as an optimization. But it clearly doesn't shortcut the results the way Azure Synapse Analytics Dedicated Pools does.

Does anyone have any interesting results they have found when putting duplicate data into a PRIMARY KEY or UNIQUE?

[frithjof_v]

The way I understand it, and as other commenters also are pointing to, the NOT ENFORCED constraints are used for query optimizations.

I think it's like we're telling the engine: "trust me, all values in this column are unique. If you find a value, you don't need to look for the same value in other rows." So the engine can stop looking, instead of spending time on looking through all the rows. It can probably speed up read operations like joins, where-statements, group by, etc.

But it might lead to incorrect results if the data doesn't conform to the not enforced constraint. E.g. the query engine stops looking for other occurences of a value, because we have told it that there is only one copy of that value (by adding the not enforced constraint), but if in reality there are more occurences of that value - then the query engine might miss those rows.

So, when adding NOT ENFORCED constraints, we are taking the responsibility for uniqueness. We can use the ETL process to check for uniqueness. The benefit is improved read performance.

[NandanHegde · Accepted solution]

setting the NOT ENFORCED option means the data engine does not enforce the constraint. for example, if the unique / primary key constant is NOT ENFORCED, then the engine will not check for duplicates on insert. this can improve insert performance. in the case of foreign key, with NOT ENFORCED the relationship can be inserted / deleted via separate transactions. but the query engine will use the constraint as a hint, and tools can know about the constraint.

not all sql database support NOT ENFORCED, say SqlServer. in the case of Fabric Warehouse, its the opposite, that is it does not support ENFORCED. with Fabric Warehouse the constraints are just "hints"

[v-huijiey-msft]

Hi @Rohitb ,

Constraints are not actually enforced in Fabric Warehouse, so the NOT ENFORCED keyword is required.

This is a limitation of horizontally scaling SQL services such as Fabric SQL, where these constraints are more for engine optimization.

Here is a case with a similar problem to yours that might be helpful:

Solved: UNIQUE constraint doesn't work in Fabric Warehouse - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Rohitb]

In fabric data warehouse, what is the significance of having Unique constraint with Not Enforced enabled? Even after creating this I am able to insert duplicates within the table

https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints

https://stackoverflow.com/questions/54513136/when-we-should-use-not-enforced-with-foreign-key-constr...
Can someone help me with this ?