Use of Unique Index in TSQL

Question

In TSQL what is the significance of having Unique constraints 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-constraint-in-db2

Answer 1

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"

Answer 2

Hi @SRIVASTAV, SOMYA

PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.

Are you using Data Warehouse? For Warehouse, PRIMARY KEY and UNIQUE constraint are only supported when NONCLUSTERED and NOT ENFORCED are both used, which means it will not check for the uniqueness of the primary key values.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".