Maintaining Database Referential Integrity Using Different Primary and Foreign Key Values

A computing device managing a database receives a database command comprising first and second identifiers identifying first and second datasets, respectively, in the database. The database command further comprises a key translation indicator identifying a key value translation function. Responsive to receiving the database command, the computing device generates a translated key value from a primary key value of a first record in the first dataset using the key value translation function, and generates a second record in the second dataset. The second record comprises the translated key value. Subsequently, the computing device selects the first and second records from the first and second datasets, respectively. To select the first and second records, the computing device applies the key value translation function to one of the primary or translated key value to determine the other of the primary or translated key value.

BACKGROUND

Databases are a common way for computing devices to store information. Relational databases, in particular, store such information in the records of separate related datasets. Thus, for example, a database that tracks dogs and their owners may have a first dataset representing the owners, and a second dataset representing the dogs that belong to those owners. A relational database associates the owners in the first dataset with the dogs in the second dataset through the use of primary and foreign keys. Traditionally, when the primary key value of a record in the first dataset matches the foreign key value of a record in the second dataset, those two records are considered to be related or associated with each other.

A primary key is a field (or set of fields) that uniquely identifies a single record in a dataset. For example, a social security number or employee ID is generally a good choice for a primary key, because those values tend to be inherently unique. A person's first and last name is generally not a good choice for a primary key field because it is possible, for example, for two people named “John Smith” to each need to be unambiguously represented in the database. For example, if the database described above had two dog owners named “John Smith,” and a dog was related in the database to “John Smith” by using “John Smith” as the value of the foreign key in that dog's record, it would be ambiguous as to which “John Smith” record the dog's record is related to. However, if the dog's record used a unique owner ID as a foreign key value matching one (and only one) record in the owner dataset, there would be no ambiguity as to which owner owns the dog.

An important concept in relational databases is the notion of referential integrity. Traditionally, referential integrity exists between two datasets of a relational database when the foreign key value of every record in one of the datasets is either null, or matches a primary key value in the other of the datasets. Relational databases traditionally enforce referential integrity by correcting the dataset with foreign keys (i.e., the child dataset) when records are deleted from the dataset with corresponding primary keys (i.e., the parent dataset). In other words, if John Smith died, upon deletion of John Smith's owner record, the foreign key values of all dog records assigned to John Smith would be set to a null value, set to another owner's primary key value, or those dog records would also be deleted, for example, so that all dog records make valid (or no) reference to a record in the parent table.

Many databases store data that is very sensitive in nature, such as credit card transactions and medical records. According to traditional relational databases, if a malfeasant is able to save the contents of all of the datasets of a database to a storage system under their control, that malfeasant would easily be able to recognize which records of one dataset are related to which records of another dataset because it is known that traditional relational databases reflect the relationship between records using matching primary and foreign key values, as discussed above. Thus, a malfeasant would be able to discern which patients have which illnesses, for example, or which buyers purchased which items and for how much. Due to the highly sensitive nature of the data that may be stored in modern databases, maintaining security in relational databases is important.

SUMMARY

Embodiments of the present disclosure hinder the ability of a malfeasant to determine the relationship between datasets of a database by obfuscating the relationship between primary and foreign keys therein. In particular, embodiments of the present disclosure maintain referential integrity between two datasets without requiring that the foreign key value of a record in one dataset be the same as the primary key value of a record in the other. Rather, a key value translation function is used to translate between primary and foreign key values, such that the relationship between records of the datasets is not clear to an ordinary observer.

In particular, various embodiments of the present disclosure include computer-implemented methods, systems, apparatus, and non-transitory computer readable mediums storing computer program products for maintaining database referential integrity using different primary and foreign key values. In an embodiment, a computer-implemented method comprises receiving, by a computing device managing a database, a database command comprising first and second identifiers identifying first and second datasets, respectively, in the database. The database command further comprises a key translation indicator identifying a key value translation function. Responsive to receiving the database command, the method further comprises generating a translated key value from a primary key value of a first record in the first dataset using the key value translation function, and generating a second record in the second dataset. The second record comprises the translated key value. The method further comprises selecting the first and second records from the first and second datasets, respectively. The selecting comprises applying the key value translation function to one of the primary or translated key value to determine the other of the primary or translated key value.

