Question

Describe how foreign keys impact the ability to add or delete rows from a table or the ability to drop tables from a database schema.

Answer 1

Foreign keys is nothing but the primary key of a table that is used in another table.Let's take two table one is "STUDENT" and other one as "FEE_DETAIL" . Let the primary key of "STUDENT" table is "STUDENT_ID" and the attribute "STUDENT_ID" is also used in the table "FEE_DETAIL" (so that it can store the detail of fees for each student with the help of student id) Now as it is used in table "FEE_DETAIL" so the "STUDENT_ID" is foreign key for the table "FEE_DETAIL".

Now let's add a tuple or row or entry in table "FEE_DETAIL"(which include student_id, and other fee details) But this may create a problem because suppose you added a entry of student with student_id as 12 (let) but it may be possible that there is no student exist with student_id=12(it may there is no entry of student with student_id=12 in table "STUDENT").So we only can add those record in foreign key which is already exists in the table from where it belong.

In same way let's suppose we have deleted an entry of student with student_id= 101 from table "STUDENT" .But it is still exists in "FEE_DETAIL".Then in this case it will become meaningless.

So to drop a table we must first delete all records that is related to that table means first we need to delete all foreign keys records related to that table then only we can drop a table.