Alter table adding unique constraint with no check SSDT

Question

I have a SSDT project and we publish databases via dacpac/sqlpackage. Now I have a existing table with duplicate records. And I am adding a UNIQUE constraint so that the records should not be duplicated. This is how my table script looks like.

CREATE TABLE [AM].[WorkflowStep]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [StepName] NVARCHAR(200) NOT NULL, 
    [IsAutomated] BIT NOT NULL DEFAULT 0, 
    [TenantId] UNIQUEIDENTIFIER NOT NULL,
    *CONSTRAINT [UNIQUE_STEP] UNIQUE (StepName, IsAutomated, TenantId)*
)

The constraint is newly added. I was expecting the sqlpackage.exe to generate publish script with constraint added with NOCHECK Option and then finally altered with CHECK option. But that is not happening. The script has only an alter table option.

ALTER TABLE [WorkflowStep]
    ADD CONSTRAINT [UNIQUE_STEP] UNIQUE NONCLUSTERED ([StepName] ASC, [IsAutomated] ASC, [TenantId] ASC);

Can someone throw light on why it is so? Is this specific to UNIQUE constraints. I have other constraints in other tables for which the sqlpackage adds constraints with NOCHECK option initially but they are foreign key references.`

Jeroen Mostert Jeroen Mostert · Accepted Answer · 2019-07-24T08:15:26

You can easily test this yourself:

CREATE TABLE #t(ID int); 
INSERT #t(ID) VALUES (1), (1); 
ALTER TABLE #t WITH NOCHECK ADD CONSTRAINT UQ_#t_ID UNIQUE(ID);

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#t...' and the index name 'UQ_#t_ID'. The duplicate key value is (1).

This proves that NOCHECK is ignored. The docs also specify an effect only for FOREIGN KEY and CHECK constraints, though they do not say what the behavior is for other constraints.

There is a good technical reason why it works this way: a UNIQUE constraint must be backed by an index, and the physical structure of a UNIQUE index differs from a non-UNIQUE index. Specifically, a non-UNIQUE index has a uniquifier, an invisible, nullable integer column added to indexed rows to make them unique anyway and allow the engine to identify individual rows. To allow creating a unique constraint WITH NOCHECK and then later applying WITH CHECK would actually require rebuilding the index, at the very least changing the metadata -- this is no better than just creating the index correctly from the start as either UNIQUE or non-UNIQUE, depending on what you need. By contrast, a FOREIGN KEY/CHECK constraint can always be verified later without having to rewrite data.

Alter table adding unique constraint with no check SSDT

1 Answers