JPH0347539B2 - - Google Patents

Description

[Detailed description of the invention]

[Technical Field of the Invention] When a command that involves updating the unique keys of multiple records is input in a database system, the present invention performs the update with a simple unique check for each corresponding unique key, and updates the corresponding unique keys. This invention relates to a unique key update processing method in which a unique check is also completed when all key updates are completed. [Prior Art and Problems] FIG. 1 shows an overview of a relational database system to which the present invention can be applied.
Reference numeral 2 indicates an execution control unit, 3 an access processing unit, and 4 a relational database. In the relational database system, the relational database 4 can be searched and updated collectively with a single command. The database management unit 1 divides the passed collective command into non-collective procedures and passes them to the execution control unit 2. The execution control section 2 controls one record at a time, and searches or updates the relational database 4 via the access processing section 3. A relational database stores data in tabular format. The rows of the table are called tuples, and each row is considered to be one record. A record has multiple fields, and in a relational database, at least one of the multiple fields is a unique key field. What is a unique key field?
A unique key field is a field in which two or more records are prohibited from having the same value at the same time. Now, assume that the unique key fields of three records constitute a set of unique keys {10, 20, 30}. When each element of this set {10, 20, 30} is updated by +10, it becomes {20, 30, 40}. In this example 2
Since no two elements have the same value at the same time, each record in this database retains its uniqueness even after updating. If you try to implement the above update with a program, 1: {10, 20, 30} 2: {20, 20, 30} 3: {20, 30, 30} 4: {20, 30, 40} You have to go through a series of stages. Therefore, in order to implement this straightforwardly, it is necessary to abandon the requirement to preserve uniqueness for the intermediate results of the second stage and the intermediate results of the third stage, and apply it for the first time in the fourth stage (final stage). be. If uniqueness could be checked for each updated record, it would be completed with a 1:n check, so the cost would generally be low, but if you want to check the final result, you would have to check all combinations of records. A situation arises in which a check has to be made, resulting in extremely high costs. In the prior art, such a costly final batch check method has been adopted. [Object of the Invention] Based on the above considerations, the object of the present invention is to provide a unique key update processing method that allows a unique key update process to be performed quickly by attaching a uniqueness check to the processing of each record in a record update in a database system. It is about providing. [Structure of the Invention] In updating a unique key, the present invention updates a record containing one key using a cascade process of two steps: deleting the record containing the old key and inserting the record containing the new key. When a record containing the old key is deleted, one equivalence key, so-called double key, may be eliminated, and on the other hand, when a record containing the new key is inserted, one equivalence key may be generated. When deleting records containing the old key and inserting records containing the new key as described above one by one for multiple keys, if uniqueness is preserved in the state of the last key, the equivalence keys will be deleted. Focusing on the fact that occurrence and resolution tend to cancel each other out,
Unique check processing can be easily performed in parallel with update processing by detecting the cancellation and generation of individual equivalence keys that occur during the key update process, and cumulatively counting them in correspondence with -1 and +1, respectively. It is something to do. Based on this, the configuration of the present invention is such that in a database processing system equipped with a function of updating a plurality of records including a unique key with a single command, when the update of a unique key is instructed by the above command, a first means for deleting a record containing one old key to be updated from a set of records and inserting a record containing a new key with the key updated; a second means for counting duplicate keys; When a record containing one old key is deleted from the set of records having the above unique key, check whether there is a record containing the same value as the old key in the set of records having the unique key that remains. and a third means for subtracting 1 from the count value of the second means only when the presence is detected, and when a record containing the updated new key is inserted into the set of records having the unique key, Detects whether there is a record containing a key with the same value as the new key in a set of records with a unique key, and only if it is detected that there is one other record containing the same value. , and a fourth means for adding 1 to the count value of the first means, and continuously repeats the first to fourth means for all the old keys to be updated, and when the operation ends, the above-mentioned The present invention is characterized in that the validity of the unique key update is determined based on the fact that the count value of the second means is 0. [Examples of the Invention] The details of the present invention will be described below with reference to Examples. FIG. 2 shows the configuration of a system according to one embodiment of the present invention. In the figure, 1 is a database management section, 2 is an execution control section, 3 is an access processing section, 4 is a relational database, 5 is a command analysis block,
6 is an update processing block, 7 is a unique check processing block, and 8 is a counter (CTR). FIG. 3 shows the overall procedure of record update processing in the update processing block 6, and FIG. 4 particularly shows the detailed procedure of the unique key update and unique check processing portions. In the relational database 4, γ=
A set R={γ} of (f ₁・ f ₂・ f ₃ ) is stored,
Suppose f ₂ is a unique key. Now, when the update processing block 6 of the database management section 1 receives a command from the command analysis block 5 to update f ₂ to f ₂ +10 for a subset of f ₁ > 10, it will do the following:
A procedure as shown in FIG. 3 is generated and passed to the execution control section 2, which instructs the execution control section 2 to update the relational database 4 via the access processing section 3. The procedure shown in Figure 3 can be explained as follows. Set up R. Read record γ. Performs processing when no end is detected. Check whether the condition f ₁ > 10 is satisfied. F
If it is (false), the process returns to step 2, and if it is T (true), it moves to the process. Create a reduction command to delete records that meet the conditions from database 4, create an updated record by updating the key f ₁ of the record that meets the conditions, and create an insert command to insert this updated record into database 4. and stores these deletion commands, updated records, and insertion commands. Return to next process. The stored deletion commands and insertion commands are arranged for each record so that the deletion command precedes the insertion command. Executes the arranged commands in order from the beginning. Each time old data is deleted and new data is inserted, it is checked whether there are two or more records having the same key value as key _f2 , and a unique check process is performed to count them. The above processing is carried out in conjunction with the update processing block and the unique check processing block 7 and the counter 8. This process will be explained according to the procedure shown in FIG. 〓〓: Delete the old key from the record. 〓〓: Check whether there is a record with the same key value as the old key of 〓〓. In this case, record set R
By arranging the keys according to the key values, it is possible to just check the vicinity of the deletion key. 〓〓: If a record with the same key value is found,
Subtract 1 from the counter CTR. This indicates that one identical key (double) has been resolved by deleting the old key. 〓〓: Update the deleted old key (+10) and determine the position to insert the updated record with the new key. 〓〓: Insert the updated record with the new key into the position determined by 〓〓. 〓〓: Check the number of records that have the same key value as the new key of the record inserted with 〓〓. If there is one record, it is determined that there are no other records with the same key value as the inserted record, and the record is not counted. 〓〓: If there are 2, it means that a record with the same key has been duplicated, so the counter
Add 1 to CTR. If three records with the same key are found in 〓〓, it is assumed that the uniqueness of the key has already been destroyed, and error processing (RollBack) is performed to invalidate this update process. 〓〓: If records to be updated remain, return to 〓〓 and process the next record. And when all the records to be updated are gone,
Finish the process. As a specific example, consider the set γ={10, 20, 30}, and consider the update process of increasing 10 to 20, 20 to 30, and 30 to 40, each by +10. This update process is divided into three stages in which 10, 20, and 30 old keys are updated. The state of the set at each update stage, the counting operation of the counter CTR based on the detection result of the equivalent key, and the count value are shown below.

