Primary key and unique constraints in Synapse Dedicated Pool

In Azure Synapse Dedicated SQL Pool, PRIMARY KEY and UNIQUE constraints are supported for design and query optimization purposes, but they must be declared with the NOT ENFORCED clause:

CREATE TABLE dbo.dim_customers (
    customer_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100),
    CONSTRAINT PK_dim_customers PRIMARY KEY NONCLUSTERED (customer_id) NOT ENFORCED,
    CONSTRAINT UQ_email UNIQUE NONCLUSTERED (email) NOT ENFORCED
)
WITH (
    DISTRIBUTION = HASH(customer_id),
    CLUSTERED COLUMNSTORE INDEX
);

Why constraints are not enforced

Enforcing a unique constraint across a distributed system requires an inter-node network lookup before every single row insertion to verify that the key does not already exist on another node. In a warehouse ingesting 50 million rows, this distributed locking mechanism would bring loading performance to a crawl.

Instead, the Synapse query optimizer uses the NOT ENFORCED declaration as a statistical hint. For example, if it knows a table is unique on customer_id, it can optimize join trees and eliminate distinct aggregates.

Enforcing uniqueness in the pipeline

Because the warehouse will not reject duplicates, data pipelines must guarantee uniqueness before committing. The standard technique uses a staging table followed by a T-SQL MERGE statement:

MERGE dbo.dim_customers AS target
USING dbo.staging_customers AS source
ON (target.customer_id = source.customer_id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name, target.email = source.email
WHEN NOT MATCHED THEN
    INSERT (customer_id, email, name)
    VALUES (source.customer_id, source.email, source.name);

This ensures idempotent, duplicate-free warehouse updates even if an ingestion pipeline retries after a network failure.