foreign key as nullable. why is it required?

Question

My understanding of foreign key relationships in tables is that a foreign key is a column in a child table and is a primary key in the parent table.we know that primary keys are unique and not null.I have some doubts and require clarification. Is it necessary that when a parent table has data the child should also has to have related data? can child table have nulls(foreign key nulls). why is there a provision for foreign keys to be null.

I have not been able to get proper understanding of all of this.

help me in making me understand these concepts better. thanks in advance.

Answer 1

why is there a provision for foreign keys to be null.

Optional participation conditions. When a child either has a single parent or non, this is one way to model this relationship between the tables.

If the value is null, then the child has no parent.

If the value is not null, then the child has a parent and the value must be an existing parent ID.

If you have such a relationship and do not allow null in the foreign key column, then you can't have a child without a parent.

You could model this with a many-to-many table, which, without any constraints would mean that participation is optional, but to ensure a one-to-one relationship you would need to make each foreign key column unique (as well as NOT NULL). I personally find this option to be overkill (and it can be changed if requirements change).

The choice between the two is one of practicality (how likely is the model to change from a one-to-one to a one-to-many or many-to-many?, for example), though relational purists would go for the many-to-many option.