Question

I need the definitions

1. Depiction of a referential IC

2.Violation of the ICs

Answer 1

1. Depiction of a referential IC :-

A referential integrity constraint is defined as part of an association between two entity types. The definition for a referential integrity constraint specifies the following information:

• The principal end of the constraint. (An entity type whose entity key is referenced by the dependent end.)

• The entity key of the principal end.

• The dependent end of the constraint. (An entity type that has a property or properties that reference the entity key of the principal end.)

• The referencing property or properties of the dependent end.

Example-

Here, if we delete record number 15 in a primary table, we need to be sure that there’s no foreign key in any related table with the value of 15. We should only be able to delete a primary key if there are no associated records. Otherwise, we would end up with an orphaned record.

So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

2. Violation of the ICs:-

• Integrity constraint violations occur when an insert, update, or delete statement violates a primary key, foreign key, check, or unique constraint or a unique index.
• There are following three possible causes of violation of referential integrity constraint-

Cause-01: Insertion in a referencing relation:-

• It is allowed to insert only those values in the referencing attribute which are already present in the value of the referenced attribute.
• Inserting a value in the referencing attribute which is not present in the value of the referenced attribute violates the referential integrity constraint.

Cause-02: Deletion from a referenced relation:-

• It is not allowed to delete a row from the referenced relation if the referencing attribute uses the value of the referenced attribute of that row.
• Such a deletion violates the referential integrity constraint.

Cause-03: Updation in a referenced relation:-

• It is not allowed to update a row of the referenced relation if the referencing attribute uses the value of the referenced attribute of that row.
• Such an updation violates the referential integrity constraint.