Primary key for a table is not a physical primary key

[pmscorca]

Hi,

reading the official documentation it seems that isn't possible to create a physical primary key for a warehouse table.

The related syntax requires to specify the NOT ENFORCED clause; so, there is no uniqueness with respect to the primary key.

Why is it not possible to create a real primary key? Thanks

[burakkaragoz]

Hi @pmscorca ,

You are correct: in Microsoft Fabric Warehouse (and many distributed data platforms), primary keys are defined with the NOT ENFORCED clause, meaning that uniqueness is not physically enforced at the storage level. This architectural choice is due to the distributed Delta Lake foundation. Enforcing constraints such as primary keys across distributed nodes would add significant performance and scalability overhead, so these keys are treated as metadata only.

If you need true data uniqueness or referential integrity, the recommended approach is to implement these checks during your data ingestion or transformation processes. You can use tools such as Dataflows, Pipelines, or custom SQL scripts to validate and clean your data before it lands in the warehouse.

References:

• Delta Lake documentation on constraints
• Microsoft Fabric Warehouse documentation

Hope this helps clarify the design! If this post was helpful, please consider giving a kudos and marking it as a solution to assist others in the community.

[pmscorca]

Hi, thanks for your reply.

It's curious that Fabric SQL Database seems to support the primary keys and it is a Fabric workload inside the same ecosystem of a Warehouse. In this case, no overhead?

Thanks

[v-vpabbu]

Hi @pmscorca,

Thank you for reaching out to Microsoft Fabric Community Forum.

Fabric Warehouse is built on a distributed Delta Lake architecture, which is optimized for performance and scalability. Enforcing constraints like primary keys across distributed nodes would introduce significant overhead, so they are defined as metadata only and not physically enforced.

If you need to ensure data uniqueness or maintain referential integrity, you’ll need to handle it within your ETL/ELT process—using Dataflows, Pipelines, or custom SQL logic to validate and clean the data as it moves through your system.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu