Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Marusyk
Advocate II
Advocate II

UNIQUE constraint doesn't work in Fabric Warehouse

Could anyone suggest why UNIQUE constraint doesn't work in the warehouse? 

I expected that creating PRIMARY KEY will be enough to not all duplication but the duplication is allowed even if I created both

 

 

CREATE TABLE [dev].[Calendar]
(
    [Year] INT NOT NULL
);
ALTER TABLE [dev].[Calendar]  ADD CONSTRAINT PK_Calendar_1 PRIMARY KEY NONCLUSTERED ([Year]) NOT ENFORCED;
ALTER TABLE [dev].[Calendar]  ADD CONSTRAINT UK_Calendar_ UNIQUE NONCLUSTERED ([Year]) NOT ENFORCED;

INSERT INTO [dev].[Calendar] ([Year]) VALUES (2020);
INSERT INTO [dev].[Calendar] ([Year]) VALUES (2020);
INSERT INTO [dev].[Calendar] ([Year]) VALUES (2020);
INSERT INTO [dev].[Calendar] ([Year]) VALUES (2020);

SELECT * FROM [dev].[Calendar]

 

 

Result:
Marusyk_0-1713876338246.png

 

1 ACCEPTED SOLUTION

You would need to build in de-duplicating in your loading processes.  Think of Fabric SQL like Databricks, Snowflake etc, none of these services have enforcable unique/foreign key constraints.

 

Unique and Foreign Key contraints are used by the Fabric SQL engine for optimisation purposes.  E.G if you define a column as unique and you use that column in a filter or join, once the SQL engine finds that value it knows it won't occur again (as an example).

 

So you won't use these contraints in the way you would with SQL Server.

 

-------------------------------------------------

If my post has been useful please consider

providing Kudos or marking as the Solution

-------------------------------------------------

View solution in original post

3 REPLIES 3
AndyDDC
Super User
Super User

Hi @Marusyk constraints are not actually enforced in Fabric Warehouses, hence the NOT ENFORCED keyword is required. This is a limitation of scale out sql services like Fabric SQL.  These constraints are more for engine optimisation 

so how to deny duplication then?

do you mean unique constraints mean nothing to me as a database admin?

If they only for the engine how can we use it?

You would need to build in de-duplicating in your loading processes.  Think of Fabric SQL like Databricks, Snowflake etc, none of these services have enforcable unique/foreign key constraints.

 

Unique and Foreign Key contraints are used by the Fabric SQL engine for optimisation purposes.  E.G if you define a column as unique and you use that column in a filter or join, once the SQL engine finds that value it knows it won't occur again (as an example).

 

So you won't use these contraints in the way you would with SQL Server.

 

-------------------------------------------------

If my post has been useful please consider

providing Kudos or marking as the Solution

-------------------------------------------------

Helpful resources

Announcements
Fabric July 2025 Monthly Update Carousel

Fabric Monthly Update - July 2025

Check out the July 2025 Fabric update to learn about new features.

July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.