Entity diagram with tables that have foreign keys that point to a non-PK column do not show relationships in the diagram

Question

I have two tables parent and child.

If I make a foreign key on child that points to the primary key of parent, and then make an entity diagram, the relationship is shown correctly.

If I make the foreign key point to a different column, the relationship is not shown.

I have tried adding indexes to the column, but it does not have an effect.

The database is sqlite, but I am not sure if that has an effect since its all hidden behind ADO.net.

How do I get the relationship to work correctly?

This is using Visual Studio 2010 in the Entity Framework (.edmx) diagram, having it automatically generate the diagram from the database.

Answer 1

A foreign key has to reference a candidate key in the parent table. The column(s) you are pointing to in the diagram must therefore be a candidate key. That doesn't have to be the "primary" key but it must be a set of columns that is guaranteed unique by the presence of a uniqueness constraint (usually a UNIQUE or PRIMARY KEY constraint).