Referential Integrity Constraints
When implementing a database, developers are regularly faced with ensuring the correctness of the data presented in the tables, which consists in eliminating the following problems:
- the presence of garbage & quot; in tables, resulting from incorrect or incomplete deletion of records of tables;
- discrepancy between the values of primary and related foreign keys;
- creating phantom records that are not related to the parent information, creating records for which access is limited or allowed, but the information contains incorrect data or voids;
- unable to delete records.
These are only the main problems that can occur if the reference integrity is not defined or defined incorrectly. As discussed earlier, the design of referential integrity rules can be organized at the stage of logical modeling, but more accurate is the formulation of these rules when modeling a physical database model, defining the basic
operations that can be performed on the data when it is modified in the add, change, or delete mode.
referential integrity means a set of restrictive rules that define the actions to be performed on data when data modification events occur, in order to ensure correct and efficient data storage.
Ready to make your order?Get your great paper now
Such operations that are performed when providing referential integrity are:
• Restrict - the action prohibits operations if there are associated entries;
• Cascade - the action determines the sequential execution of the operation on the associated data in the child table;
• Set NULL - the action sets an empty value to a foreign key if it is not possible to associate a record with the parent table by it;
• Set Default - an action similar to the previous option, but not a blank value, but a default value;
• No action - the action does not involve standard referential integrity constraints, but allows the use of trigger operations;
• None - all possible actions, including trigger actions, are prohibited.
Typically, the & quot; None & quot; in databases is rare,
because it is not mandatory in terms of a fundamental restriction on the use of trigger operations or operations to provide referential integrity. Considering the subject area when forming the logical and physical models of the database, developers should well understand the essence of those operations that will be determined by the constraints of referential integrity.
In the example of the e-store domain, there are quite a lot of links between the database tables and they need to define the rules for limiting referential integrity (Table 5.4), which can be made in the form of a table where related tables are specified, dividing them into parent and child, an operation that initiates the restriction of the referential integrity, the selected restrictive action and its justification.
When interacting with tables that are associated with entities "Orders & quot; and "Client", if a new order is added, then the conditions for using a foreign key, since it is set to the property of impossibility of storing an empty value, is supposed to be filled with data. In addition, the condition of having a foreign key in the databases determines an automatic non-configurable value control operation, ensuring that the value of the foreign key is unambiguously correlated with the existing values of the primary key of the parent table. This feature and the absence of the need to perform any operations, other than controlling the value of the foreign key, lead to the decision to set the constraint of the link integrity "None."
Reasoning for referential integrity rules
Ready to make your order?Get your great paper now
When defining a referential integrity constraint between tables associated with the entities "Client" and " and "Orders", but the change in the value of the primary key for the customer is determined by a unique match with the orders that it made out. Attempt to change the value of the client's primary key leads to the need to bring the links into the correct correspondence, which requires saving the interaction of the changed client with his orders. Therefore, no action that changes the value of the primary key is permissible, and because the client's primary key is surrogate and is required only for database operation, you can set the restriction to "Sassaba", providing an automatic change of the foreign key value of all records, associated with the record of the client being changed, to the new value purchased by that client.
Such analytical conclusions need to be performed on each variant of the action on the data and, if necessary, performing additional trigger actions within the "Restrictive action" column. the names of the required trigger operations may be indicated. But the main thing in determining the rules of referential integrity is an accurate and correct understanding of possible problems and their resolution through restrictive operations.
Typically, referential integrity constraint operations are performed before the initiating action occurs (Figure 5.42). According to this logic, after the user starts the command to change the data in the database table, the operation will occur in the temporary system table, to which the triggered referential integrity operation has access. This pre-processing is necessary so that other users do not experience phantom records and they do not see the changes that have not yet been applied to the actual table.
Next, the check of the rules for limiting referential integrity is performed, and observance of these rules results in physical operations in real tables. In the event that the referential integrity conditions are not met, the DBMS is denied the operation of the data and returns the database to its original state.
Fig. 5.42. Algorithm for implementing referential integrity operations
It is important to understand that since the trigger effect of the referential integrity constraint is performed at the level of only the changed and linked tables, all actions that are not relevant to referential integrity control, such as increasing the value of the surrogate attribute key, are executed and fixed in the database data completely fulfilled. Therefore, you need to use very carefully the various operations of limiting referential integrity.
Price Plan & Our Features
- Free Outline
- Free Formatting
- Free Title page
- Free Reference Page
- Free Revisions
- Free Submission by Chapters
- Free Plagiarism Report
- Free Customer Service
- 1 – page abstract
- VIP Support
- Expedited delivery
- Essay outline
How to ...
We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)