In which table is the foreign key in a ratio 1 to 1?

Question

In an SQL relationship of two tables 1 to 1, there is some rule that defines which table the foreign key will be in?

Answer 1

The question is good and made me think of something that we don’t always realize.

We learn (I think) that N is the countermastery and that is where the foreign key is. I don’t have such a formal study of relational algebra, and I may be talking some nonsense, but normal relationship use prevents the domain side, 1, after all you can have several. I will discard some database implementation that allows several foreign keys on the same line (this can even be simulated over the SGDB). And we know that N:M may have foreign keys on both sides. That is, we can only not use foreign key if it has to be repeated on the same line. I think this is the rule (note that I’m talking about a rule, something that can’t be broken, I’m not talking about good practice that depends on context).

That’s why there are cases where you need to create a mooring table between two tables so that the key behaves like 1:N and M:1 on the other side.

If the rule isn’t about that then it’s probably bad, but I doubt because it was defined at the time that the definitions of computing were made in a scientific way, not like nowadays when someone writes some zucchini on a blog, there’s no proof at all, and everyone goes around repeating it like an axiom. Then they create crazy terms like "Nosql", "responsive", "serverless" and so many others :) Unless I’ve interpreted something wrong and didn’t notice.

So in 1:1 we can have key on both sides, because it is guaranteed that there is no repetition of this key on the same line.

An example is a pessoa which has a role of cliente, therefore a relationship 1:1. If you want starting from the pessoa get the data from cliente puts a foreign key there and departing from the cliente, wants to know who is the pessoa Behind him he puts a foreign key there. It can be accessed from any side, either from the logical point of view in which the foreign key goes, but it can be different for reasons of optimization. The right choice can prevent the creation of a secondary index, example: if part of the pessoa, just have the foreign key to the cliente and in the cliente only has the primary key to find it. If you have nothing in the pessoa the only way to find out quickly who the role of cliente that pessoa is accessing the foreign key that indicates who is the pessoa there. It is either slow or has a secondary index, unless the primary key is equal to the key of the pessoa, which makes you even need a foreign key, and who knows 1:1 is wrong.

But if you want to know on which side to use in a concrete example just by looking at this concrete case, being able to use on both sides does not mean that you should, and choosing the side depends on the need. Without the concrete case can only answer from the point of view of algebra that allows on both sides.

Understand more about in What is the advantage of a 1:1 relationship?.

Answer 2

Summarizing: The foreign key will always be in the table that will fetch the other or the table that contains some field identifying or referring another table.