CODEWITHSUNDEEP
azureazure-synapse
sthambi
sthambi

Reputation: 257

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

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://[email protected]' 
    )
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?

Upvotes: 1

Views: 1199

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11474

In Azure Synapse, for Creating a Primary Key We should mention it as NONCLUSTERED and as NOT ENFORCED.

To learn more about Table Constraints in Synapse, Please Refer this :

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-table-constraints

Please follow this link which has step by step process to create an External Table in Synapse.

https://dwgeek.com/create-external-tables-in-azure-synapse-analytics.html/

We can create Primary Key like this after following steps in above link.

create EXTERNAL TABLE external_table_name
(
[column1]   varchar(100) primary key NONCLUSTERED NOT ENFORCED,
[column2]  integer,
[column3]  integer,
[column4]  integer
)  
WITH (
LOCATION='/Datasets/data.csv',
DATA_SOURCE = external_source_name,  
FILE_FORMAT = external_file_format_name
);

In case, If the above doesn’t work, I suggest you to go through this :

https://learn.microsoft.com/en-us/answers/questions/819253/not-null-is-not-allowed-for-external-table-columns.html

Upvotes: 0

Related Questions