NOT NULL is not allowed for external table columns in Azure Synapase

Dhilip Subramanian 56 Reputation points
2022-04-20T10:28:56.997+00:00

I am creating an external table in Azure Synapse. My data is in the parquet format and sits in the data lake.

Creating a table called "test"

> IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat')
CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]
WITH ( FORMAT_TYPE = PARQUET)
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'sandboxs2345_dfs_core_windows_net')
CREATE EXTERNAL DATA SOURCE [sandboxs2345_dfs_core_windows_net]
WITH (
LOCATION = 'abfss://jobsnumber@sandboxs2345.dfs.core.windows.net'
)
GO

CREATE EXTERNAL TABLE [test] (
[Name] nvarchar(4000),
[JobNo] nvarchar(4000) PRIMARY KEY
)
WITH (
LOCATION = 'New/update.parquet',
DATA_SOURCE = [sandboxs2345_dfs_core_windows_net],
FILE_FORMAT = [SynapseParquetFormat]
)
GO

SELECT TOP 100 * FROM [dbo].[test]
GO

I have two columns called Name & JobNo. I wanted to make JobNo a Primary Key. I am getting the below error when I am trying to run the above query

NOT NULL is not allowed for external table columns.

Can anyone advise what would be the issue?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,690 questions
{count} votes