DACPAC Publish fails when altering colum constraint to NOT NULL
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
We use Azure SQL Database deployment Task Version : 1.171.4 to deploy to Azure SQL db, when we tried to deploy a column constraint we got the following error in the log.
` 2020-10-26T14:30:50.1206693Z *** The column MarketId on table [bdl_DW].[MarketLanguage] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. **To avoid this issue, you must add values to this column for all rows** or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
...
2020-10-26T14:30:52.0466530Z ##[error]lumn LanguageId on table [bdl_DW].[MarketLanguage] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. To avoid this issue, you must add values to this column for all rows or mark it as allowing NULL values, or
2020-10-26T14:30:52.0468615Z ##[error] enable the generation of smart-defaults as a deployment option.
Warning SQL72016: The column MarketId on table [bdl_DW].[MarketLanguage] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. To avoid this issue
2020-10-26T14:30:52.0470791Z ##[error], you must add values to this column for all rows or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 8 Rows were detecte
2020-10-26T14:30:52.0473247Z ##[error]d. The schema update is terminating because data loss might occur.
Error SQL72045: Script execution error. The executed script:
**IF EXISTS (SELECT TOP 1 1
FROM [bdl_DW].[MarketLanguage])
RAISERROR** (N'Rows were detected. The schema upda
2020-10-26T14:30:52.0474749Z ##[error]te is terminating because data loss might occur.', 16, 127)
WITH NOWAIT;
`
The database we deploy to does NOT contain any null values in that column but deployment still fails , I know that it is possible to enable GenerateSmartDefaults but we don't want to to this and from the log it looks like code is designed to raise an error if the table contains rows even tough that there are no rows that are in conflict with the NOT NULL constraint.
I believe this to be a bug as the informational message suggest that having no null values in the column would solve this.
Contributor guide
Research direction
Start by reproducing the Azure SQL deployment with the MarketLanguage table and the MarketId or LanguageId column changed from NULL to NOT NULL, using the logged DACPAC deployment scenario. Compare the generated ALTER script and row-check behavior when existing rows contain no NULL values. Done means deployment succeeds without enabling GenerateSmartDefaults when the constraint is already satisfied.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- azure, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100