[SOLVED] Primary Key in Synapse Serverless SQL Table

Primary Key in Synapse Serverless SQL Table

How can I create a primary key to an Azure Synapse Serverless SQL Database table?

I tried this:

CREATE EXTERNAL TABLE [silver].[table]
(
     [MATNR] char(100) NOT NULL
)
WITH
(
    LOCATION = 'file/tables/bronze_MARA',
    DATA_SOURCE = dsrc,
    FILE_FORMAT = formatparquet
)
ALTER TABLE [silver].[table] ADD PRIMARY KEY (MATNR);

But Synapse serverless does not allow NOT NULL

Also tried this in create table part:

[MATNR] char(100) primary key NONCLUSTERED NOT ENFORCED

returns: 'NOT ENFORCED' is not supported in this version of SQL Server.

and if all columns must be nullable, I cant add any kind of key?

Is it possible at all to add a key on serverless synapse table?

This doesnt work for me, its referring to a dedicated pool: NOT NULL is not allowed for external table columns in Azure Synapase

As I use serverless

Solution

The short answer is you can't. It's important to recognize that "Serverless SQL" is not SQL Server, meaning these aren't actual database objects, and these aren't really Tables. It's akin to Polybase, an abstraction of a schema overlaid on top of text files at runtime. As a result, while the query syntax is SQL, SQL concepts like Indexes and Keys (and many others) are not supported. This is why storage partitioning and well-defined schemas are very important to enhance performance.