CODEWITHSUNDEEP
mysqlrelational-database
bilalktk
bilalktk

Reputation: 11

Foreign Key - Child Parent Relationship

I am newbie to SQL. I am having difficulty in understanding child parent relationships. In the book "Database Systems by Thomas Conally" it is mentioned that "if the forign key in child table is null, the participation of child table is full in a relationship". How can the participation of child table be full when foreign key is null? Please can anyone explain this concept. Thanks!

Upvotes: 1

Views: 2779

Answers (1)

Aby Sheffer
Aby Sheffer

Reputation: 513

First, I am going to assume you are familiar with primary keys, foreign keys, parent table and child table, yes?

So, you have a parent_table: PK IDnumber, Name, Location, PhoneNumber.

And a child_table: FK parent_table.IDnumber, PK LocNumber, OfficeLocation, OfficeAddress, OfficeCity.

You have some general types of relationships: one-one, one-many, many-many (which are for tables as well as fields.) And specific relationships: deny, restrict, cascade, nullify, set default. General relationships are basically how many records in parent_table are connected/related to how many records in child_table or fields related to other fields in the same table. Specific relationships say what happens to the related records in the child_table when we add/modify/delete the parent_table record with the same key (IDnumber).

With that understanding the "full participation of the child table" is because it is mandatory that the child table participate in actions that happen in the parent. This is usually accomplished by setting the foreign key field to NOT NULL/minimum=1. So actions in the parent_table on a record will impact all child_table records with a matching foreign key.

For example, if you delete the parent table record with an IDnumber of 123, what action do you want to happen in the child table for all records with the foreign key of IDnumber=123? Delete both (or Cascade), keep but mark as inactive (Deny), do not allow the parent table deletion if child table records with matching foreign key exist (Restrict)? Using the Restrict relationship is usually best practice.

There's more rules and guidelines for relationships, but I hope this answered your question.

Upvotes: 4

Related Questions