if the deletion violates referential integrity

Question

If the deletion violates referential integrity constraint, then three alternatives are available:

• Default option: - refuse the deletion. It is the job of the DBMS to describe to the user why the deletion was rejected.
• Attempt to propagate (or cascade) the deletion by deleting tuples that reference the tuple that is being deleted.
• Vary the value of referencing attribute that origins the violation.

Example:

Let R:

A#	B	C^
A1	B1	C1
A2	B3	C3
A3	B4	C3
A4	B1	C5

Q

C#	D
C1	D1
C3	D2
C5	D3

 

Note:

1) '#' shows the Primary key of a relation.

2) '^' shows the Foreign key of a relation.

(1) Delete a tuple with C# = 'C1' in Q.

Violated constraint: - Referential Integrity constraint

Motive: - Tuples in relation A refer to tuple in Q.

Dealing: - Options available are

1)  Decline the deletion.

2)  DBMS may automatically delete all tuples from relation S and Q with C

# = 'C1'. This is known as cascade detection.

3)  The one more option would result in putting NULL value in R where C1 exist, which is the initial tuple R in the attribute C.