Is foreign key correct with null field? Postgresql

Question

Is foreign key correct with null field? Postgresql

Asked 6 years, 3 months ago

Viewed 503 times

1

I was building a relationship entity model (MER) from a database to a rual association, it is a work for TABD matter. But I came across an atypical situation for me, a 0.n to 0.1 relationship. As you can see in the figure.

It turns out that in this database not always a transaction will have a related person (the transaction may be the cash out with payment of accounts for example) and as the foreign key is in the transaction table this would imply in foreign null keys. My question is whether this is correct? Can foreign keys be null? If not, why?

I searched a few places before asking here but there was no agreement on the answers.

I am not an expert, I will leave it to someone in the area to answer. But as a developer, there is no problem in leaving the foreign key null. You can probably use the field tipo to differentiate when performing the darlings application. If the type is common (for example, to show that it was a transaction performed by a person), then you will know that there is always a valid, non-null id_person. If the guy isn’t common (for example, performed by the system itself), so you know that there is no valid id_person. Just a Developer view. I will follow the answer.

– Andrew Ribeiro

2018/08/14 at 01:06
It is illogical for a foreign key to have null value. Besides the keys can be repeated, the Foreign key if it is connected to a table, has no use as ID.

– White

2018/08/14 at 15:44
This is the problem in my research to resolve this issue. There are always people who say there are no problems (they give clearer and more coherent answers) and people who say there is no logic (but there is usually no explanation for this answer). The question for the second answer is. If it has no logic, what is the logical way to connect two tables in which links are not required on either side?

– Calebe Pereira

2018/08/15 at 02:16
No problem. This indicates that the relationship is optional. If required you would specify NOT NULL on the foreign key. What you have to check is if such a situation makes sense in your fingered model and, it seems, you have already made such a check. Note that what does not make sense is that you have a primary key that can contain NULL, and in this case the DBMS itself does not allow such a construction.

– anonimo

2019/07/13 at 18:23

1 answer

Browser other questions tagged database foreign-key

You are not signed in. Login or sign up in order to post.

by Calebe Pereira • 29 points · Answer 1 · 2019-07-13T18:11:26+00:00

Based on the comments of the authors

Andrew Ribeiro & White

I came to the following conclusion:

It makes no sense even to have a foreign key null, at the same time that yes there can be a foreign key null but it becomes useless because the access to tuples with foreign keys is juxtaposed by the value of this, if it is null there is no way to access it.

The mistake in the relationship I sent is that it makes no sense to have a transaction without it being done by a person, so the relationship should be (1,1) for (0,n).