0

If I add a foreign key between two tables, am I allowed to add orphan rows afterwards? Also when I'm creating a foreign key between two tables, is there any way to create it ignoring the orphan rows?

My next question is about the efficiency of foreign keys. I had always thought that they created an index between one key in a table and the corresponding key in another table which essentially made it a linear lookup when doing a join.

Is a foreign key much more efficient then simply having an index or are they the same?

Thanks.

Navetz
  • 393
  • 1
  • 2
  • 12

1 Answers1

1

foreign key relationship is often between a foreign key in one table and a primary key in another.

primary key do implicitly create an index.
Not foreign keys. Mostly it's good practice to add index on a foreign key column.

keys are constraints to guarantee data consistency and index can be used improve access performance on the data. So these are different things that you often combine in practices and thus are often confused.

Regarding orphan rows I think the concept of keys is to prevent this. But I'm not completely sure If I understand exactly what you are asking here. I think adding orphan rows is not possible and creating keys if orphan rows exist sounds kind of impossible.

see also other questions on SO dealing with foreign key / index topic.
Here some more good answers related to primary key / key / index

Community
  • 1
  • 1
bw_üezi
  • 4,483
  • 4
  • 23
  • 41