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?

Legibility

Related info:dba.stackexchange.com/questions/187065/… – Idoux

SQL Data Warehouse currently doesn't support Primary or Foreign keys. What is your use case? – Manwell

I am using SQL data warehouse & need to set primary key in one table & forgein key in another,then what is the approach to accomplish this. – Legibility

I have read there is concept of indentifier in Azure SQL Data Warehouse on place of unique keys..Any Suggestions over same. – Legibility

Answer 1

Azure SQL Data Warehourse doesn't support Primary Key.

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

Exciter

This right here. DRI is not supported in Azure SQL DW. – Shupe

Please mark it as an answer if it answered your question. – Exciter

If azure sql dw does not support primary and foreign keys then how azure calls it a relational since the relationship between tables is established by these keys? – Kissee

DRI means "Declarative Referential Integrity" for those wondering. – Somniferous

Answer 2

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.

Haroldson

Answer 3

< 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://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-table-constraints

Writing

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.

Scum