Forum Discussion
Advocate IVData Warehouse deployment fails when new NOT NULL column added in schema
Greetings!
We have a deployment pipeline that we are attempting to move a warehouse schema change through, we are adding some not null columns.
The deployment from our Dev environment to Test is failing with the following error:
Microsoft SqlClient Data Provider: Msg 24735, Level 16, State 1, Line 1 Only nullable columns can be added to an existing table. SqlMSBuild: Script execution error. The executed script: ALTER TABLE [DM_Schema].[SummaryTable] ADD [NewColumn] INT NOT NULL,CONSTRAINT [SD_SummaryTable_2d66d69a5ee8421398ab13068f0c28b4] DEFAULT 0 FOR [NewColumn]A couple of questions:
1) Why is this blocking? I can't seem to locate where NOT NULL columns are not supported by deployment pipeline? Is this a legitimate bug?
2) Why is does it appear to (rightly) add a DEFAULT 0 constraint even though no such constraint was defined in DEV environment?
3) Our current process with complicated schema changes is to use Schema Compare tool in VS Code, and promote complicated schema changes outside of the deployment pipeline, adding a column generally is not considered a complicated schema change, so just wondering if there is a way to do this within deployment pipeline?
Thanks!
5 Replies
- NandanHegde
Super User
Not a deployment pipeline issues but a generic situation within Fabric warehouse
You can only add nullable columns in an existing table.Not nullable columns can only be created during table creation script
v-saisrao-msftCommunity Support
HI anon97242,
Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.
Thank you.
- anon97242
Additionally, it seems Schema Compare tool does not handle this well either. It attempts to create a NOT NULL column via Alter Statement, when it should be creating a temp table with new schema def / loading the temp table with existing then perform a drop & rename.
- v-saisrao-msft
Hi anon97242,
The deployment generates an ALTER TABLE ... ADD ... NOT NULL operation that is rejected by Fabric Warehouse. Microsoft documents that Fabric deployment uses DacFx to generate the deployment plan and recommends reviewing the generated T-SQL before deployment. For this limitation, the affected change should be handled separately rather than relying on the generated statement.
Schema Comparison Overview - SQL Server | Microsoft Learn
Deploy Fabric Data Warehouse Using Pipelines - Microsoft Fabric | Microsoft Learn
ALTER TABLE (Transact-SQL) - SQL Server | Microsoft Learn
Thank you.
jubinsoniAdvocate II
Hi anon97242 ,
On your second question, the DEFAULT 0 comes from DacFx itself. When it sees a NOT NULL column being added to a table that might already have rows, its "generate smart defaults" option adds a default so existing rows get a value. In SQL Server that would work fine. Fabric Warehouse just doesn't allow adding NOT NULL columns to an existing table at all, so the generated script fails either way.
For keeping it inside the deployment pipeline, the simplest option is to define the new column as nullable in Dev and enforce the not null rule in your load logic or with a data quality check. Not ideal, but it deploys cleanly every time.
If the constraint really matters, a rebuild is the way to go, run as a script before the deployment.
CREATE TABLE [DM_Schema].[SummaryTable_new] AS SELECT *, CAST(0 AS INT) AS NewColumn FROM [DM_Schema].[SummaryTable]; DROP TABLE [DM_Schema].[SummaryTable]; EXEC sp_rename 'DM_Schema.SummaryTable_new', 'SummaryTable';Once the target already matches Dev, the pipeline sees no difference and skips that change. Just check that column types and nullability on the CTAS result match your Dev definition exactly, otherwise the pipeline will try to "fix" it again.
