Alter Table Add Constraint vs Alter Table Add Check Constraint
<p>I'm experiencing a strange issue in SSMS.</p>

<p>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: <a href="https://stackoverflow.com/questions/37743526/how-can-i-enforce-second-degree-relationships-without-composite-keys">How can I enforce second-degree relationships without composite keys?</a> ).</p>

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

<p>Here's a simplified example of my problem:</p>

<pre><code>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 )
)
</code></pre>

<p>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.</p>

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

<blockquote>
  <p>The columns in table 'Shops' do not match an existing primary key or UNIQUE constraint.</p>
</blockquote>

<p>...even though those two columns <strong>are</strong> the primary-key of the <code>Shops</code> table!</p>

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

<pre><code>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 
</code></pre>

<p>When I ran it directly, SQL Server gave me this error:</p>

<blockquote>
  <p>Msg 1776, Level 16, State 0, Line 1
  There are no primary or candidate keys in the referenced table '<code>dbo.Shops</code>' that match the referencing column list in the foreign key '<code>FK_Jobs_Shops</code>'.  </p>
  
  <p>Msg 1750, Level 16, State 0, Line 1
  Could not create constraint or index. See previous errors.</p>
</blockquote>

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

<pre><code>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
</code></pre>

<p>When I ran this it worked!</p>

<p>Note the differences:</p>

<ul>
<li>The addition of <code>WITH CHECK</code></li>
<li>The lack of <code>ON UPDATE</code> / <code>ON DELETE</code> statements</li>
<li>The constraint was effected in two statements, not one.</li>
</ul>

<p>My questions:</p>

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