Visual Studio 2019 Database Schema Compare ignores foreign key names

Question

This is all related to SQL Server.

Visual Studio 2019 Database Schema Compare ignores foreign key names (definition of the keys is the same) when comparing schema between Database Project (source) and actual database (destination). It is not just the casing, foreign key names are completely different yet no differences detected.

I went through all Schema Compare options available and didn't find anything to not ignore differences in foreign key names.

Is that just the way it is? Or did I miss some options?

Answer 1

Several last versions of SSDT ignore names of system-named constraints, and AFAIK it's not configurable. Suppose your database project has a table definition that looks like this:

CREATE TABLE [dbo].[Lookups] (
    [Id]         INT          IDENTITY (1, 1) NOT NULL,
    [LookupType] VARCHAR (50) NOT NULL,
    [Value]      [sysname]    NOT NULL,
    [IsActive]   BIT          DEFAULT ((1)) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UQ_Lookups_NK] UNIQUE NONCLUSTERED ([LookupType] ASC, [Value] ASC)
);

As you can see, this table has 2 unnamed constraints (PK and a default on the IsActive column), and a unique key with its name specified explicitly. Now, if you look into metadata, for keys it would be

select c.name, c.type_desc, c.is_system_named
from sys.key_constraints c
where c.parent_object_id = object_id('dbo.Lookups');

they all have names of course, but some will be automatically named by SQL Server during table / constraint creation, such as:

name                             type_desc               is_system_named
-------------------------------- ----------------------- ---------------
PK__Lookups__3214EC076C07A41D    PRIMARY_KEY_CONSTRAINT  1
UQ_Lookups_NK                    UNIQUE_CONSTRAINT       0

-- This row actually comes from sys.default_constraints
DF__Lookups__IsActiv__24927208   DEFAULT_CONSTRAINT      1

Note the is_system_named column, it tells SSDT which constraints' names should be ignored by Schema Compare. If you set explicit names for either PK or the default, these differences will be highlighted during comparison. If, however, a constraint is unnamed in the project and has is_system_named = 1 in the database, its auto-generated name will be ignored.