Restriction of referential integrity in the parent table - Databases: design

Referential Integrity Restriction in the Parent Table

Suppose that, but the tables associated with the entities "Orders and "Order Items", there are some previously generated information that allow you to see possible actions and possible problems that arise when adding, deleting, and changing data in the parent table "Orders (Figure 5.43).

Fig. 5.43. Source data for checking referential integrity constraints

The

The first action being considered is the addition of a new order. Since orders do not determine the goods that should be in them, this action will not entail any problematic situations with regard to the goods that will later be in it.

But, suppose that the electronic store automatically creates a certain gift item of the goods when creating a new order. Then it turns out that there can not exist orders without goods, which means that a new record must also be added to the child table. This action is not provided by standard mechanisms for limiting referential integrity and refers to operations defined in the subject area. Therefore, in order to implement this working condition with orders, the developer must independently determine the trigger action, which will add the desired item to the order.

The second action performed on the parent table is to change the value of the primary key (Figure 5.44). In principle, such operations are often prohibited in the DBMS, and any attempt to change the value of the primary key encounters its resistance, which consists in blocking the action. However, often this operation is permissible and its execution can lead to problems in the child table.

Suppose you changed the value of the primary order key from 2 on 4 & quot ;. Given that in the child table for the order with the value of the key 2 there is a commodity position, an uncontrolled change will result in the loss of this item position in the order, which is unacceptable.

Order products Orders

IDF order

Goods IDF

Number of IDF of the order Number of the order

1

1

5 1 3-1/2012

1

5

10 4 3-2/2012

2 & lt; 3 & gt;

3

3 5 3-4/2013

The

Fig. 5.44. Changing the value of the primary key

parent table

Resolution of this situation can be performed in different ways:

the first, the most correct one, is the indication of the "Cascade" action, since then the value of the foreign key in the child table will automatically be changed to the correct value for the associated records, and there will be no integrity violation;

- the second option is to assign an empty value of NULL through the "Set NULL" operation, if it is allowed in the DBMS, which, of course, will lead to the loss of the item's position in the order, but processing items of the orders with an empty value in the foreign key will allow them to access and resolve the resulting integrity violations in the manual mode by the user;

- the third option is to use the "Set default" action setting the default value to the foreign key, which is unacceptable for this example, but it is quite possible for links between other tables.

Thus, for the example in question, there are two acceptable options for restricting referential integrity: to prevent changing the values ​​of the primary key by the "Restrict" or allow a change, and then automatically change the value in the foreign key of the child table through the action "Cascade".

The third action is to delete the record in the parent table, which in this example means deleting the order (Figure 5.45). In DBMS, this action does not have a unique solution to the problem, except if there are no associated records in the child table. In this situation, when the order does not contain the ordered goods, deleting an order will not cause a breach of integrity and the action will not prevent anything.

Order products Orders

IDF order

Goods IDF

Number of IDF of the order Number of the order

1

1

5 1 3-1/2012

1

5

10 e 3 2/2012

2 & lt; 3 & gt;

3

3 5 3-4/2013

Fig. 5.45. Deleting an entry in the parent table

The

If we assume, as in the example of the data, that the order to be removed contains the ordered goods, then its removal will lead to a breach of integrity, which consists in preserving the ordered goods assigned to a non-existent order. The ego is unacceptable and the actions of the link integrity constraint that can be represented by the following options must be allowed:

• Restrict - to forbid deletion of a parent table entry if there are associated records in the child table, which is acceptable for the example, but it is illogical, assuming that order deletion is due to the need to exclude it in further processing, but on condition of saving all orders that make customers, this action will be completely justified;

• Cascade is the most commonly used action that involves sequential deletion of related records in a child table, thus clearing all the products that were assigned to the specified order for the example in question;

• Set NULL - an action that can allow to store the positions in the child table for further processing when using the procedures for processing null values, without losing data integrity, but for the example in question it is unacceptable, since it does not make sense to store the ordered goods if they are not fixed for a specific order;

• Set default is also a valid action similar to the previous one, but sets the default value in the foreign key field by the records of the child table associated with the deleted record of the parent table, which is also unacceptable for the example in question.

Therefore, given that the goods can not be stored in the database table, "Order Items" and " without specifying the order for which they are assigned, valid variants can be Restrict and Cascade & quot ;. When considering the possibility of deleting an order together with an order that, but the domain is acceptable, the option of the action "Cascade" is the most correct and correct.

thematic pictures

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)