Alter Table Add Constraint vs Alter Table Add Check Constraint

Question

I'm experiencing a strange issue in SSMS.

My table design makes heavy use of composite-keys to enforce strict and robust referential integrity across all transitive relations - at a cost of violating BCNF, but in practice it's proving quite handy, especially with Entity Framework's automatic Navigation Properties (related to this question I posted earlier: How can I enforce second-degree relationships without composite keys? ).

Anyway, I'm having issues with adding Foreign Key relationships using SSMS 2014's diagram editor.

Here's a simplified example of my problem:

CREATE TABLE Tenants (
    TenantId bigint IDENTITY(1,1) PRIMARY KEY
)

CREATE TABLE Shops (
    TenantId bigint, -- Is a FOREIGN KEY( Tenants REFERENCES TenantId )
    ShopId   bigint IDENTITY(1,1)

    PRIMARY KEY( TenantId, ShopId )
)

CREATE TABLE Job (
    TenantId bigint, -- Is a FOREIGN KEY( Tenants REFERENCES TenantId )
    ShopId   bigint, -- Is a FOREIGN KEY( Shops REFERENCES TenantId )
    JobId    bigint IDENTITY(1,1)

    PRIMARY KEY( TenantId, ShopId, JobId )
)

I have many tables with columns like these, and using the Diagram Editor to create relationships with referential integrity for composite keys works fine (you simply ctrl+click each composite FK column, then drag on to the primary-key table, and click OK, and that's it.

However with some times, it fails. For example, if I select TenantId and ShopId in Jobs and drag them on to the Shops table, it gives me this error:

The columns in table 'Shops' do not match an existing primary key or UNIQUE constraint.

...even though those two columns are the primary-key of the Shops table!

I got SSMS to generate the SQL for the constraint it was attempting to add, and it gave me this (formatting mine, additional TRANSACTION code removed):

ALTER TABLE
    dbo.Jobs
ADD CONSTRAINT
    FK_Jobs_Shops
FOREIGN KEY
    ( ShopId, TenantId ) REFERENCES dbo.Shops ( ShopId, TenantId )

ON UPDATE
    NO ACTION
ON DELETE
    NO ACTION 

When I ran it directly, SQL Server gave me this error:

Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'dbo.Shops' that match the referencing column list in the foreign key 'FK_Jobs_Shops'.

Msg 1750, Level 16, State 0, Line 1 Could not create constraint or index. See previous errors.

Note that other tables already had a defined foreign-key relationship to the Shops table - so I wondered what was going on. So I told SSMS to Script to CREATE the seemingly working constraints - I then renamed things to make it create the constraint I wanted originally (between Jobs and Shops), and it gave me this different output (formatting mine):

ALTER TABLE
    [dbo].Jobs WITH CHECK
ADD CONSTRAINT
    [FK_Jobs_Shops]
FOREIGN KEY
    ([TenantId], [ShopId]) REFERENCES [dbo].[Shops] ([TenantId], [ShopId])
GO

ALTER TABLE
    [dbo].[Jobs]
CHECK CONSTRAINT
    [FK_Jobs_Shops]
GO

When I ran this it worked!

Note the differences:

• The addition of WITH CHECK
• The lack of ON UPDATE / ON DELETE statements
• The constraint was effected in two statements, not one.

My questions:

• Have I discovered a bug in SQL Server?
• Is there a semantic difference between the two constraint definition syntaxes?
• Or rather, why does one work but not the other?

Answer 1

The only significant difference between the two scripts is the order of the foreign key fields / referenced primary key fields.

The failing script uses (ShopId, TenantId) which is not the order of the primary key definition:

PRIMARY KEY(TenantId, ShopId) 

Couldn't find a official reference but it seems that you have to specify the FK fields in the same order as in the PK definition.