Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MaxBronder
Frequent Visitor

Can not insert null value error for nulllable column

During an INSERT statement I´m always receiving this error 

MaxBronder_0-1697174696794.png

The column here is defined as nulllable and I even recreated the table multiple times to assure that.

The error also appears if I´m using ISNULL(column, 0) or if I just put a "0".

I also tried to comment this column out but then the error just came for the next column, and the next column and so on...

2 ACCEPTED SOLUTIONS
MaxBronder
Frequent Visitor

Hi @v-nikhilan-msft 

Thanks for your answer, unfortunately the column was neither primary key, foreign key or use in calculations.

Nethertheless I found a solution, or more like a workaround.

The columns in the Insert statement have been in a different order than the columns were ordered in the create table. So indeed, there were NULL values for a not null column, but it was not the one displayed in the error message, but the one that would have been in the same position in the table.

So for me that sounds like a bug.

Thx and KR Max

View solution in original post

Hi @MaxBronder ,
I have checked with the internal team. There is no active bug for such a scenario currently. 
But you could share your feedback on our feedback link which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.
Feedback Link : Home (microsoft.com)
Thank you.

View solution in original post

8 REPLIES 8
MaxBronder
Frequent Visitor

Hi @v-nikhilan-msft 

Thanks for your answer, unfortunately the column was neither primary key, foreign key or use in calculations.

Nethertheless I found a solution, or more like a workaround.

The columns in the Insert statement have been in a different order than the columns were ordered in the create table. So indeed, there were NULL values for a not null column, but it was not the one displayed in the error message, but the one that would have been in the same position in the table.

So for me that sounds like a bug.

Thx and KR Max

Hi @MaxBronder ,
Thanks for the inputs. In the above comment , are you trying to say that the error message displayed in SSMS was different from the actual error?

@v-nikhilan-msft yes exactly, the wrong column was mentioned in the error.

Hi @MaxBronder ,
Thanks for the information. Glad that your issue got resolved.
I will check with the internal team regarding the error in SSMS.
Appreciate your patience.

Hi @MaxBronder ,
I have checked with the internal team. There is no active bug for such a scenario currently. 
But you could share your feedback on our feedback link which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.
Feedback Link : Home (microsoft.com)
Thank you.

Hi @MaxBronder ,
Glad that your issue got resolved. Please continue using Fabric Community for any help regarding your queries.

v-nikhilan-msft
Community Support
Community Support

Hi @MaxBronder ,
Thanks for using Fabric Community.
As I understand you are trying to insert NULL values into a table, and you are facing error eventhough the column is nullable column. I tried to repro the scenario and was successful in inserting NULL values into my Warehouse table using SSMS. I have attached the screenshots for your reference.

1) Creating a table in Warehouse:

vnikhilanmsft_0-1697194799464.png


2)Inserting NULL values into the table using Warehouse T-sql :

vnikhilanmsft_1-1697194907473.png

3)NULL values inserted into the table successfully:

vnikhilanmsft_2-1697195033554.png

4)Trying to insert NULL columns using SSMS :

vnikhilanmsft_3-1697195096213.png

5)NULL columns inserted successfully:

vnikhilanmsft_4-1697195177296.png

 

There are a few reasons why you might get this error:

  • You are trying to insert a NULL value into a column that is defined as the primary key. Primary keys must always have a value, so they cannot be NULL.
  • You are trying to insert a NULL value into a column that has a foreign key constraint. Foreign key constraints require that the value in the column must exist in the referenced table. If the value in the referenced table is NULL, then the foreign key constraint will be violated.
  • You are trying to insert a NULL value into a column that is used in a calculation or other operation. If the column is NULL, then the calculation or operation cannot be performed.

Hence check for the above reasons in your case and try inserting the rows again. 
Hope this helps. Do let us know if you have any further issues.

Hi @MaxBronder ,
We haven’t heard from you on the last response and was just checking back to see if your issue has been resolved. Please let us know if you have any further issues.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

MayFabricCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Top Kudoed Authors