Restriction of referential integrity in a child table - Databases: design

Referential Integrity Restriction in a Child Table

The first action that can be controlled by referential integrity operations is the attempt to add a new record to the child table. Suppose that there are data in the tables associated with the entities "Orders and Client (Figure 5.46).

Orders Customer

IDF order

Order number

IDF client IDF client client's name

1

3-1/2012

1 1 Ivanov P. S.

2

3-2/2012

1 2 Kravets RS

5

3-4/2013

2 3 Rodnina A.K.

The

Fig. 5.46. Background for checking constraints

referential integrity

We will assume that the child table, in connection with the presence of a foreign key binding in it, will consider the table associated with the entity "Orders", and accordingly the parent table - "Client". The first action in question involves adding a new entry to the table Orders (Figure 5.47), where the foreign key does not matter or has a value that is not represented by the primary key value of the client table.

Orders

Customer

IDF order

Order number

Client IDF

Client IDF Name of the client

1

3-1/2012

1

1 Ivanov 11. S.

2

3-2/2012

1

2 Kravets RS

5

3-4/2013

2

3 Rodnina A.K.

10

3-152/2014

The

Fig. 5.47. Adding a new order for an unknown customer

This operation, given that the foreign key referring to the client does not matter, or rather, has the null value "NULL", can not be fulfilled, and the link integrity constraint must provide this control, which usually occurs in modern DBMSs. Setting a limit on storing an empty value in the foreign key field will be a sufficient constraint to prevent adding an entry.

When a database user tries to add an entry with a new order assigned to a particular client but that is not contained in the client table, then you must specify the referential integrity constraint on the action to add data to the child table. If the DBMS provides the ability to define a rule for limiting referential integrity, then it is necessary to set the Restrict operation for these actions, which will prohibit adding entries with incorrect primary key values. If you do not do this, an entry will be added that violates the integrity of the data. For example, this violation can lead to the following problems:

- an attempt to get a list of orders indicating which client made each order can lead to the inability to perform this operation, because for one of the order entries there will not be a specified client;

- performing the same operation, but only providing communication with existing customers will lead to the impossibility to gain access to the added order;

- selecting a list of orders made by existing clients in the database will cause the added order to be selected and processed;

- counting the number of orders made by each client will also lead to the impossibility of processing the added order, although a simple operation for counting the total number of orders will show that such an entry exists.

There are other possible problems with this phantom record addition to the child table, so DBMS developers usually strictly restrict the referential integrity of this action.

The second action is to change the value of the foreign key in the child table or to clear it (Figure 5.48). When the value changes to the one that exists in the primary key of the parent table, the operation should succeed, except for individual cases that are controlled by individual trigger operations defined by the database developer based on the domain conditions.

Orders

IDF order

Order number

Client IDF

1

3-1/2012

1

2

3-2/2012

1

5

3-4/2013

4

Fig. 5.48. Changing the value of a foreign key

The operation when the value of the foreign key is cleared, if provided by the rules of the domain of such an option, should not be performed, which can be ensured, as in the previous example, by setting a restriction on the possibility of storing an empty value of NULL for the corresponding attribute.

When the value of a foreign key changes to another, but does not exist among the values ​​of the primary key of the parent table, then there must be a constraint of referential integrity, which is usually provided by standard DBMS mechanisms. However, the installation of the option "No action or None can lead, especially when using the ERWin Data Modeler DL tool, which generates trigger actions independently, to failure in controlling the correspondence between the values ​​of the external and primary keys. This situation requires monitoring and setting the option of the "Restrict" action that prohibits a change if there is no associated record in the parent table.

If you go back to the table for describing referential integrity rules (see Table 5.4), assuming, as mentioned earlier, that the selected DBMS is IBM DB2, then knowing the features of this database and using the IBM InfoSphere Data Architect tool allows you to use the action No action, understanding that the DBMS has an automatic operation that can not be configured by indicating a standardized action, but can only be replaced by a trigger action that must be determined by the developer, which is not supposed to be done under the condition domain.

The third action is to delete the record from the child table (Figure 5.49). This action does not affect the parental

table, and its implementation will not entail any consequences. However, if we assume that only information about customers who made orders is stored in the database, then the problem of the existence of a client without an order arises.

Orders

IDF order

Order number

Client IDF

1

3-1/2012

1

2

3-2/2012

1

§

3 1/2013

%

Rice . 5.49. Deleting an entry in a child table

To solve the problem of the existence of a client who has not made any orders, the developer will need to determine his own trigger, which will check the customer table for the presence of such clients and remove the one that was associated with the order being deleted.

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)