Foreign Key Concepts

What is a Foreign Key?

It is a point in one table that links to a Primary Key (PK) of another table. The relationship between these tables is also called a Parent-Child relationship or Foreign Key Relationship. The table with PK is a Parent and the table with Foreign key is a Child. The purpose of FK is to maintain the data Integrity. Data which is not in Parent table cannot be inserted into a Child table, this is how Foreign Key protects the data integrity in a Database.

How do you delete a record from the Parent's table?

There are various ways you can delete records from parent table. I will discuss few simple and common methods

1. Delete corresponding child records first and then delete record from parent table.
2. Use ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table.

This above delete statement made me think, can I truncate records from a parent table? You can definitely truncate or delete the  records from child table. This is a no brainier to any database personnel. The million dollar question is; can I truncate a parent table after truncating Child table? If no, why not?

I was under the assumption we can truncate the parent table but that is not the case. So what is going to block when the child has no record? You can't truncate the Parent table without disabling the Foreign Key Constraints because truncate will violate the F.K. Constraints.

Syntax: To display foreign key constraints

SELECT owner, 
       constraint_name, 
       constraint_type, 
       table_name, 
       r_owner, 
       r_constraint_name 
FROM   all_constraints 
WHERE  constraint_type = 'R' 
       AND r_constraint_name IN (SELECT constraint_name 
                                 FROM   all_constraints 
                                 WHERE  constraint_type IN ( 'P', 'U' ) 
                                 AND table_name = 'YOUR_TABLE_NAME'); ---accepts parent table

Syntax: Displays all the FK Constraints

SELECT * 
FROM   dba_constraints 
WHERE  constraint_type = 'R'; 

Moving or copying data to tables with FK is little complicated then you think. If you are moving data from production to development to tables with F.K. relationships, there are series of steps that should happen in order to avoid the constraints violation.

1. Truncate Child table.
2. Disable F.K. Constraint
3. Truncate parent table.
4. Enable F.K. constraint
5. Load Parent table
6. Load child table
7. You are done!!

Please post your experience or question you may have on this Foreign Key concepts. I would love to hear or answer any question that you may have.