Forum Discussion
Creating a Primary Key for Lakehouse Table
Hi, Can we create Primary key constraints in a Lakehouse table? If so how and if no why wasn't it available?
We have tried creating by using Alter table and not enforced. But it's giving an error.
Error message - Table constraints (Primary and Foreign key) are not supported in V2 Datasource tables
Thanks for your help in advance!!
Hello ESharathChandra,
Thank you for reaching out to the Microsoft Fabric Community.
Unfortunately, Microsoft Fabric's Lakehouse tables (V2 Datasource, based on Delta Lake) do not support primary key or foreign key constraints, even with the NOT ENFORCED option. This is why you're encountering the error: "Table constraints (Primary and Foreign key) are not supported in V2 Datasource tables."The Lakehouse in Fabric is designed for big data analytics and uses Delta Lake as its storage format. Delta Lake prioritizes scalability and performance over traditional relational database constraints like primary keys, which are more common in OLTP systems. Instead, data integrity is typically managed through ETL/ELT processes or data quality checks.
We recommend that you submit this feature request to the Ideas forum: Fabric Ideas - Microsoft Fabric Community, If the idea receives enough community support through votes, the Microsoft product team may consider it for future releases.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
4 Replies
Hello ESharathChandra,
Thank you for reaching out to the Microsoft Fabric Community.
Unfortunately, Microsoft Fabric's Lakehouse tables (V2 Datasource, based on Delta Lake) do not support primary key or foreign key constraints, even with the NOT ENFORCED option. This is why you're encountering the error: "Table constraints (Primary and Foreign key) are not supported in V2 Datasource tables."The Lakehouse in Fabric is designed for big data analytics and uses Delta Lake as its storage format. Delta Lake prioritizes scalability and performance over traditional relational database constraints like primary keys, which are more common in OLTP systems. Instead, data integrity is typically managed through ETL/ELT processes or data quality checks.
We recommend that you submit this feature request to the Ideas forum: Fabric Ideas - Microsoft Fabric Community, If the idea receives enough community support through votes, the Microsoft product team may consider it for future releases.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.You can create primary key constraints using the ALTER TABLE statement, but only with the following conditions:
The primary key must be NONCLUSTERED
It must be NOT ENFORCEDCREATE TABLE MyTable ( id INT NOT NULL, name STRING ); ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (id) NOT ENFORCED;Thank you for quick response wardy912
When tried creating in lakehouse using notebook but getting below error :
I guess it's working in warehouse but not in lakehouse
Hello, if anyone is interested in primary and foreign keys support in Lakehouse I created a new feature request in Idea portal. I used this post as reference in the request.
https://community.fabric.microsoft.com/t5/Fabric-Ideas/Primary-and-Foreign-key-support-in-Lakehouse/idi-p/4833394#M163888
