MySQL Forums
Forum List  »  MySQL Workbench

Foreign key: Mandatory vs non-Mandatory; and identifying vs non-identifying
Posted by: Shirley Lee
Date: September 17, 2013 01:16AM

Can some one explain the differences?

I may be confused by some other answers I searched online.

My understanding, or at least I thought:
that identifying is if the foreign key in the child table is part of the primary key (meaning there is more that one field making up the primary key to identify a child record. So the primary key and identifying foreign key are both needed to identify a child record. Because of that, one usually do not want to delete a parent record that has the key if the child exists. So the relationship from parent to child, implicitly, can never be 0 to 0-N. It can only be 1 to 0-N. Right?

But then for Mandatory checkbox on referencing(child) table and referenced(parent) table, does that mean if mandatory checkbox on referenced(parent) table is checked, then it can be 0 (inteadt of 1) to 0-N relationship? Which means a child record can exist without the parent, which also means the foreign key pointing to the parent table can be null on child table?

And I read that the mandatory checkbox checked on referencing (child table) has no meaning to it because the child is always 0-N so it can be no record or N record, unless the relationship is a 1 to 1. Am I right?

Or can anyone help to elaborate all these?

I also want to know what if one wants to have 0-1 (on parent) to 0-N (on child) relationship. How should one set the mandatory checkboxes and the identifying filed? My guess is that it is a non-identifying foreign key and have no check on the Mandatory checkbox on referenced table. And it does not matter if the Mandatory field on referencing(child) table is checked or not. Please help let me know if I am right or wrong and why it is wrong. Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Foreign key: Mandatory vs non-Mandatory; and identifying vs non-identifying
10012
September 17, 2013 01:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.