Limitations of referential integrity - Databases: design

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.

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."

Table 5.4

Reasoning for referential integrity rules

















Adding a new order should be secured against the customer's 11th client terminal, given the presence of a foreign key that references the client, which is provided by the standard table link control


No action

The parent key of the parent table is not used in relationships with the child table, which does not require the use of a referential integrity constraint



The child entity is not functionally dependent on the parent table, which does not require the definition of a referential integrity





& quot; •

Clients are the determinant in the functional dependency with the order, so adding a new client does not require mandatory operations in the child table



The primary key of the parent table is the basis for the foreign key values ​​of the child table with a unique match, and the order generated for the client can not be redefined



Removing a customer who made earlier orders is not allowed. because it violates the safety of historical data about the orders that are being executed, which requires the establishment of an appropriate restriction


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.

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

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)