topic Re: Primary key for a table is not a physical primary key in Data Warehouse

Primary key for a table is not a physical primary key

pmscorca — Tue, 03 Jun 2025 18:07:09 GMT

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

Re: Primary key for a table is not a physical primary key

Anonymous — Wed, 04 Jun 2025 07:31:03 GMT

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

Re: Primary key for a table is not a physical primary key

burakkaragoz — Wed, 04 Jun 2025 08:05:30 GMT

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:

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.

Re: Primary key for a table is not a physical primary key

pmscorca — Wed, 04 Jun 2025 16:17:57 GMT

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

Re: Primary key for a table is not a physical primary key

Anonymous — Mon, 09 Jun 2025 09:14:10 GMT

Hi pmscorca,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

Re: Primary key for a table is not a physical primary key

pmscorca — Mon, 09 Jun 2025 14:48:10 GMT

Instead, it's curious that Fabric SQL Database seems to support the primary keys.

Thanks

Re: Primary key for a table is not a physical primary key

Anonymous — Tue, 10 Jun 2025 11:47:39 GMT

Hi pmscorca,

The key reason lies in the architectural goals, while both are part of the Microsoft Fabric ecosystem, they serve different purposes and are optimized accordingly.

Regards,

Vinay Pabbu