title: How to get EF6 to honor Unique Constraint (on FK) in Association/Relationship multiplicity?
body: <p><strong>2019 Update / TLDR; switch to Entity Framework <em>Core</em> (or whatever else)</strong></p>
  
  <p>While missing some "Features", EF Core properly honors Alternate Keys (aka Unique Constraints) in addition to Primary Keys and thus does a <em>much</em> better job of honoring Relational Algebra. YMMV otherwise; at least it supports many more SQL schemes correctly.</p>
  
  <p>This support added was in the (very outdated) <a href="https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-1.0" rel="nofollow noreferrer">EF Core 1.0 release</a>.. a bit disappointing that the original EF never had this design(ed!) flaw addressed.</p>
  
  <hr>
  
  <p>This may be related to my <a href="https://stackoverflow.com/questions/27553855/how-to-add-an-ef6-association-to-a-candidate-key-unique-key-which-is-not-the-p">other question</a> - which seems to be that either:</p>
  
  <ol>
  <li><p>Entity Framework is a <em>terrible</em> Relational Algebra mapper<sup>1</sup> or;</p></li>
  <li><p>(which I am hoping for) I am overlooking something with SSDL/CSDL and the EDMX model or EF mappings in general.</p></li>
  </ol>
  
  <p>I have a <em>Schema First</em> model and the schema looks like this:</p>
  
  <pre class="lang-none prettyprint-override"><code>ExternalMaps
  ---
  emap_id - PK
  
  Melds
  ---
  meld_id - PK
  emap_id - &gt;&gt;UNIQUE INDEX&lt;&lt; over not-null column, FK to ExternalMaps.emap_id
  </code></pre>
  
  <p>For verification, these are scripted as the following, which <em>should</em> result in a multiplicity of <code>ExternalMaps:1 &lt;-&gt; 0..1:Melds</code><sup>2</sup>.</p>
  
  <pre><code>ALTER TABLE [dbo].[Melds] WITH CHECK ADD CONSTRAINT [FK_Melds_ExternalMaps]
  FOREIGN KEY([emap_id]) REFERENCES [dbo].[ExternalMaps] ([emap_id])
  
  CREATE UNIQUE NONCLUSTERED INDEX [IX_Melds] ON [dbo].[Melds] ([emap_id] ASC)
  </code></pre>
  
  <p>However, when I use the EDMX designer to update from the database (SQL Server 2012), from scratch, it <em>incorrectly</em> creates the Association / Foreign Key relation as <code>ExternalMap:1 &lt;-&gt; M:Meld</code>.</p>
  
  <p>When I try to change the multiplicity manually for the Meld (via the "Association Set" properties in the designer) side to either <code>1</code> or <code>0..1</code>, I get:</p>
  
  <blockquote>
    <p>Running transformation: Multiplicity is not valid in Role 'Meld' in relationship 'FK_Melds_ExternalMaps'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be <code>*</code>.</p>
  </blockquote>
  
  <p>(As with my other question, this seems to be related to Unique Constraints <em>not</em> being correctly registered/honored as Candidate Keys.)</p>
  
  <p><em>How can I get EF to honor the <code>1 &lt;-&gt; 0..1/1</code> multiplicity</em>, as established by the model?</p>
  
  <hr>
  
  <p><sup>1</sup> While I hope this is not the case, I am having no end to grief when trying to get EF to map onto a perfectly valid RA model: LINQ to SQL (L2S) does <em>not</em> have this problem. Since my other question was not trivially answered for such a popular ORM, I am losing faith in this tooling.</p>
  
  <p><sup>2</sup> It is by design that the FK is not the other way: "Though shalt not have nullable foreign keys." - It is also <em>not</em> the case that it's a "shared" PK as this <a href="https://stackoverflow.com/a/1761403/2864740">answer from 2009</a> suggests as a fix.</p>
  
  <p>I am using EF 6.1.1, VS 2013 Ultimate, and am <em>not</em> going to use any "OO subtype features" - if that changes anything.</p>
  
  <hr>
  
  <p>EDIT <em>sigh</em>:</p>
  
  <p><a href="https://social.msdn.microsoft.com/Forums/en-US/91eb2709-f478-4884-8e93-69031f97d2f2/multiplicity-is-not-valid-because-the-dependent-role-properties-are-not-the-key-properties?forum=adodotnetentityframework" rel="nofollow noreferrer">Multiplicity is not valid because the Dependent Role properties are not the key properties?</a> (from 2011) - is this <em>still</em> the case for the EF "Microsoft-endorsed Enterprise-ready" ORM in <del>2014</del> 2015?</p>
  
  <p>At this rate the next time someone asks why EF wasn't used I'll have a large set of reasons other than "LINQ to SQL works just fine" ..</p>