How to set any column as primary key in Azure SQL data warehouse

Question

I am trying to set one column in DB as primary key but I always get this error:

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.

While doing R & D,I found that there is no concept of primary keys & foreign keys in Azure SQL data warehouse then how can we accomplish this in it.

Any ideas?

Answer 1

Azure SQL Data Warehourse doesn't support Primary Key.

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview

Answer 2

< Quote >

Azure SQL Data Warehouse supports these table constraints:

• PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used.
• UNIQUE constraint is only supported with NOT ENFORCED is used.

Source: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-table-constraints

Answer 3

I've just signed up for StackOverflow RSS feeds on Synapse. The first feed was this post. I thought it was a recent one but just notice it is from 2 years ago, @Lurifaxel just posted an answer 4 days ago. I'll learn how to work around here ... eventually. :)

To the topic, database constraints like primary keys and foreign keys are resources to prevent bad client code to mess up data consistency. This is very popular (and almost mandatory) on OLTP databases.

In OLAP systems, where you load data either in batches or streams, you usually don't want this because it slows down the ingestion process. You usually rely on stage tables and CTAS techniques that will render the consistent version of your table.

OLTP transactions should not be considered in OLAP / BI system like Synapse. Which makes primary keys and foreign key constraints just irrelevant and not necessary.

In case you really need to create a primary key constraint in a table, Synapse pool can do that but it will not validate data already in the table. Only new data (insert/update) will be checked for duplication.

Answer 4

Alter a table to create an unenforced and non clustered primary key like this:

-- Schema: sales
-- Table:  customer
-- primary key column: customerid
ALTER TABLE sales.customer
ADD CONSTRAINT PK_customer_customerid PRIMARY KEY NONCLUSTERED (customerid) NOT ENFORCED;

Keep in mind that you'll have to make sure that there aren't any rows with duplicate values manually! See the docs for further information.