Cannot insert NULL into nullable column in Synapse Analytics

Question

Cannot insert NULL into nullable column in Synapse Analytics

Adam Eljasiak 1

Nullability constraints do not behave as expected in our Synapse Analytics data warehouse. All columns in all tables are nullable, and primary keys are not enforced as described here, yet sometimes we get errors like "Cannot insert the value NULL into column \

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-08-03T19:55:14.583+00:00
Hello @Adam Eljasiak ,

Thanks for the question and using MS Q&A platform.

I have gone through your post and see this is strange behavior.

also in the 1st place, you can't insert a null value on a primary key constraint defined column.

This can be demonstrated by the following:

CREATE TABLE [dbo].[Dim_Test] ( [Col1] NVARCHAR(10), --not null, [Col2] NVARCHAR(10), [Col3] NVARCHAR(10), [Col4] NVARCHAR(10), [Col5] NVARCHAR(10) --CONSTRAINT PK_Dim_Test PRIMARY KEY NONCLUSTERED ([Col1]) NOT ENFORCED ) INSERT INTO dbo.[Dim_Test] (Col1, Col2, Col3, Col4, Col5) VALUES (NULL, 2, 3, 4, 5) ALTER TABLE [dbo].[Dim_Test] ADD CONSTRAINT PK_Dim_Test PRIMARY KEY NONCLUSTERED ([Col1]) NOT ENFORCED

You will see the below error message:

Cannot define PRIMARY KEY constraint on nullable column in table 'Dim_Test'.
Could not create constraint or index. See previous errors.

Bhargava-MSFT 31,261 Microsoft Employee Moderator

< continuation to the above message >

but we can bypass this error message using the below approach:

CREATE TABLE [dbo].[Dim_Test]  
 (  
          
     [Col1] NVARCHAR(10), --not null,  
     [Col2] NVARCHAR(10),  
     [Col3] NVARCHAR(10),  
     [Col4] NVARCHAR(10),  
     [Col5] NVARCHAR(10)  
      
     CONSTRAINT PK_Dim_Test PRIMARY KEY NONCLUSTERED ([Col1]) NOT ENFORCED  
 )  
 WITH  
 (  
     CLUSTERED COLUMNSTORE INDEX  
 )  
 GO  
   
 INSERT INTO dbo.[Dim_Test] (Col1, Col2, Col3, Col4, Col5) VALUES (NULL, 2, 3, 4, 5)  
  
 result:   
   
 (1 row affected)  
  
Completion time: 2022-08-03T12:50:13.9025012-07:00

and I totally agree with you on the "Cannot insert NULL into nullable column in Synapse Analytics". This seems like a product issue.

Let me check with my internal team and get back to you.

Thomas Schouten 61 Reputation points

2022-08-15T10:44:34.24+00:00

@Bhargava-MSFT Do you have any updates on this? If it's a product issue, we could also submit a Microsoft Support ticket.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-08-15T16:37:28.42+00:00

Hello @Thomas Schouten ,

Yes, this is a product issue. I have reported this issue to the product team. Let me check with them and get back to you with an update.
Subashri Vasudevan 11,226 Reputation points

2022-09-30T05:27:01.02+00:00

Hi @Bhargava-MSFT ,

We have the same issue, is the fix in progress? Any idea when it will be live?

Please check and let us know as it stalls our development activity.

Awaiting your response

TIA.
Thomas Schouten 61 Reputation points

2022-09-30T08:48:01.257+00:00

According to our most recent communication with Microsoft Support, the product issue is not resolved yet but there are at least three workarounds:
Christian Rivera Diaz 0 Reputation points

2023-04-26T19:57:47.8333333+00:00

Any updates?
Shrenik Shah 0 Reputation points

2024-01-19T12:38:32.1433333+00:00

@Bhargava-MSFT Any updates on this issue, even we are facing exact same issue and our analytics is getting affected due to this.

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-08-03T19:55:14.583+00:00

Hello @Adam Eljasiak ,

Thanks for the question and using MS Q&A platform.

I have gone through your post and see this is strange behavior.

also in the 1st place, you can't insert a null value on a primary key constraint defined column.

This can be demonstrated by the following:

CREATE TABLE [dbo].[Dim_Test] ( [Col1] NVARCHAR(10), --not null, [Col2] NVARCHAR(10), [Col3] NVARCHAR(10), [Col4] NVARCHAR(10), [Col5] NVARCHAR(10) --CONSTRAINT PK_Dim_Test PRIMARY KEY NONCLUSTERED ([Col1]) NOT ENFORCED ) INSERT INTO dbo.[Dim_Test] (Col1, Col2, Col3, Col4, Col5) VALUES (NULL, 2, 3, 4, 5) ALTER TABLE [dbo].[Dim_Test] ADD CONSTRAINT PK_Dim_Test PRIMARY KEY NONCLUSTERED ([Col1]) NOT ENFORCED

You will see the below error message:

Cannot define PRIMARY KEY constraint on nullable column in table 'Dim_Test'.
Could not create constraint or index. See previous errors.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-08-03T19:56:03.477+00:00

< continuation to the above message >

but we can bypass this error message using the below approach:

CREATE TABLE [dbo].[Dim_Test] ( [Col1] NVARCHAR(10), --not null, [Col2] NVARCHAR(10), [Col3] NVARCHAR(10), [Col4] NVARCHAR(10), [Col5] NVARCHAR(10) CONSTRAINT PK_Dim_Test PRIMARY KEY NONCLUSTERED ([Col1]) NOT ENFORCED ) WITH ( CLUSTERED COLUMNSTORE INDEX ) GO INSERT INTO dbo.[Dim_Test] (Col1, Col2, Col3, Col4, Col5) VALUES (NULL, 2, 3, 4, 5) result: (1 row affected) Completion time: 2022-08-03T12:50:13.9025012-07:00

and I totally agree with you on the "Cannot insert NULL into nullable column in Synapse Analytics". This seems like a product issue.

Let me check with my internal team and get back to you.
Thomas Schouten 61 Reputation points

2022-08-15T10:44:34.24+00:00

@Bhargava-MSFT Do you have any updates on this? If it's a product issue, we could also submit a Microsoft Support ticket.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-08-15T16:37:28.42+00:00

Hello @Thomas Schouten ,

Yes, this is a product issue. I have reported this issue to the product team. Let me check with them and get back to you with an update.
Subashri Vasudevan 11,226 Reputation points

2022-09-30T05:27:01.02+00:00

Hi @Bhargava-MSFT ,

We have the same issue, is the fix in progress? Any idea when it will be live?

Please check and let us know as it stalls our development activity.

Awaiting your response

TIA.
Thomas Schouten 61 Reputation points

2022-09-30T08:48:01.257+00:00

According to our most recent communication with Microsoft Support, the product issue is not resolved yet but there are at least three workarounds:
Christian Rivera Diaz 0 Reputation points

2023-04-26T19:57:47.8333333+00:00

Any updates?
Shrenik Shah 0 Reputation points

2024-01-19T12:38:32.1433333+00:00

@Bhargava-MSFT Any updates on this issue, even we are facing exact same issue and our analytics is getting affected due to this.

Share via

Cannot insert NULL into nullable column in Synapse Analytics

Your answer