3

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.

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

A nullable FK constraint models a (Zero or One)-to-Many relationship, whereas a non-nullable FK constrains models a One-to-Many relationship. Modelling a (Zero or One)-to-Many relationship as a many-to-many relationship is an overkill.
@Alex - I absolutely agree. But the purists ("No NULLs in a DB") will not.
2

A nullable foreign key in essence means the relationship is optional.

Assuming that the FK constraint is being checked, then if the Foreign Key is not null, its value must exist in the referenced table.

However, if the value of the foreign key is null, it means that the relationship doesn't exist. Typically, you would LEFT/RIGHT OUTER JOIN in this case to prevent nulls from being filtered out in the JOIN.

Optional relationships are useful when a relationship which is state-dependent. E.g. A Customer will only be assigned to a Branch IF the customer is approved as a client, until then, the customer's BranchId will be NULL, etc.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.