GenerateDeployScript emits invalid `ALTER TABLE ... DROP CONSTRAINT ;` (empty name) when dropping an unnamed default constraint from a script-built model
- 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
Assessment
This issue has not been assessed yet.