US-20260127160-A1 - MECHANISMS FOR LOGICAL REFERENTIAL INTEGRITY IN RELATIONAL DATABASE MANAGEMENT SYSTEMS

US20260127160A1US 20260127160 A1US20260127160 A1US 20260127160A1US-20260127160-A1

Abstract

Techniques provide mechanisms for logical referential integrity in relational database management systems. A child record with a foreign key can be inserted into a child table out of order from inserting parent record with the primary key without foreign key violations. When the child record is inserted a referential integrity check is performed to determine whether the primary key exists in a parent table. An integrant field for each child record is set to indicate whether the primary key exists. Query results can filter out records that do not have referential integrity.

Inventors

Ivan Melyakov
Carlos Sierra
Quais Taraki

Assignees

ORACLE INTERNATIONAL CORPORATION

Dates

Publication Date: 20260507
Application Date: 20241107

Claims (16)

1 . A method comprising: inserting records of a first parent table and a child table of said first parent table into a database, wherein said child table includes a first foreign key that includes a plurality of first foreign key values, wherein said first parent table includes a first primary key that includes a plurality of first primary key values; for each child table record of a set of child table records of said child table: determining that no record in said first parent table holds a first primary key value that equals a first foreign key value held in the first foreign key of said each child table record; and in response to determining that no record in said first parent table holds an first primary key value that equals the first foreign key value held in the first foreign key of said each child table record, setting an integrant field in said each child table record to indicate that said first foreign key value does not equal any first primary key value in said first primary key of said first parent table, said integrant field being indexed on an index; repeatedly index scanning, by a database process, on said index to continuously check for child table records that have the integrant field set to indicate said first foreign key value does not equal any first primary key value in said first primary key; and querying said child table, wherein querying said child table includes filtering out any child table record from said child table having an integrant field that indicates said first foreign key value does not equal any first primary key value in said first primary key.
2 . The method of claim 1 , wherein said querying includes querying a view that filters records based on each said integrant field.
3 . The method of claim 2 , wherein the view filters records based on said integrant field.
4 . The method of claim 1 , wherein said querying is performed in response to receiving a database statement from a client of a database management system that manages said database, said database statement specifying to filter based on said integrant field.
5 . (canceled)
6 . The method of claim 5 , wherein each child table record includes a time stamp field that indicates a time when an initial determination was made of a first primary key value not equaling a first foreign key value, wherein the method comprises, for each child table record that has an integrant field set to indicate that said first foreign key value does not equal any first primary key value in said first primary key of said first parent table: comparing an initial determination time period from the time when the initial determination was made to a threshold time period; and stopping the repeated index scanning based on the initial determination time period exceeding the threshold time period.
7 . The method of claim 6 , further comprising issuing an integrity check failure alert based on the initial determination time period exceeding the threshold time period.
8 . The method of claim 1 , wherein the first parent table comprises a second foreign key that includes a plurality of second foreign key values; wherein a second parent table includes a second primary key that includes a plurality of second primary key values; for each first parent table record of a set of first parent table records of said second parent table: determining that no record in said second parent table holds a second primary key value that equals a second foreign key value held in the second foreign key of said each first parent table record; and in response to determining that no record in said second parent table holds a second primary key value that equals a second foreign key value held in the second foreign key of said each first parent table record, setting a second integrant field in said each first parent table record to indicate that said second foreign key value does not equal any second primary key value in said second primary key of said second parent table; and querying said first parent table, wherein querying said first parent table includes filtering out any first parent table record from said first parent table having a second integrant field that indicates said second foreign key value does not equal any second primary key value in said second primary key.
9 . One or more non-transitory storage media storing one or more sequences of instructions, which when executed by one or more computing devices, cause: inserting records of a first parent table and a child table of said first parent table into a database, wherein said child table includes a first foreign key that includes a plurality of first foreign key values, wherein said first parent table includes a first primary key that includes a plurality of first primary key values; for each child table record of a set of child table records of said child table: determining that no record in said first parent table holds a first primary key value that equals a first foreign key value held in the first foreign key of said each child table record; and in response to determining that no record in said first parent table holds an first primary key value that equals the first foreign key value held in the first foreign key of said each child table record, setting an integrant field in said each child table record to indicate that said first foreign key value does not equal any first primary key value in said first primary key of said first parent table, said integrant field being indexed on an index; repeatedly index scanning by a database process. on said index to continuously check for child table records that have the integrant field set to indicate said first foreign key value does not equal any first primary key value in said first primary key; and querying said child table, wherein querying said child table includes filtering out any child table record from said child table having an integrant field that indicates said first foreign key value does not equal any first primary key value in said first primary key.
10 . The one or more storage media of claim 9 , wherein said querying includes querying a view that filters records based on each said integrant field.
11 . The one or more storage media of claim 10 , wherein the view filters records based on said integrant field.
12 . The one or more storage media of claim 9 , wherein the instructions, when executed by the one or more computing devices, further cause said querying to be performed in response to receiving a database statement from a client of a database management system that manages said database, said database statement specifying to filter based on said integrant field.
13 . (canceled)
14 . The one or more storage media of claim 9 , wherein each child table record includes a time stamp field that indicates a time when an initial determination was made of a first primary key value not equaling a first foreign key value, wherein the instructions, when executed by the one or more computing devices, further cause, for each child table record that has an integrant field set to indicate that said first foreign key value does not equal any first primary key value in said first primary key of said first parent table: comparing an initial determination time period from the time when the initial determination was made to a threshold time period; and stopping the repeated index scanning based on the initial determination time period exceeding the threshold time period.
15 . The one or more storage media of claim 14 , wherein the instructions, when executed by the one or more computing devices, further cause issuing an integrity check failure alert based on the initial determination time period exceeding the threshold time period.
16 . The one or more storage media of claim 9 , wherein the first parent table comprises a second foreign key that includes a plurality of second foreign key values; wherein a second parent table includes a second primary key that includes a plurality of second primary key values; wherein the instructions, when executed by the one or more computing devices, further cause: for each first parent table record of a set of first parent table records of said second parent table: determining that no record in said second parent table holds a second primary key value that equals a second foreign key value held in the second foreign key of said each first parent table record; and in response to determining that no record in said second parent table holds a second primary key value that equals a second foreign key value held in the second foreign key of said each first parent table record, setting a second integrant field in said each first parent table record to indicate that said second foreign key value does not equal any second primary key value in said second primary key of said second parent table; and querying said first parent table, wherein querying said first parent table includes filtering out any first parent table record from said first parent table having a second integrant field that indicates said second foreign key value does not equal any second primary key value in said second primary key.

