microsoft / microsoft/DacFx

GenerateDeployScript emits invalid `ALTER TABLE ... DROP CONSTRAINT ;` (empty name) when dropping an unnamed default constraint from a script-built model

Open
#807 0 comments 0 reactions 1 assignee Claimed by @aasimkhan30 View on GitHub
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

# Bug: GenerateDeployScript emits invalid `ALTER TABLE ... DROP CONSTRAINT ;` (empty name) when dropping an unnamed default constraint from a script-built model

### SqlPackage or DacFx Version
`Microsoft.SqlServer.DacFx` — reproduced on **170.1.61, 170.3.93, 170.4.83 (latest stable), and 170.5.29-preview (latest preview)**. Assembly version 170.0.0.0.

### .NET Framework (Windows-only) or .NET Core
.NET 8.0 (.NET Core).

### Environment
- Local platform: Windows 11 x64
- Source/target platform: SQL Server 2022 (TSql160). Target generated against an in-memory empty model; no live target required to reproduce.

---

## Summary

When a `TSqlModel` is built from CREATE TABLE **script text** (via `TSqlModel.AddObjects`) that contains an **unnamed** default constraint, and a deploy script is generated in the **drop** direction (the table exists in the source being removed, e.g. with `DropObjectsNotInSource = true`), DacFx emits a syntactically invalid statement with an empty constraint name:

```sql
PRINT N'Dropping Default Constraint unnamed constraint on [dbo].[customers]...';
GO
ALTER TABLE [dbo].[customers] DROP CONSTRAINT ; -- <-- empty name, invalid T-SQL
GO
```

DacFx correctly *detects* that the constraint is unnamed (it prints `unnamed constraint`), but then produces a `DROP CONSTRAINT` with no name, which fails to parse/execute (`Msg 102 / incorrect syntax near ';'`).

Notably, DacFx handles the same unnamed default constraint **correctly in the CREATE direction** — it emits `ALTER TABLE ... ADD DEFAULT (...) FOR [col]` (no name, server auto-names it). The drop path lacks the equivalent handling (it does not resolve the name at runtime from `sys.default_constraints`).

## Steps to Reproduce

Minimal, self-contained console app (no live database needed):

```csharp
using Microsoft.SqlServer.Dac;
using Microsoft.SqlServer.Dac.Model;

const string create = @"
CREATE TABLE [dbo].[customers] (
[customer_id] INT IDENTITY (1,1) NOT NULL,
[email] NVARCHAR(100) NOT NULL,
[created_at] DATETIME DEFAULT (getdate()) NULL, -- UNNAMED default constraint
[updated_at] DATETIME DEFAULT (getdate()) NULL, -- UNNAMED default constraint
PRIMARY KEY CLUSTERED ([customer_id] ASC),
UNIQUE NONCLUSTERED ([email] ASC));";

static DacPackage FromScript(string? s)
{
var ms = new MemoryStream();
using (var m = new TSqlModel(SqlServerVersion.Sql160, new TSqlModelOptions()))
{
if (!string.IsNullOrWhiteSpace(s)) m.AddObjects(s);
DacPackageExtensions.BuildPackage(ms, m, new PackageMetadata { Name = "Pkg", Version = "1.0" });
}
ms.Position = 0;
return DacPackage.Load(ms, DacSchemaModelStorageType.Memory);
}

var source = FromScript(create); // has the table (unnamed default constraints)
var target = FromScript(null); // empty -> everything must be dropped

var opt = new DacDeployOptions { DropObjectsNotInSource = true, BlockOnPossibleDataLoss = false };
var script = DacServices.GenerateDeployScript(target, source, "Tgt", opt);
Console.WriteLine(script);
```

1. Build a source model from the CREATE TABLE script via `AddObjects` (the default constraints are unnamed — valid T-SQL).
2. Build an empty target model.
3. `GenerateDeployScript(target=empty, source=table)` with `DropObjectsNotInSource = true`.
4. Inspect the generated script.

### Expected
A valid statement that drops each unnamed default constraint — e.g. resolving the actual constraint name at runtime from the catalog, as is done for other unnamed-constraint operations. The generated script must be parseable/executable T-SQL.

### Actual
```sql
ALTER TABLE [dbo].[customers] DROP CONSTRAINT ;
```
Empty constraint name → invalid T-SQL. One such line per unnamed default constraint.

## Prior Version Behavior
Same behavior in DacFx 170.1.61, 170.3.93, 170.4.83, and 170.5.29-preview — not a regression; appears to be long-standing.

## Additional context

- **Trigger is specific to script-built models.** If the source model is instead obtained via `DacServices.Extract` from a live database, the default constraints carry the server-assigned names (e.g. `DF__customers__creat__38996AB5`), so the drop statement is non-empty. The empty name only occurs when the dropped side comes from `AddObjects` script text with unnamed defaults.
- **Workaround:** explicitly naming the default constraints in the source (`CONSTRAINT [DF_...] DEFAULT (...)`) makes the drop statement valid. But valid supported input (unnamed defaults) should not produce invalid output.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.