SQL72031 when renaming column and removing index on said column
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- SqlPackage or DacFx Version: 170.2.70.1 and 162.5.57.1
- .NET Framework (Windows-only) or .NET Core: dacpac created with .NET Framework (using Build in Visual Studio 2022). Deployment using both sqlpackage.exe and dotnet sqlpackage
- Environment (local platform and source/target platforms): Commandline deploy using sqlpackage from dacpac built with Visual Studio 2022. Target platform is SQL Server 2022.
**Steps to Reproduce:**
1. Create a database project with table dbo.t1:
```
CREATE TABLE [dbo].[t1]
(
[Id] bigint NOT NULL CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED,
[Value1] NVARCHAR(200) NOT NULL,
[CreatedAt] DATETIME2 NOT NULL CONSTRAINT DF_t1_CreatedAt DEFAULT SYSUTCDATETIME()
);
GO
CREATE INDEX IX_t1_Value ON [dbo].[t1]([Value1]);
GO
```
2. Deploy database
3. Use Refactor->Rename to rename Value1 to Value2
4. Remove the index ix_t1_value from the database project.
5. Build and deploy again with DropObjectsNotInSource=false and BlockOnPossibleDataLoss=true
```
dotnet sqlpackage /Action:publish /sourcefile:".\paramtest.dacpac" /tsn:sql-01.prod.contoso.com /tdn:paramtest /p:BlockOnPossibleDataLoss=true /p:DropObjectsNotInSource=false
```
The above gives this error:
```
Verification of the deployment plan failed.
Error SQL72031: This deployment may encounter errors during execution because changes to [dbo].[t1].[Value2] are blocked by [dbo].[t1].[IX_t1_Value]'s dependency in the target database.
```
Same result if I use sqlpackage.exe instead of dotnet sqlpackage.
If I run the same command with DropObjectsNotInSource=true, the deployment works fine. And this makes no sense to me, because indexes are by default dropped when they exist in target but not in source. To me, it looks like validation is done in the wrong order somehow. Validation should take into consideration that the index will be dropped anyway. And there shouldn't be a difference between DropObjectsNotInSource being true or false, since indexes are normally dropped regardless of value for the parameter.
**Did this occur in prior versions? If not - which version(s) did it work in?**
I don't really know. The error is in the versions I have tested. I never had this occur before.
It might seem like an edge case, but it really isn't. The code above is just my repro of the error. When it happened to us in a real project, we changed a foreign key column: We changed the name of the column and we dropped a single-column index on the column because we already had a compound index with said column as leading key.
(DacFx/SqlPackage/SSMS/Azure Data Studio)
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the deployment using the database project table and index from the issue, then run the shown dotnet sqlpackage command with DropObjectsNotInSource=false and true. Trace the deployment-plan validation that reports SQL72031 for the renamed column and removed index; done means both settings handle the rename and index removal without the dependency error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100