SchemaComparisonResult.PublishChangesToProject can't update check constraint
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- SqlPackage or DacFx Version: 170.4.83.3
- .NET Framework (Windows-only) or .NET Core: .NET 10.0
- Environment (local platform and source/target platforms): Windows 11; source = SQL Server 2019; target = .sqlproj.
SSMS 22.10.0 and VS Code ms-mssql.mssql 1.45.1
## Description
When a constraint is **changed** (not added), and the target SQL project declares that
constraint **inline** inside `CREATE TABLE (...)`, `SchemaComparisonResult.PublishChangesToProject`
writes a complete standalone `ALTER TABLE ... ADD CONSTRAINT ...;` statement over the inline
constraint's span. This reproduces through the DacFx API directly.
**Steps to Reproduce:**
Two single-table projects. The only difference is the constraint's **form** and its value.
`source/dbo/Tables/T1.sql` — constraint standalone, allows 0/1/2 (how a database renders it):
```sql
CREATE TABLE [dbo].[T1] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Type] TINYINT NOT NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
ALTER TABLE [dbo].[T1]
ADD CONSTRAINT [CK_T1_Type] CHECK ([Type]=(2) OR [Type]=(1) OR [Type]=(0));
GO
```
`target/dbo/Tables/T1.sql` — same constraint inline, allows 0/1:
```sql
CREATE TABLE [dbo].[T1] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Type] TINYINT NOT NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [CK_T1_Type] CHECK ([Type]=(1) OR [Type]=(0))
);
```
Both `.sqlproj` files:
```xml
Target
Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider
1033, CI
```
Driver:
```csharp
using Microsoft.SqlServer.Dac;
using Microsoft.SqlServer.Dac.Compare;
const string Dsp = "Microsoft.Data.Tools.Schema.Sql.Sql150DatabaseSchemaProvider";
string dacpac = Path.GetFullPath(args[0]); // source/bin/Debug/Source.dacpac
string sqlproj = Path.GetFullPath(args[1]); // target/Target.sqlproj
string projDir = Path.GetDirectoryName(sqlproj);
string[] scripts = Directory.GetFiles(projDir, "*.sql", SearchOption.AllDirectories);
var result = new SchemaComparison(
new SchemaCompareDacpacEndpoint(dacpac),
new SchemaCompareProjectEndpoint(sqlproj, scripts, Dsp, DacExtractTarget.SchemaObjectType))
.Compare();
var pub = result.PublishChangesToProject(projDir, DacExtractTarget.SchemaObjectType);
Console.WriteLine($"Success={pub.Success}");
Console.WriteLine($"ErrorMessage={pub.ErrorMessage}");
```
Run:
```
cd source && dotnet build # produces Source.dacpac
dotnet run --project ../runner -- source/bin/Debug/Source.dacpac target/Target.sqlproj
```
### Expected
The inline constraint in `target/dbo/Tables/T1.sql` is updated in place:
```sql
CONSTRAINT [CK_T1_Type] CHECK ([Type]=(2) OR [Type]=(1) OR [Type]=(0))
```
### Actual
```
Success=False
ErrorMessage=startIndex ('-1') must be a non-negative value. (Parameter 'startIndex')
Actual value was -1.
```
[DacFx-issue.zip](https://github.com/user-attachments/files/32079105/DacFx-issue.zip)
**Did this occur in prior versions? If not - which version(s) did it work in?**
(DacFx/SqlPackage/SSMS/Azure Data Studio)
Contributor guide
Research direction
Reproduce the failure with source/dbo/Tables/T1.sql and target/dbo/Tables/T1.sql using the runner driver and SchemaComparison.PublishChangesToProject. Start by tracing the inline-constraint path at this entry point; done means the target file is updated in place with the standalone constraint definition and the publish result reports Success=True.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 58/100