Question

What would you do if you encountered a database that violated referential integrity? How would you address the problem? When forming your answer be sure to include at least one of the three main types of referential actions (cascade, restrict, set null).

Answer 1

Q What would you do if you encountered a database that violated referential integrity? How would you address the problem? When forming your answer be sure to include at least one of the three main types of referential actions (cascade, restrict, set null).

Ans: Firstly let's take an overview about the referential integrity.The referential integrity is basically a database concept that are used to maintain and build a logical relationships  between the tables to avoid the logical corruption of data.This concept is very important in the RDBMS.The  referential integrity concept is basically made up of the combination of the foreign key and the primary key.The main use of the referential integrity is that it does not allow to add the table which contain the foreign key unless if the reference table contain the corresponding primary key.

So, we see above that the how much referential integrity is important to maintain in the database.The following are the causes of violation of referential integrity in the databases:

• Insertion to a referencing relation:It allow to insert only those value of the referencing attribute that are already present in the value of the referenced attribute.So, inserting the value in the referencing attribute that are not present in the  value of the referenced attribute that will violate the referential integrity constraint.
• Deletion from a referenced relation: This is the another reason for the violation of the referential integrity.When the referencing attribute uses the value of the referenced attribute then it not allowed to delete the row from the referenced relation.This type of deletion violates the the referential integrity constraint.

• Updation in a Referenced Relation: This is not correct way or we can say allow to update a row of the referenced relation when the  referencing attribute uses the value of  referenced attribute of that row.

Handling the Violation-To handle the violation we can perform the following method:

1. This method is basically called the On Update Cascade.In this method the simultaneous updation of those tuples of the referencing relation are done that referencing attribute that uses the referenced attribute value that are being updated.
2. In this method the deleting and aborting the request are involved that  request for the updation of the referenced relation when the value is used by the referencing relation.
3. This is the last method in which the setting of the value are being updated   in the referenced relation to NULL or some other value that  referencing the relation when the the referencing attribute uses that value.

Cascade referential integrity: Cascading referential integrity  constraints is basically a foreign key constraints that tells the SQL Server to perform certain task when the  primary key field are deleted or updated in the primary key-foreign key relationship .In the cascade referential integrity it basically uses the foreign key constraints that tells the SQL sever to perform certain task.To specify the action that are taking places in the referenced table or referencing table ue use the  CASCADE or RESTRICT to  specify the actions.

THANKS , i think this will give you a better intuition about your question. If you have any doubt feel free to ask in the comment section.