In another embodiment, a computing device comprises interface circuitry and processing circuitry. The interface circuitry is configured to exchange signals with a database managed by the computing device and a user interface. The processing circuitry is communicatively coupled to the interface circuitry and is configured to identify first and second datasets of the database by first and second identifiers, respectively. The processing circuitry is further configured to generate a second record in the second dataset via the interface circuitry. The second record comprises a translated key value generated from a primary key value of a first record in the first dataset according to a key value translation function. The processing circuitry is further configured to select the first and second records from the first and second datasets, respectively. To select the first and second records the processing circuitry is configured to apply the key value translation function to one of the primary or translated key value to determine the other of the primary or translated key value.

In yet another embodiment, a non-transitory computer readable medium stores a computer program product for controlling a programmable computing device managing a database. The computer program product comprises software instructions that are executable to cause the programmable computing device to receive a database command comprising first and second identifiers identifying first and second datasets, respectively, in the database, and a key translation indicator identifying a key value translation function. Responsive to receiving the database command, the software instructions further cause the programmable computing device to generate a translated key value from a primary key value of a first record in the first dataset using the key value translation function, and generate a second record in the second dataset. The second record comprises the translated key value. The software instructions further cause the programmable computing device to select the first and second records from the first and second datasets, respectively. The selecting comprises applying the key value translation function to one of the primary or translated key value to determine the other of the primary or translated key value.

The embodiments of the present disclosure are not limited to the above contexts or examples, but may include other features and advantages, such as those described in the following detailed description.

DETAILED DESCRIPTION

To the extent that “one of” a conjunctive list of items (e.g., “one of A and B”) is discussed, the present disclosure refers to one (but not both) of the items in the list (e.g., an A or a B, but not both A and B). Such a phrase does not refer to one of each of the list items (e.g., one A and one B), nor does such a phrase refer to only one of a single item in the list (e.g., only one A, or only one B). Similarly, to the extent that “at least one of” a conjunctive list of items is discussed (and similarly for “one or more of” such a list), the present disclosure refers to any item in the list or any combination of the items in the list (e.g., an A only, a B only, or both an A and a B). Such a phrase does not refer to one or more of each of the items in the list (e.g., one or more of A, and one or more of B).

In general, embodiments of the present disclosure relate to a computing device that manages a database.FIG. 1illustrates an example network environment100in which just such a computing device110may operate. In the example ofFIG. 1, the network environment100includes the computing device110, a remote device115, and a network105interconnecting the devices110,115.

The network105is any network capable of exchanging communication signals with the computing device110and remote device115. Examples of the network105include (but are not limited to) one or more of: the Internet; one or more local area networks; one or more wireless networks; one or more cellular networks; one or more Internet Protocol-based networks; one or more Ethernet networks; one or more optical networks; and one or more circuit switched networks. The network105may comprise any number of networking devices such as routers, gateways, switches, hubs, firewalls, and the like (not shown) supporting the exchange of such communication signals.

The remote device115is communicatively connected to, and capable of exchanging signals with, the network105. Examples of the remote device115include a personal computer, a laptop computer, a desktop computer, a workstation, a smartphone, a tablet computer, a wearable computer, a server, a server cluster, a smart appliance, network attached storage, and a storage area network.

The computing device110is also communicatively connected to, and capable of exchanging signals with, the network105. The computing device110comprises a database120and a translation function pool130, which will be discussed in greater detail below. Examples of the computing device110include a personal computer, a laptop computer, a desktop computer, a workstation, a smartphone, a tablet computer, a wearable computer, a server, a server cluster, a smart appliance, network attached storage, and a storage area network.

AlthoughFIG. 1depicts a computing device110that comprises a database120and translation function pool130, according to other embodiments of the present disclosure, the database120, the translation function pool130, or both the database120and the translation function pool130are comprised within the remote device115, and accessed by the computing device110via the network105. Further, althoughFIG. 1depicts an example network environment100that comprises a network105and a remote device115, other embodiments include standalone configurations of the computing device110(i.e., without the network105and remote device115).

