UNIQUE constraint doesn't work in Fabric Warehouse

[Marusyk]

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:

[AndyDDC]

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 

[Marusyk]

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?

[AndyDDC · 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

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