LECTURE #25|DEALING CONSTRAINT VIOLATION|DOMAIN-PRIMARY KEY-ENTITY INTEGRITY - REFRENTIAL VIOLATION
Operations on relational model can be categorized in to:
(i) Read / Retrieve
(ii) Update/ Write/Modification: There are three basic operations that can change the states of relations in the database:
(a) Insert: Insert is used to insert one or more new tuples in a relation
(b) Delete: Delete is used to delete tuples
(a) Update (or Modify) : Update (or Modify) is used to change the values of some attributes in existing tuples
Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated.
Performing above operations may violate constraints. We learn here how to preserve domain constraints, key constraints, entity integrity constraints, and the referential integrity constraints.
Insert Operation:
The Insert operation provides a list of attribute values for a new tuple t that is to be inserted into a relation R. Insert can violate any of the four types of constraints:
1. Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain or is not of the appropriate data type.
2. Key constraints can be violated if a key value in the new tuple t already exists in another tuple in the relation r(R).
Result: This insertion violates the key constraint because another tuple with the same Ssn value already exists in the EMPLOYEE relation, and so it is rejected.
3. Entity integrity can be violated if any part of the primary key of the new tuple t is NULL.
Result: This insertion violates the entity integrity constraint (NULL for the primary key Ssn), so it is rejected
4. Referential integrity can be violated if the value of any foreign key in t refers to a tuple that does not exist in the referenced relation.
Result: This insertion violates the referential integrity constraint specified on Dno in EMPLOYEE because no corresponding referenced tuple exists in DEPARTMENT with Dnumber = 7
If an insertion violates one or more constraints, the default option is to reject the insertion. In this case, it would be useful if the DBMS could provide a reason to the user as to why the insertion was rejected. Another option is to attempt to correct the reason for rejecting the insertion, but this is typically not used for violations caused by Insert; rather, it is used more often in correcting violations for Delete and Update. In the first operation, the DBMS could ask the user to provide a value for Ssn, and could then accept the insertion if a valid Ssn value is provided. In operation 3, the DBMS could either ask the user to change the value of Dno to some valid value (or set it to NULL), or it could ask the user to insert a DEPARTMENT tuple with Dnumber = 7 and could accept the original insertion only after such an operation was accepted. Notice that in the latter case the insertion violation can cascade back to the EMPLOYEE relation if the user attempts to insert a tuple for department 7 with a value for Mgr_ssn that does not exist in the EMPLOYEE relation.
Delete Operation:
The Delete operation can violate only referential integrity. This occurs if the tuple being deleted is referenced by foreign keys from other tuples in the database. To specify deletion, a condition on the attributes of the relation selects the tuple (or tuples) to be deleted.
Operation:
1.
Delete the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10.
Result: This deletion is acceptable and deletes exactly one tuple.
2.
Delete the EMPLOYEE tuple with Ssn = ‘999887777’.
Result: This deletion is not acceptable, because there are tuples in WORKS_ON that refer to this tuple. Hence, if the tuple in EMPLOYEE is deleted, referential integrity violations will result.
3.
Delete the EMPLOYEE tuple with Ssn = ‘333445555’.
Result: This deletion will result in even worse referential integrity violations, because the tuple involved is referenced by tuples from the EMPLOYEE, DEPARTMENT, WORKS_ON, and DEPENDENT relations.
violation is part of the primary key, it cannot be set to NULL; otherwise, it would violate entity integrity.
Update Operation:
The Update (or Modify) operation is used to change the values of one or more attributes in a tuple (or tuples) of some relation R. It is necessary to specify a condition on the attributes of the relation to select the tuple (or tuples) to be modified.