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?

Why do you need to know name differences? As fas as I know it is utilised by the engine. — Alex, Apr 08 '20 at 19:55
If the FK names are different, then the schema is not the same and this should be detected. I found that I copied part of the table definition script from another project and FK names don't make sense in context of new project. When an exception was thrown due to FK violation the message included FK name and it didn't make sense because some of the tables that were part of the name don't exist anymore. I renamed key names in DB project but can't synchronize the change to DB itself via Schema Compare — Joe Schmoe, Apr 08 '20 at 20:02
I have tested in **VS2017** and foreign key names are NOT ignored. I did find this interesting feature request though: https://social.msdn.microsoft.com/Forums/security/en-US/e07c63b6-6a1e-4ffe-adae-8e64a8c017f8/ssdt-schema-compare-feature-request-option-to-ignore-constraint-name-when-comparing-column-default?forum=ssdt . Have a look in the options, perhaps there is an additional option to compare constraint names, or maybe you have constraint comparison disabled. — Alex, Apr 08 '20 at 23:02

score 3 · Answer 1 · answered Apr 09 '20 at 02:13

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.

Visual Studio 2019 Database Schema Compare ignores foreign key names

1 Answers1