FIG. 2illustrates an example database120, according to embodiments of the present disclosure. Example database120includes two datasets210a,210b. Each dataset210a,210bcomprises a record220a,220b, respectively. The record220acomprises a field230aand a primary key240a. The record220bcomprises a field230band a foreign key250a. The two datasets210a,210bare associated with each other by the values stored in the primary key240aand foreign key250aof the respective records220a,220b. The value of one of these two keys240a,250amay be determined by applying a key value translation function to the other of the two keys240a,250a. Accordingly, the records220a,220bare associated with each other by having a primary key240aand a foreign key250a, respectively, which correspond according to the key value translation function. According to this example, the key value translation function is one of a plurality of predefined key value translation functions stored in the translation function pool130.

AlthoughFIG. 2illustrates two datasets210a,210b, other embodiments of the database120include more than two datasets210. Further, although each dataset210a,210bis depicted inFIG. 2as having one record220a,220brespectively, other embodiments of the database120have a dataset210that comprises a plurality of records220. Several of these embodiments will be discussed in further detail below.

An example translation function pool130is illustrated inFIG. 3. The translation function pool130ofFIG. 3comprises three key value translation functions310a,310b,310c. Each of the key value translations310a,310b,310cspecify a translation scheme between a primary key240value (represented by the variable x) and a foreign key250value (represented by the variable y). For example, the key value translation function310amultiplies a primary key240value by ten, then subtracts one, to determine a corresponding foreign key250value.

AlthoughFIG. 3illustrates key value translation functions310a-cthat map a primary key240ato a foreign key250avalue, the inverse of the functions310a-cdepicted inFIG. 3map the foreign key250aback to the primary key240a. Thus, to determine the primary key240avalue from the foreign key250avalue according to key value translation function310a, one is added to the foreign key250avalue, and the result is divided by ten. Accordingly, despite the functions310a-cbeing depicted inFIG. 3in the form f(x)=y for purposes of explanation, a key value translation function310allows for any one of the primary key240aand the foreign key250avalues to be determined from the other, either directly or by extrapolation (e.g., by applying logical mathematical properties).

Further, although the key value translation functions310a-care depicted inFIG. 3as mathematical expressions, a key value translation function310may include other ways of translating between primary key240and foreign key250values. Other examples of key value translation functions310include, but are not limited to, applying a cipher (e.g., a substitution cipher), bit-shifting, converting, compressing/decompressing, encrypting/decrypting, and/or encoding/decoding between the values.

The particular key value translation function310to be used to maintain referential integrity between datasets210of the database120may be identified in various ways, depending on the embodiment. For example, in one embodiment, the key value translation functions310a-cin the translation function pool130are predefined, and the particular key value translation function310to be used to maintain referential integrity between datasets210a-bof the database120is selected by the computing device110at random.

In another embodiment, a key value translation function310is user-specified and received in a database command that identifies the datasets210a-bto be related. The received key value translation function310is then stored in the translation function pool130for future use.

In another embodiment, the key value translation function310is generated by computing device110and stored in the translation function pool130for future use in response to a database command. For example, the computing device110may randomly generate coefficients to plug into a base polynomial formula. As one simple example, the computing device may use a base polynomial formula of Ax2+Bx−C=y, wherein x is the value of the primary key240, y is the value of the foreign key250, and the values of A, B, and C are randomly-generated coefficients that the computing device110generates in response to the database command. In such an example, if the computing device110generates values for A, B, and C of 5, 2, and 1, respectively, the resulting key value translation function310cwould be 5x2+2x−1=y. The generated key value translation function310cis then stored in the translation function pool130for future use.

