Database table with two FK to the same table

Question

I know that is not "healthy" to have two FK to the same table but this is my scenario: first person run the test and another person add it int the DB. TestResult is linked to person that created the result and person who downloaded in DB. What is your solution for this scenario? it is ok to have two Fk to userInfo?

when I want to generate second FK with Entity framework: "Unable to determine a composite foreign key ordering for foreign key on type DataAccessLayer.Entity.TestResult. When using the ForeignKey data annotation on composite foreign key properties ensure order is specified by using the Column data annotation or the fluent API."

Two foreign keys to the same table is quite common and not at all "unhealthy". It is easy to do in SQL. — Gordon Linoff, Jul 14 '20 at 15:16
Can this help you: https://stackoverflow.com/questions/40090288/unable-to-determine-a-composite-foreign-key-ordering-for-foreign-key ? — mrNone, Jul 14 '20 at 15:32
What is your 1 specific researched non-dupcliate question? PS If asking about an error message, what did you do in what situation? PS What does 'healthy' in quotes mean? What does 'is linked to' mean? How is your question not answered by the design method reference you are following? What is it & where are you stuck? Don't ask us to rewrite it. [ask] PS Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, Jul 15 '20 at 00:44

score 1 · Answer 1 · answered Jul 14 '20 at 15:39

It's perfeclty normal to have multiple foreign keys between two tables. That depends on the business definition of the relationship between the tables.

Why did you think it was not normal?

Anyway, as an example, you can do it as shown below:

create table UserInfo (
  id int primary key not null,
  name varchar(20),
  password varchar(20),
  role int
);

create table TestResult (
  id int primary key not null,
  added_by int references UserInfo (id),
  created_by int references UserInfo (id)
);

Database table with two FK to the same table

1 Answers1