Creating a table in Fabric Warehouse - allow NULL values

[webchris]

Hi there

I created a few tables in my Fabric Warehouse, and some columns were supposed to allow NULL values.

CREATE TABLE [Cleansing].[Allotment]

(

    [Id] [varchar](250)  NOT NULL,

    [OwnerId] [varchar](250)  NOT NULL,

    [IsDeleted] [bit]  NOT NULL,

    [Name] [varchar](8000)  NOT NULL,

    [CurrencyIsoCode] [varchar](250)  NOT NULL,

    [CreatedDate] [datetime2](6)  NULL,

    [CreatedById] [varchar](250)  NULL,

...

When I run a stored procedure to ingest data into that table, I get an error stating "NULL values not allowed in column "CreatedDate".

Hm, I check the create-statement of the table and see that all columns have been re-set to "NOT NULL".

Is this a known issue, has anybody seen this before?

Thanks for help

Christian

[v-nikhilan-msft]

Hi @webchris ,
Thanks for using Fabric Community.
I tried to create a repro of your scenario and was successful in inserting NULL values into the 'CreatedDate' column. I have attached the screenshots for your reference.
1) I created a table named Allotment in my warehouse.

2) I tried to insert a row manually and was successful for inserting null values.

3) I also created a stored procedure for inserting rows into the table.

4) I inserted the row with NULL values.

5) I also created a stored procedure including the CreatedDate column and tried inserting NULL values.

Hence try running the Stored Procedure once again. 
Hope this helps. Please let me know if you have any further queries.

[webchris]

@v-nikhilan-msft 

Good morning, thanks for your investigation.

When you now click on the three dots next to your table and select "Create", does the DDL Code show the correct types (esp. regarding nulls)? On my end, the "create"-statement resets all columns to "Not Null".

Thanks again

Christian

[v-nikhilan-msft]

Hi @webchris ,
Yes you are right, the create statement resets all the columns to "NOT NULL". But I couldnt understand why to create another table?
As per your ask, you stated that NULL columns are not inserted into the table. But when I created the table, I was able to insert NULL values. 
If you are trying to create another table with same columns the editor resets the columns to "NOT NULL". Then you cannot insert NULL values into such table.
Could you explain me your ask more clearly, so that I can help you better?
Thank you.

[webchris]

@v-nikhilan-msft 

I know that, once created, the columns work and the NULL setting. But, we are in a process of developping our environment, which sometimes makes it necessary to re-create a table when the first version did not work - and e.g. more columns need to be Nullable.

When this happens, I can not just change this one column, but instead I always have to re-do the whole work for all columns - in tables with 120 columns that's a complex task.

[v-nikhilan-msft · Accepted solution]

Hi @webchris ,
Thanks for the information. Apologies for the issue you are facing. Currently there is no bug for this feature. We can certainly keep an eye on it for upvote.

Appreciate if you could share the feedback on our feedback channel. 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.

[v-nikhilan-msft]

Hi @webchris ,
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 queries.

[webchris]

I just added a Feedback to the Feedback channel. Feel free to vote up my proposal. Thanks!

[v-nikhilan-msft]

Hi @webchris ,
Thanks for your valuable feedback. Please continue using Fabric Community for any help regarding your queries.