Identifying a key value translation function310may, according to embodiments, be handled autonomously by the computing device110, or may be responsive to a database command received by the computing device110. When performed in response to a database command, such a command be formatted according to the syntax “<INSTUCTION> RI <CHILD>(FK) ON <PARENT>(PK) USING <INDICATOR>.” In such an example, <INSTRUCTION> indicates whether a new relationship should be created or an existing relationship should be modified between datasets210. RI indicates that the command relates to referential integrity between datasets210. <CHILD> is an identifier that identifies the child dataset of the relationship (e.g., dataset210b). FK identifies which field in the records220of the child210is the foreign key250. PARENT is an identifier that identifies the parent dataset210of the relationship (e.g., dataset210a). PK identifies which field in the records220of the parent210is the primary key240. USING indicates that a key value translation function310should be used for maintaining referential integrity between the parent and child. <INDICATOR> is a key translation indicator that identifies the key value translation function.

According to one example, in response to receiving the database command “CREATE RI DOGS(ID_NUMBER) ON OWNERS (SSN) USING RANDOM,” the computing device110randomly selects key value translation function310afrom the translation function pool130, and uses that key value translation function310ato create a parent/child relationship between the “OWNERS” and “DOGS” datasets210, using the “SSN” field in the “OWNERS” dataset as the primary key240and the “ID_NUMBER” field in the “DOGS” dataset as the foreign key250. To ensure that referential integrity between the “OWNERS” and “DOGS” datasets210exists, the computing device110may also delete records from the “DOGS” dataset210, and/or update the values of the “ID_NUMBER” field with null values (i.e., to ensure that no record220in the “DOGS” dataset210makes an invalid reference to the “OWNERS” dataset210, according to the selected translation function310a).

According to another example, in response to subsequently receiving the database command “UPDATE RI DOGS(ID_NUMBER) ON OWNERS(SSN) USING AUTOMATIC,” the computing device generates a new key value translation function310to replace the previous randomly-selected key value translation function310. The new key translation function310is then stored in the translation function pool130as previously described. According to embodiments, such an update command further causes the computing device110to update all of the foreign key250values according to the new key value translation function310and the relationships already established between the datasets210.

According to yet another example, in response to further subsequently receiving the database command “UPDATE RI DOGS(ID_NUMBER) ON OWNERS(SSN) USING (x+13=y),” the computing device uses x+13=y as a new key value translation function310to replace the previous automatically-generated key value translation function310. The new key translation function310is then stored in the translation function pool130as previously described.

In view of the above,FIGS. 4A-Eillustrate an example of maintaining referential integrity among datasets210in response to various database commands, according to embodiments of the present disclosure. In particular, the referential integrity illustrated inFIGS. 4A-4Eis maintained using different primary key240and foreign key250values between related datasets210. As will be shown below, even though the primary key240and foreign key250values are different between related datasets210, invalid references are prevented by maintaining a consistent relationship between primary key240and foreign key250that is in accordance with a key value translation function310.

To begin,FIG. 4Aillustrates two datasets210a,210b, which the computing device110initially created in response to receiving a “CREATE” command with a key translation indicator that identified a user-specified key value translation function310bof 4x=y. In response, the computing device110stored the key value translation function310bin the translation function pool130as depicted inFIG. 3.

The “CREATE” command that caused the computing device110to create the datasets210a,210bcomprised an identifier for the parent dataset210aof “OWNERS” and an identifier for the child dataset210bof “DOGS.” Initially, these two datasets210a,210bwere empty. However, a user subsequently added “Adam” to the list of “OWNERS,” which the computing device110stored as record220ain parent dataset210a. To store the record220ain parent dataset210a, the computing device populated field230awith the name “Adam,” and generated a unique value of “1” for the primary key240a.

The user then added “Fido” to the list of “DOGS” with Adam as the owner. To do so, the computing device110stored “Fido” in the child dataset210bas record220bin association with Adam's record220ain the parent dataset210a. To store the record220bin child dataset210b, the computing device populated field230bwith the name “Fido” and generated a value of “4” for the foreign key250aas specified by the received user-specified key value translation function310bof 4x=y.

Having stored Adam's record220ain dataset210aand Fido's record220bin dataset210b, a user of the computing device110, a user of the remote device115, or both are able to issue one or more queries against the database120such that both of records220a,220bare located and selected. For example, a user may request all dogs owned by Adam. In response to such a query, the computing device110will search dataset210afor record220acorresponding to “Adam,” then apply the key value translation function310bto the value of the primary key240ato obtain the foreign key250aof the record220bcorresponding to the dog owned by Adam, namely, Fido.

