CODEWITHSUNDEEP
azure-sql-databaseazure-synapse
shweta_kaushish
shweta_kaushish

Reputation: 151

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

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?

Upvotes: 11

Views: 32109

Answers (4)

Armando Lacerda
Armando Lacerda

Reputation: 81

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.

Upvotes: 7

Lurifaxel
Lurifaxel

Reputation: 714

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.

Upvotes: 1

AecorSoft
AecorSoft

Reputation: 494

< 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

Upvotes: 3

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

Azure SQL Data Warehourse doesn't support Primary Key.

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

Upvotes: 9

Related Questions