[Bug]: A disabled unique index is used as the primary key of a table without one #3918

What happened?

When a SQL Server table has no primary key and its entity doesn't configure source.key-fields, DAB uses the key that FillSchema infers. That key is made of the columns SQL Server reports as key columns (CommandBehavior.KeyInfo). For a table without a primary key, SQL Server reports the columns of a unique index, even when that index is disabled.

A disabled index doesn't enforce uniqueness, so DAB can treat columns that hold duplicate values as the primary key. Requests by key then read or change the wrong number of rows.

Steps to reproduce

CREATE TABLE dbo.Product (code int NOT NULL, name nvarchar(50) NOT NULL); -- no primary key
CREATE UNIQUE INDEX ux_code ON dbo.Product(code);
ALTER INDEX ux_code ON dbo.Product DISABLE;
INSERT dbo.Product VALUES (1, N'Widget'), (1, N'Gadget'), (2, N'Gizmo');

dab init --database-type mssql --connection-string "<connection string>" --host-mode development
dab add Product --source dbo.Product --source.type table --permissions "anonymous:*"
dab start

DAB starts and logs Primary key column name: code. Then:

Expected behavior

DAB shouldn't infer a key from an index that doesn't enforce uniqueness. If a table has no primary key and no enabled unique index, startup should fail with Primary key not configured on the given database object, so source.key-fields is required. That's what already happens for a table without any key.

Notes

• Checked on SQL Server 2025 (LocalDB), using tables with no primary key and one unique index each:

  Unique index	Reported as key (IsKey)	FillSchema primary key
  Regular	code	code
  Hypothetical	none	none
  Filtered	none	none
  Disabled	code	code

• This isn't new: every table goes through FillSchema. It came up while reviewing Skip unsupported column data types during schema discovery #3802, whose fallback for tables FillSchema can't read infers keys the same way.

• A check at startup can't catch an index that is disabled after DAB starts, so a warning or documentation may be part of the fix.

• Workaround: rebuild the index (ALTER INDEX ux_code ON dbo.Product REBUILD), add a primary key, or configure source.key-fields with columns that are unique.

Version

Reproduced on a local 2.1.0 build. The table above doesn't reach #3802's changes, so this is main's behavior.

What database are you using?

Azure SQL / SQL Server