Similarly, a user may request Fido's owner. In response to such a query, the computing device110will search dataset210bfor record220bcorresponding to “Fido,” then apply the key value translation function310bto the value of the foreign key250ato obtain the primary key240aof the record220acorresponding to Fido's owner, namely Adam. Accordingly, the computing device110may select first and second records from first and second datasets, respectively, by applying the key value translation function to one of the primary key240or foreign key250values to determine the other of the key values.

After the datasets210a,210bare established as depicted inFIG. 4A, the computing device110receives an “UPDATE” command indicating that the computing device110should randomly select a new key value translation function310. In response, the computing device110randomly selects the key value translation function310aof 10x−1=y, which the computing device110then applies to record220bby recalculating a value for foreign key250a. In accordance with the randomly-selected key value translation function310a, and as depicted inFIG. 4B, the new value established for the foreign key250ais “9.”

After the datasets210a,210bare established as depicted inFIG. 4B, Adam buys a new dog named “Rover.” Accordingly, a user of the computing device110adds “Rover” to the list of “DOGS” with Adam as the owner, resulting in the dataset210bto be updated as shown inFIG. 4C. To add the new dog, the computing device110stores “Rover” in the child dataset210bas record220cin association with Adam's record220ain the parent dataset210a. To store the record220cin child dataset210b, the computing device populates field230cwith the name “Rover” and generates a value of “9” for the foreign key250ain accordance with the randomly-selected key value translation function310a. Thus, the key value translation function310a, as applied to primary key240a, results in the same value to be calculated for the foreign keys250a,250bof both records220b,220c, which reflects the relationship of both of those records220b,220cwith record220a.

After the datasets210a,210bare established as depicted inFIG. 4C, Adam gives Rover to a new owner, Bob. Accordingly, a user of the computing device110adds “Bob” to the list of “OWNERS,” which results in the datasets210a,210bbeing updated as depicted in FIG.4D. In particular, to add “Bob” to the list of “OWNERS,” the computing device110stores record220din parent dataset210a, populating field230dwith the name “Bob,” and generating a unique value of “2” for the primary key240b. To reflect Bob's new relationship with Rover, the computing device110calculates a new value of “19” for the foreign key250bof record220cin accordance with the key value translation function310a.

After the datasets210a,210bare established as depicted inFIG. 4D, Rover dies and Adam decides to get a cat named “Toonces.”FIG. 4Eillustrates the datasets210a,210bas modified to reflect these occurrences. In particular, to reflect Rover's death, a user of the computing device110sends a database query to the computing device110to determine Rover's owner. The database query finds Rover's record220cin the “DOGS” dataset210b, then applies the key value translation function310ato the value of the corresponding foreign key250b, i.e., “19.” The result of applying the key value translation function310ato value of foreign key250bis the value “2,” which uniquely identifies Bob's record220dby its primary key240b. The computing device110then deletes Rover's record220c(since that dog has died). Also, since Bob owns no other dogs (i.e., no other dogs have a foreign key250value of “19”), the computing device110also deletes Bob's record220d.

To reflect Adam getting a cat, a user of the computing device110issues a command to create a new relationship between the existing “OWNERS” list as the parent dataset210aand a new “CATS” list as the child dataset210c. The command indicates that the computing device110should automatically generate a new key value translation function310. In response, the computing device110generates the key value translation function310cof 5x2+2x−1=y, which is then stored in the translation function pool130as described above. The computing device110also creates the child dataset210c. A user then adds “Toonces” to the “CATS” dataset210c, indicating that Adam should be the owner. In response, the computing device110stores record220ein dataset210c, populating field230ewith the name “Toonces” and generating a value of “6” for the foreign key250cin accordance with the automatically-generated key value translation function310cgiven Adam's primary key240avalue of “1.”

