Creating a Primary Key for Lakehouse Table

[ESharathChandra]

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!!

[v-ssriganesh · Accepted solution]

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.

[wardy912]

Hi @ESharathChandra 

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 ENFORCED

CREATE TABLE MyTable (
    id INT NOT NULL,
    name STRING
);

ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED (id) NOT ENFORCED;

[ESharathChandra]

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