title: Missing FK Relationship in Entity Framework Model
body: <p>I had a lot of trouble implementing the technique described in an <a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2011/10/17/trusted-foreign-keys-allow-orphans-reject-valid-child-rows.aspx" rel="nofollow">Alexander Kuznetsov article</a>. Basically, the article describes a way to create a FK between one table and <em>alternate</em> tables, and still maintain full constraints on those relationship.</p>
  
  <p>Here's part of Alexander's code:</p>
  
  <pre><code>CREATE TABLE dbo.Vehicles(
      ID INT NOT NULL, 
      [Type] VARCHAR(5) NOT NULL,
      CONSTRAINT Vehicles_PK PRIMARY KEY(ID),
      CONSTRAINT Vehicles_UNQ_ID_Type UNIQUE(ID, [Type]),
      CONSTRAINT Vehicles_CHK_ValidTypes CHECK([Type] IN ('Car', 'Truck'))
  )
  
  CREATE TABLE dbo.Cars(ID INT NOT NULL,
      [Type] AS CAST('Car' AS VARCHAR(5)) PERSISTED,
      OtherData VARCHAR(10) NULL,
      CONSTRAINT Cars_PK PRIMARY KEY(ID),
      CONSTRAINT Cars_FK_Vehicles FOREIGN KEY(ID, [Type])
      REFERENCES dbo.Vehicles(ID, [Type])
  )
  </code></pre>
  
  <p>I finally got it working after errors and confirmed bugs. But when I generate my EF models from the new schema, it is missing a relationship between two of my tables.</p>
  
  <p>The problem is that, in order to have a FK on two columns, there <em>must</em> be an index or unique constraint on <em>both</em> those columns. However, in my case, I also have another table with a FK to a single column in the base table (Vehicles, in Alexander's code).</p>
  
  <p>Since you cannot have more than one PK in a table, this means I cannot have a FK to a PK on both sides. The PK can be for one or two columns, and the other FK will need to reference the non-PK unique constraint.</p>
  
  <p>Unfortunately, Entity Framework will only create relationships for you when there is a FK to a PK. That's the problem. Can someone who understand DB design better than I spot any other alternatives here?</p>
  
  <p><em>Note: I realize some will see the obvious fix as simply modifying the model to manually add the additional relationship. Unfortunately, we are using a database project and are constantly using automated systems to regenerate the project and model from an updated database. So manual steps are really not practical.</em></p>