〔Effect of the invention〕

As is clear from the above description, according to the present invention, when collectively updating a database, it is possible to easily check whether records with the same unique key field value exist.

[Brief explanation of drawings]

FIG. 1 is a diagram showing an overview of a relational database system to which the present invention is applied, FIG. 2 is a configuration diagram of a system according to an embodiment of the present invention, and FIG.
The figure is an overall flowchart of the record update processing procedure.
The figure is a flowchart showing detailed procedures of update processing and unique check processing, and FIG. 5 is an explanatory diagram of application to indexes. In the figure, 1 is the database management section, 2 is the execution control section, 3 is the access processing section, and 4 is the relational
5 is a command analysis block, 7 is a unique check processing block, and 8 is a counter (CTR).

Claims (1)

[Claims] 1 In a database processing system equipped with a function to update multiple records containing a unique key with a single command, when a unique key update is instructed by the above command, one of the records to be updated is selected from a set of records having a unique key. a first means for deleting records containing old keys and inserting records containing new keys with updated keys;
A second means for counting duplications of keys; and when a record including one old key is deleted from the set of records having the unique key, a record having the same value as the old key remains in the set of records having the unique key. a third means for detecting whether or not a record including the key exists, and subtracting 1 from the count value of the second means only when the existence is detected;
When a record including an updated new key is inserted into the set of records having the unique key, detecting whether or not there is a record including a key with the same value as the new key in the set of records having the unique key; and a fourth means that adds 1 to the count value of the first means only when it is detected that there is one other record containing the same value key, and for all the old keys to be updated. 1st above
A database system characterized in that the fourth means is operated repeatedly, and the validity of the update of the unique key is determined based on the fact that the count value of the second means is 0 at the end of the operation. unique key update processing method.