In view of all of the above, an example of a computer-implemented method500according to embodiments of the present disclosure is illustrated inFIG. 5. The method500comprises receiving, by a computing device110managing a database120, a database command comprising first and second identifiers and a key translation indicator (block510). The first and second identifiers identify first and second datasets210a,210brespectively, in the database120. The key translation indicator identifies a key value translation function310.

The method500further comprises, responsive to receiving the database command, generating a translated key250avalue from a primary key240avalue of a first record220ain the first dataset210ausing the key value translation function310(block520), and generating a second record220bin the second dataset210b, the second record220bcomprising the translated key value250a(block530). The method500further comprises, also in response to receiving the database command, selecting the first and second records220a,220bfrom the first and second datasets,210a,210brespectively (block540). The selecting comprises applying the key value translation function310to one of the primary or translated key240a,250avalue to determine the other of the primary or translated key240a,250avalue.

Other embodiments of the present disclosure include the computing device110implemented according to the hardware illustrated inFIG. 6. The example hardware ofFIG. 6comprises processing circuitry710, memory circuitry720, and interface circuitry730. The processing circuitry710is communicatively coupled to the memory circuitry720and the interface circuitry730, e.g., via one or more buses. The processing circuitry710may comprise one or more microprocessors, microcontrollers, hardware circuits, discrete logic circuits, hardware registers, digital signal processors (DSPs), field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), or a combination thereof. For example, the processing circuitry710may be programmable hardware capable of executing software instructions stored as a machine-readable computer program760in the memory circuitry720. The memory circuitry720of the various embodiments may comprise any non-transitory machine-readable media known in the art or that may be developed, whether volatile or non-volatile, including but not limited to solid state media (e.g., SRAM, DRAM, DDRAM, ROM, PROM, EPROM, flash memory, solid state drive, etc.), removable storage devices (e.g., Secure Digital (SD) card, miniSD card, microSD card, memory stick, thumb-drive, USB flash drive, ROM cartridge, Universal Media Disc), fixed drive (e.g., magnetic hard disk drive), or the like, wholly or in any combination.

The interface circuitry730may be a controller hub configured to control the input and output (I/O) data paths of the computing device110. Such I/O data paths may include data paths for exchanging signals over a communications network105and data paths for exchanging signals with a user. For example, the interface circuitry730may comprise a transceiver configured to send and receive communication signals over one or more of a cellular network, Ethernet network, or optical network. The interface circuitry730may also comprise one or more of a graphics adapter, display port, video bus, touchscreen, graphical processing unit (GPU), display port, Liquid Crystal Display (LCD), and Light Emitting Diode (LED) display, for presenting visual information to a user. The interface circuitry730may also comprise one or more of a pointing device (e.g., a mouse, stylus, touchpad, trackball, pointing stick, joystick), touchscreen, microphone for speech input, optical sensor for optical recognition of gestures, and keyboard for text entry.

The interface circuitry730may be implemented as a unitary physical component, or as a plurality of physical components that are contiguously or separately arranged, any of which may be communicatively coupled to any other, or may communicate with any other via the processing circuitry710. For example, the interface circuitry730may comprise output circuitry740(e.g., transmitter circuitry configured to send communication signals over the communications network105) and input circuitry750(e.g., receiver circuitry configured to receive communication signals over the communications network105). Similarly, the output circuitry740may comprise a display, whereas the input circuitry750may comprise a keyboard. Other examples, permutations, and arrangements of the above and their equivalents will be readily apparent to those of ordinary skill.

According to embodiments of the hardware illustrated inFIG. 6, the interface circuitry730is configured to exchange signals with a database120managed by the computing device110, and a user interface. The processing circuitry710is configured to identify first and second datasets210a,210bof the database120by first and second identifiers, respectively, and generate a second record220bin the second dataset210bvia the interface circuitry730. The second record220bcomprises a translated key250avalue generated from a primary key240avalue of a first record220ain the first dataset210aaccording to a key value translation function310. The processing circuitry710is further configured to select the first and second records220a,220bfrom the first and second datasets210a,210b, respectively. To select the first and second records220a,220bthe processing circuitry710is configured to apply the key value translation function310to one of the primary or translated key240a,250avalue to determine the other of the primary or translated key value240a,250a.