Description

FIELD The present application relates to database systems and, more specifically, to mechanisms for logical referential integrity for records in parent and child tables in relational database management systems. BACKGROUND A relational database organizes data into tables that include rows and columns. Each row represents a unique record, and each column represents a field within the record. A foreign key is used in a child table to reference a record with a corresponding primary key in a parent table. For example, a database can include a table for departments and a table for employees. The department table can include Department ID, Manager, Phone fields, while the employee table can include Employee ID, Name, Address, and Department ID fields. The Department ID field of the employee table acts as a foreign key that links each employee to a specific department in the department table, which creates a parent and child relationship between the tables. Referential integrity requires that any foreign key value in a child table must match an existing primary key value in the related parent table to ensure consistency of data within relationships between tables. In the example above, a value in a cell of the Department ID field of a record of the employee table must correspond to a valid value in a cell of a record of the Department ID field in the department table. Referential integrity prevents foreign key violations. An example foreign key violation is a foreign key value in a record of a child table, where the foreign key value does not match a primary key value in a parent table, such as when the record in the child table is inserted before a record holding that primary key value has been inserted in the parent table. A foreign key value that does not match a primary key value may be referred to herein as a dangling foreign key value. Relational databases are managed using a Relational Database Management System (RDBMS). A RDBMS provides an interface between users and applications and the database and also provides administrative functions for managing data storage, access, and performance. For example, an RDBMS allows users to create, read, update, and delete data. As mentioned, records having a foreign key value are sometimes inserted into a child table before a record with a matching primary key value is inserted into the parent table. For example, control planes manage the lifecycle of cloud resources. As a particular example, when a Compute Virtual Machine is created, a control plane orchestrates writing records to a configuration database that describe a configuration of the Computer Virtual Machine. Thus, control planes can be used to handle tasks like creating, updating, and deleting resources for inventory updates. Control plane events can be processed and stored in a set of relational tables collectively representing a customer's inventory state in Near-Real-Time (NRT). Customer inventory tables have foreign key constraints denoting logical relationships between resources. The control planes can be independent, uncoordinated, and asynchronous. This means records are inserted into parent and child tables out of order. For example, a resource record with a foreign key value can be inserted into a child table before a resource record with the matching primary key value is inserted into the parent table. This ingestion of such disjoint events into a relational database with foreign key constraints typically leads to foreign key violations and ingestion pipeline brittleness. For example, the RDBMS could enforce foreign key constraints, but this would result in rejecting attempts to insert child resource records with the foreign keys when the parent record with the primary key does not exist. This causes the concept of a brittle ingestion pipeline because it is undesirable and infeasible to force customers and control planes to coordinate the entry of all records in a specific sequence. Also, if the foreign key constraints are not enforced, customers receive incomplete information with a lack of referential integrity. For example, if a customer queries their data store, they see dangling resources without information from the primary key of records that do not exist, and the customer does not know why there is a key to a non-existent record. The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as background merely by virtue of their inclusion in this section. Further, it should not be assumed that any of the approaches described in this section are well-understood, routine, or conventional merely by virtue of their inclusion in this section. BRIEF DESCRIPTION OF THE DRAWINGS In order to describe the manner in which advantages and features of the disclosure can be obtained, a de