Allow Unique Index on Columns that intersect the distribution column
If SET tables aren't available, at least allow an UNIQUE INDEX to be placed on a table if the DISTRIBUTION column is found in the columns of the UNIQUE INDEX
Table with locationid and productid with any number of fact columns. Partition the table by productid. All the data for any productid will be on one specific distribution. Allow that distribution to enforce uniqueness on locationid + productid.
Primary key and unique constraints are now supported by Azure Synapse SQL Analytics tables.
Reiterating my own question. How do we get this to work?
Synapse throws an error when I try to create a primary key unless I specify NOT ENFORCED, which basically means it is not a constraint at all.
Reiterating Robert's question... How do we use this? It doesn't work for me either.
Actually, I have been able to create a unique constraint with the NOT ENFORCED clause, but of course, this is not really a constraint at all. It's an optimizer hint, and it does not do me any good. I hope this is not what this post is referring to. Please advise.
Robert Golik (o365) commented
Hi, great to hear this ....
But do we have to do something special to use the advantage of unique constraints?
I'm trying this:
create table test (id int not null);
-> Commands completed successfully.
alter table test add CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id);
-> Msg 104467, Level 16, State 1, Line 52
Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.
create unique index udx_id on test(id);
->Msg 104421, Level 16, State 1, Line 54
Creation of indexes with unique constraint is not supported in PDW.
Microsoft Azure SQL Data Warehouse - 10.0.10783.0 Oct 26 2019 23:24:02 Copyright (c) Microsoft Corporation