Database table foreign key association reconstruction method and device

Abstract

The invention relates to the technical field of databases, and discloses a database table foreign key association reconstruction method and device. The method comprises the steps that columns of multiple target data tables are combined in pairs to generate column pairs, and a first set is constructed based on all the column pairs; pruning the first set based on the target unique key through the target large language model; constructing a second set based on column pairs reserved after pruning; performing foreign key association detection on the column pairs in the second set through the target large language model to obtain a detection result; traversing all the column pairs with foreign key association, and deleting at least one column pair with foreign key association through the target large language model under the condition that foreign key conflicts exist between the column pairs so as to eliminate the foreign key conflicts; under the condition that foreign key conflicts do not exist between the column pairs, foreign key association is reconstructed for the multiple target data tables. By adopting the method, the accuracy and generalization of foreign key association reconstruction of the database can be improved, and the reconstruction quality is improved.

Description

Technical Field

[0001] This application relates to the field of database technology, specifically to a method and apparatus for reconstructing foreign key associations in database tables. Background Technology

[0002] Databases are the foundation of software applications. System integration development often involves the connection and flow between database tables. However, in real-world applications, due to various reasons such as missing documentation, code changes, database table modifications, and personnel turnover, foreign key relationships in database tables are often lost, making efficient and continuous integration development difficult. When dealing with large database tables, manually identifying foreign key relationships is inefficient, wasting time and manpower. Therefore, there is a need to find a way to automatically rebuild foreign key relationships.

[0003] Existing automated foreign key relationship reconstruction schemes rely on manually defined heuristic rules. These rules are used to identify and reconstruct foreign key relationships in database tables. However, due to the diverse data types and complex foreign key relationships in database tables, heuristic rule-based methods fail to capture the semantics of foreign key relationships, resulting in low reconstruction accuracy. Furthermore, manually defined rules are often based on specific features, leading to a sharp decline in recognition performance when applied to databases lacking those features. These schemes also suffer from low generalization. Especially as databases grow larger, the complex foreign key relationships across hundreds or thousands of tables in software systems further exacerbate the problem, making reconstruction even less effective and failing to meet requirements. Summary of the Invention

[0004] The purpose of this application is to provide a method and apparatus for reconstructing foreign key associations in database tables, so as to improve the accuracy and generalizability of reconstructing foreign key associations in database tables.

[0005] To achieve the above objectives, the technical solution of this application is as follows: In a first aspect, embodiments of this application provide a method for reconstructing foreign key associations in a database table, the method comprising: Columns from multiple target data tables are combined in pairs to generate column pairs, and a first set is constructed based on all column pairs; Pruning the first set includes: using the target large language model, deleting column pairs from the first set whose referenced columns are not included in all target unique keys; the target unique key is: the most likely candidate key to be referenced determined from all candidate keys corresponding to the target data table; constructing a second set based on the column pairs retained after pruning; The target large language model is used to detect foreign key associations in the column pairs of the second set, and the detection results are obtained. The detection results are: the column pairs have foreign key associations, or the column pairs do not have foreign key associations. Iterate through all column pairs with foreign key relationships to determine whether there are foreign key conflicts between the column pairs; In the event of foreign key conflicts between column pairs, at least one column pair with a foreign key relationship is deleted through the target large language model to eliminate the foreign key conflict; If there are no foreign key conflicts between column pairs, rebuild the foreign key relationships for the multiple target data tables based on all column pairs that currently have foreign key relationships and do not have foreign key conflicts.

[0006] Optionally, using the target large language model, column pairs whose referenced columns are not included in all target unique keys are removed from the first set, including: Obtain the minimum unique column combination for each target data table, and determine each minimum unique column combination as a candidate key; the minimum unique column combination is the combination of the smallest columns that can uniquely identify a row of data. Extract the first portion of the table data from each target data table; Based on the minimum unique column combination corresponding to each target data table and the partial table data, a corresponding first prompt word is constructed; the first prompt word includes the following analysis rules: Rule 1: A row of data in a table can be uniquely identified and can be referenced; Rule 2: The earlier a column number appears in the data table, the more likely it is to be referenced; Rule 3: Field names ending with "key" or "id" may be referenced; Rule 4: Data types that are integers or strings may be referenced; Rule 5: Shorter texts, easier for humans to read, are more likely to be cited; Rule Six: The fewer the number of fields, the more likely they are to be referenced; Rule 7: Entities whose business meaning is based on the table they belong to and that conform to the business logic can be referenced; The first prompt word corresponding to each target data table is input into the target large language model, so that the target large language model determines the most likely candidate key to be referenced from all candidate keys corresponding to the target data table based on the analysis rules, and uses it as the target unique key corresponding to the target data table; Iterate through each column pair in the first set and delete column pairs whose referenced columns are not included in all target unique keys.

[0007] Optionally, foreign key association detection is performed on column pairs in the second set using the target large language model to obtain detection results, including: The target language model is used to determine the types of target information required to reconstruct foreign key relationships for the multiple target data tables. The types of target information include: maximum value, minimum value, number of rows, column number, and average character length. Based on the type of the target information, query the multiple target data tables to obtain the corresponding target information; Based on the target information and each column pair in the second set, a corresponding second prompt word is constructed; Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results.

[0008] Optionally, the type of target information required to reconstruct foreign key relationships for the multiple target data tables is determined through the target large language model, including: Based on all column pairs in the second set, and the table data at the beginning of the target data table to which two columns in the column pairs belong, a first data example is constructed; The first data example is input into the target large language model so that the target large language model can analyze the column pair and the corresponding partial table data to obtain the type of target information required for foreign key association detection of the column pair; Based on the type of the target information, the multiple target data tables are queried to obtain the corresponding target information, and the obtained target information is associated with the corresponding column pairs in the second set.

[0009] Optionally, based on the target information and each column pair in the second set, a corresponding second prompt word is constructed, including: Based on each column pair and its associated target information, a second cue word based on the thought chain is constructed; the second cue word includes the following analysis rules: Rule 1: Determine the semantic validity of foreign key relationships between column pairs based on the semantics of the data table. The higher the semantic validity, the greater the likelihood of a foreign key relationship. Rule 2: Based on syntax rules, determine the similarity between the strings in two columns, and whether the strings have id, key, or no as suffixes. The higher the similarity between the strings, and the greater the possibility of a foreign key relationship if the strings have id, key, or no as suffixes. Rule 3: Based on the data inclusion relationship, coverage, average length difference, and out-of-range ratio of the two columns in the column pair, the likelihood of a foreign key relationship is greater if the two columns are parent and child sets, the higher the coverage, the smaller the average length difference, and the lower the out-of-range ratio. Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results, including: The second prompt word for each column pair is input into the target large language model, so that the target large language model can perform a comprehensive analysis of the column pair based on the analysis rules and the associated target information, and generate the corresponding detection results.

[0010] Optionally, the foreign key conflict includes: multiple reference conflicts and circular reference conflicts; traversing all column pairs with foreign key associations to determine whether there are foreign key conflicts between each column pair includes: Examine all column pairs with foreign key relationships to determine if there is a case where one column references multiple columns; if there is a case where one column references multiple columns, determine that there is a multi-reference conflict between the column pairs; if there is no case where one column references multiple columns, determine that there is no multi-reference conflict between the column pairs. In the absence of multiple reference conflicts, a directed graph of foreign key associations is constructed based on all column pairs with foreign key associations, and it is determined whether the directed graph has a closed cycle. Each node of the directed graph represents a column. Each edge of the directed graph connects two nodes and represents the reference relationship between two columns. If the directed graph has a closed cycle, it is determined that there is a circular reference conflict between column pairs. If the directed graph does not have a closed cycle, it is determined that there is no circular reference conflict between column pairs.

[0011] Optionally, in the event of multiple reference conflicts between column pairs, at least one column pair with a foreign key relationship is deleted through the target large language model to eliminate the foreign key conflict, including: Based on all column pairs corresponding to each multi-reference conflict, a third cue word based on the thought chain is constructed; the third cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The third prompt word and all column pairs with multiple reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with multiple reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pairs other than the column pair with the highest score as the column pairs that need to be deleted, and output them. Remove the column pair that needs to be deleted from the second set.

[0012] Optionally, in the event of a circular reference conflict between column pairs, at least one column pair with a foreign key relationship is deleted through the target large language model to eliminate the foreign key conflict, including: Based on all column pairs corresponding to each circular reference conflict, a fourth cue word based on the thought chain is constructed; the fourth cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The fourth prompt word and all column pairs with circular reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with circular reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pair with the lowest score as the column pair that needs to be deleted, and output it. Remove the column pair that needs to be deleted from the second set; Based on all column pairs in the current second set, reconstruct the directed graph of foreign key associations to determine whether there are circular reference conflicts between the current column pairs.

[0013] Optionally, pruning the first set further includes: Delete column pairs containing empty columns, and delete column pairs corresponding to columns in data tables whose most recent access time is greater than a first threshold from the current time; Check if the data types of the two columns in each column pair are the same, and delete the column pairs corresponding to the two columns with different data types; Delete column pairs containing the following data types: floating-point numbers, booleans, and binary large objects; Determine whether the two columns in each column pair have a parent-child relationship, and delete the column pairs that do not have a parent-child relationship.

[0014] Secondly, embodiments of this application provide a database table foreign key association reconstruction apparatus for implementing the steps in the method provided in the first aspect of this application. The apparatus includes: The preprocessing module is configured to combine columns from multiple target data tables in pairs to generate column pairs, construct a first set based on all column pairs, and prune the first set, including: deleting column pairs from the first set whose referenced columns are not included in all target unique keys using the target large language model; the target unique key is: the most likely candidate key to be referenced determined from all candidate keys corresponding to the target data table; and constructing a second set based on the column pairs retained after pruning. The association recognition module is configured to perform foreign key association detection on column pairs in the second set using the target large language model, and obtain the detection result; the detection result is: the column pair has a foreign key association, or the column pair does not have a foreign key association; The conflict handling module is configured to traverse all column pairs with foreign key associations, determine whether there are foreign key conflicts between the column pairs, and delete at least one column pair with foreign key associations through the target large language model to eliminate the foreign key conflict. The reconstruction module is configured to rebuild foreign key relationships for the multiple target data tables based on all existing column pairs that have foreign key relationships and do not have foreign key conflicts, provided that there are no foreign key conflicts between column pairs.

[0015] This application leverages the multi-dimensional and cross-domain cognitive capabilities of a large language model to analyze foreign key relationships in data tables. Compared to traditional methods that use manually defined heuristic rules, this approach significantly improves the accuracy of foreign key relationship identification and its generalization ability across different types of database tables. It can also handle empty data tables and address application scenarios with non-standard data syntax and difficult-to-understand data semantics. Before detecting column pairs using the large language model, this approach prunes the original set of column pairs (i.e., the first set), removing column pairs whose referenced columns are not included in the target unique key. This reduces the amount of data the large language model needs to process during the foreign key relationship identification stage, decreases the proportion of invalid interactions, and improves the efficiency of foreign key relationship detection.

[0016] In the foreign key association detection stage, this solution employs an indirect discrimination approach. It utilizes a large language model to perform local foreign key association determination on column pairs generated from the target data table. Compared to directly detecting foreign key associations on the entire data table using a large language model, this simplifies the model's decision-making process, effectively avoids "illusions" generated by the large language model, and thus improves the accuracy of foreign key association identification. Furthermore, after identifying local foreign key associations on column pairs using the large language model, this solution also addresses foreign key conflicts between column pairs from a holistic perspective, ensuring the data integrity and consistency of the database tables. Attached Figure Description

[0017] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments of this application will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] Figure 1 This is a flowchart of a database table foreign key association reconstruction method proposed in an embodiment of this application; Figure 2 This is a flowchart of foreign key association detection using a target large language model in one embodiment of this application; Figure 3This is a flowchart of a method for eliminating foreign key conflicts using a target large language model in one embodiment of this application; Figure 4 This is a schematic diagram of a database table foreign key association reconstruction device proposed in an embodiment of this application. Detailed Implementation

[0019] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0020] It should be understood that the phrase "one embodiment" or "an embodiment" throughout the specification means that a specific feature, structure, or characteristic related to the embodiment is included in at least one embodiment of this application. Therefore, "in one embodiment" or "in an embodiment" appearing throughout the specification does not necessarily refer to the same embodiment. Furthermore, these specific features, structures, or characteristics can be combined in any suitable manner in one or more embodiments.

[0021] In the various embodiments of this application, it should be understood that the sequence number of each process described below does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of this application.

[0022] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects as detailed in this application.

[0023] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other.

[0024] Traditional methods for identifying foreign key relationships in database tables using heuristic rules typically rely on data inclusion dependencies and manually defined heuristics. However, this approach is prone to numerous false positives, resulting in low accuracy in foreign key identification. For example, many tables have primary key fields that are incrementing; using inclusion dependencies can easily misclassify two incrementing fields as having a parent-child relationship, leading to false positives. Furthermore, manually defined rules often rely on surface-level characteristics rather than inherent semantics, failing to cover all foreign key relationships and exhibiting low accuracy and poor generalization.

[0025] This application leverages the multi-dimensional and cross-domain cognitive capabilities of a target large language model to analyze and judge the entity semantics of a data table from a semantic inference perspective. It simplifies the decision-making process and the number of decisions by generating column pairs and pruning, effectively avoiding model illusions and thus improving the accuracy of foreign key association identification. The application will now be described in detail with reference to the accompanying drawings and embodiments.

[0026] Figure 1 This is a flowchart of a database table foreign key association reconstruction method proposed in an embodiment of this application. Figure 1 As shown, the method includes: S1: Combine columns from multiple target data tables in pairs to generate column pairs, and construct the first set based on all column pairs; S2: Pruning the first set includes: using the target large language model, deleting column pairs from the first set whose referenced columns are not included in all target unique keys; the target unique key is: the most likely candidate key to be referenced determined from all candidate keys corresponding to the target data table; constructing a second set based on the column pairs retained after pruning; S3: Detect foreign key associations in the column pairs of the second set using the target large language model, and obtain the detection results; the detection results are: the column pairs have foreign key associations, or the column pairs do not have foreign key associations; S4: Iterate through all column pairs with foreign key relationships and determine whether there are foreign key conflicts between the column pairs; S5: In the event of a foreign key conflict between column pairs, delete at least one column pair with a foreign key relationship using the target large language model to eliminate the foreign key conflict; S6: If there are no foreign key conflicts between column pairs, rebuild the foreign key relationships for the multiple target data tables based on all column pairs that currently have foreign key relationships and do not have foreign key conflicts.

[0027] This scheme employs an indirect decision-making approach, utilizing a target large language model to identify foreign key relationships in target data tables. Specifically, columns from multiple target data tables whose foreign key relationships need to be reconstructed are paired to generate column pairs, and a first set is constructed based on all column pairs. On this basis, the target large language model is used to detect foreign key relationships in each column pair, determining whether a foreign key relationship exists, thereby identifying the foreign key relationships between the target data tables. By constructing column pairs, the task of identifying foreign key relationships in data tables is transformed into determining whether two columns in a column pair have a local foreign key relationship, greatly simplifying the decision-making process of the large language model. For foreign key relationship detection of column pairs, the target large language model only needs to output "yes" or "no." Compared to directly using the target large language model to identify foreign key relationships between two data tables, this scheme avoids the "illusion" phenomenon of the model and improves the accuracy of foreign key relationship detection. Furthermore, each interaction of the model only judges the foreign key relationship of a single column pair, significantly reducing the amount of data processed compared to the number of data tables, thus improving the model's interaction efficiency.

[0028] Considering that in foreign key relationships between data tables, the columns of the referenced data table are usually unique keys of that data table, and a data table usually has only one unique key referenced by other data tables, before performing foreign key relationship detection on the column pairs in the first set using the target large language model, the target large language model is used to select the target unique key corresponding to the target data table from all candidate keys of each target data table. Then, based on these selected target unique keys, column pairs whose referenced columns are not any of these target unique keys are deleted from the first set, thus completing the pruning operation on the first set.

[0029] Then, based on all the remaining column pairs in the pruned first set, a second set is constructed. Pruning reduces the amount of data requiring subsequent foreign key association detection, thereby reducing the number of interactions with the target language model and improving the efficiency of foreign key association reconstruction. After pruning the first set, the target language model is used to perform local foreign key association detection on each column pair in the second set. The target language model comprehensively analyzes the data from semantic and data distribution perspectives to determine whether a local foreign key association exists in each column pair. The target language model used in this application can be selected according to actual needs, and this solution does not impose any limitations on it. For example, the DeepSeek model and the GPT model can be used as the target language model.

[0030] Considering the potential for foreign key conflicts between different column pairs, this embodiment, after obtaining all column pairs with local foreign key relationships, also performs a global analysis, traversing all column pairs with foreign key relationships to determine if foreign key conflicts exist. If foreign key conflicts exist between column pairs, some column pairs are deleted using the target large language model to eliminate the conflicts. After eliminating all foreign key conflicts, foreign key relationships are reconstructed for the target data table based on the remaining column pairs with foreign key relationships in the second set.

[0031] In this embodiment, the reconstruction of foreign key relationships for the target data table includes three stages. In the first stage, columns in the target data table are paired to generate multiple column pairs and construct a first set. The first set is pruned using a target large language model to remove column pairs whose referenced columns are not included in all target unique keys, reducing the amount of data to be processed subsequently. In the second stage, the target large language model performs local foreign key relationship detection on the column pairs, efficiently and accurately determining whether foreign key relationships exist between them. In the third stage, the target large language model reduces conflicts between column pairs from an overall structural perspective, ensuring that the reconstructed foreign key relationships maintain logical consistency and integrity.

[0032] This solution is highly versatile and can efficiently generate high-quality, accurate foreign key relationships for data tables. It is suitable for foreign key relationship reconstruction tasks of various types of large data tables, especially for large database tables with large data volumes. Furthermore, this solution can handle empty data tables and address application scenarios with non-standard data syntax and difficult-to-understand data semantics.

[0033] As one implementation of this application, using a target large language model, column pairs whose referenced columns are not included in all target unique keys are deleted from the first set, including: Obtain the minimum unique column combination for each target data table, and determine each minimum unique column combination as a candidate key; the minimum unique column combination is the combination of the smallest columns that can uniquely identify a row of data. Extract the first portion of the table data from each target data table; Based on the minimum unique column combination corresponding to each target data table and the partial table data, a corresponding first prompt word is constructed; the first prompt word includes the following analysis rules: Rule 1: A row of data in a table can be uniquely identified and can be referenced; Rule 2: The earlier a column number appears in the data table, the more likely it is to be referenced; Rule 3: Field names ending with "key" or "id" may be referenced; Rule 4: Data types that are integers or strings may be referenced; Rule 5: Shorter texts, easier for humans to read, are more likely to be cited; Rule Six: The fewer the number of fields, the more likely they are to be referenced; Rule 7: Entities whose business meaning is based on the table they belong to and that conform to the business logic can be referenced; The first prompt word corresponding to each target data table is input into the target large language model, so that the target large language model determines the most likely candidate key to be referenced from all candidate keys corresponding to the target data table based on the analysis rules, and uses it as the target unique key corresponding to the target data table; Iterate through each column pair in the first set and delete column pairs whose referenced columns are not included in all target unique keys.

[0034] In this embodiment, the indirect method of determining foreign key associations simplifies the analysis and decision-making process of the target large language model. However, it also increases the number of interactions with the model and the data processing cost. To reduce the number of model interactions and data processing costs, this embodiment removes column pairs from the first set whose referenced columns are not included in the target unique key through pruning. This effectively reduces the number of interactions with the target large language model and the amount of data processing, thereby improving the efficiency of foreign key association reconstruction.

[0035] Considering that foreign keys in one table often reference unique keys in another table, and that a table typically has only one unique key referenced by other tables, pruning the first set based on unique keys—retaining only column pairs where the referenced column is contained within the target unique key—effectively eliminates column pairs in the first set that cannot have foreign key relationships, reducing the model's data processing cost and interaction frequency. The steps for pruning the first set using the target large language model to remove column pairs where the referenced column is not contained within the target unique key are as follows: (1) Traverse all target data tables and find the corresponding MinUCC (Minimal Unique Column Combination) for each target data table. The minimum unique column combination (i.e., candidate key) is the smallest column combination that can uniquely identify a row of data, and the MinUCC does not contain redundant fields; (2) Since a data table typically has only one unique key that is referenced by other data tables, this embodiment selects the most likely candidate key from all minimal unique column combinations (i.e., candidate keys) through the target large language model and determines it as the target unique key. Specifically, the first part of the table data (e.g., the first 5 rows of table data) is extracted from each target data table. Based on the minimal unique column combination corresponding to each target data table and the extracted table data, the corresponding first prompt word is constructed. For example, based on the minimal unique column combination corresponding to data table A and the first 5 rows of table data of data table A, the first prompt word corresponding to data table A is constructed. By formulating analysis rules through the first prompt word, the target large language model is guided to select the most likely candidate key from the unique column combination and determine it as the target unique key. Thus, the task of identifying unique keys from target data tables by the target large language model is transformed into the task of selecting unique keys. This can further simplify the analysis logic of the large language model, reduce the decision difficulty, reduce the model's illusion output, and improve the accuracy of identifying the target unique key.

[0036] In this embodiment, the target large language model selects the analysis rule for the target unique key based primarily on syntactic features and metadata features. Specifically, syntactic features include: field names ending with id or key, etc.; metadata features include: data type, average length, whether field content is incremental, column cardinality, column position, etc.

[0037] In one embodiment, the first prompt word used to determine the target unique key includes the following analysis rules: 1) It can uniquely identify a row of data in a table and can be referenced; 2) The earlier a column number appears in the table, the more likely it is to be referenced; 3) Fields whose names contain "key" or "id" may be referenced; 4) If the data type is an integer or a string, it may be referenced; 5) The text is relatively short and suitable for human reading; texts that meet this characteristic are more likely to be cited. 6) The fewer fields involved, the higher the probability of it being referenced; 7) Analyzing the meaning of the entities corresponding to the table, the referenced unique keys usually conform to the business logic.

[0038] After selecting the target unique key for each data table using the target large language model, the first set is pruned based on the target unique key, deleting column pairs whose referenced columns are not included in all target unique keys.

[0039] As one embodiment of this application, pruning the first set further includes: Delete column pairs containing empty columns, and delete column pairs corresponding to columns in data tables whose most recent access time is greater than a first threshold from the current time; Check if the data types of the two columns in each column pair are the same, and delete the column pairs corresponding to the two columns with different data types; Delete column pairs containing the following data types: floating-point numbers, booleans, and binary large objects; Determine whether the two columns in each column pair have a parent-child relationship, and delete the column pairs that do not have a parent-child relationship.

[0040] In one embodiment, in addition to pruning the first set using the target large language model, the first set is also filtered and pruned by including dependencies and manually defined rules to remove column pairs that are unlikely to have foreign key associations, thereby further reducing the amount of data to be processed in the subsequent foreign key association detection stage and improving the processing efficiency of the model.

[0041] The first set is pruned using manually defined rules, and the specific steps are as follows: (1) Remove empty data columns and column pairs generated from columns in data tables that have not been accessed for a long time. Specifically, delete all column pairs containing columns that do not contain data, as well as column pairs generated from columns in data tables whose most recent access time is more than a first threshold from the current time. The first threshold can be set according to the needs of the actual application. It is worth noting that in special scenarios, if it is necessary to rebuild foreign key relationships in empty data tables in the database, it is not necessary to remove empty data columns; (2) The data types of two columns with foreign key association are usually the same. In this embodiment, all column pairs in the first set are traversed and the data types of the two columns in the column pair are compared. If the data types are different, the column pair is deleted. (3) In addition, certain special data types are not suitable as objects associated with foreign keys. Therefore, in this embodiment, column pairs containing these special data types are deleted. Specifically, column pairs with data types of floating-point numbers, boolean types, and binary large objects are deleted.

[0042] The value sets of two columns with a foreign key relationship form a parent-child relationship, which is a necessary condition for the two columns to form a foreign key relationship. Based on this, in this embodiment, the first set is pruned using the inclusion dependency relationship between the two columns. The specific steps are as follows: (1) Traverse all remaining column pairs in the first set, check the values ​​of the two columns in each column pair, and determine whether the values ​​of the two columns are in a parent-child relationship; (2) Delete column pairs that are not parent-child pairs.

[0043] By pruning the first set based on manually defined rules and dependency relationships, the amount of data that the target large language model needs to process can be further reduced, the number of interactions of the target large language model can be reduced, and the efficiency of the model in recognizing foreign key associations can be improved.

[0044] As one implementation of this application, foreign key association detection is performed on column pairs in the second set using the target large language model to obtain detection results, including: The target language model is used to determine the types of target information required to reconstruct foreign key relationships for the multiple target data tables. The types of target information include: maximum value, minimum value, number of rows, column number, and average character length. Based on the type of the target information, query the multiple target data tables to obtain the corresponding target information; Based on the target information and each column pair in the second set, a corresponding second prompt word is constructed; Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results.

[0045] Foreign key association identification is closely related to a wealth of information about the data table. For example, it requires obtaining the field names of the data table to identify their semantics, the data distribution, and so on. In this embodiment, when the target large language model detects foreign key associations in column pairs, it also uses information from the data table as auxiliary information to improve the accuracy of the detection results. However, directly inputting the target data table into the target large language model can lead to unsatisfactory detection results due to the contextual limitations of the large language model, performance degradation, and high token costs. Furthermore, each model interaction contains a large amount of invalid information, resulting in low payload per interaction. On the other hand, manually selecting a small amount of information from the target data table to input into the target large language model may miss important data or information, leading to poor foreign key association identification performance.

[0046] Therefore, considering the complexity of foreign key association recognition tasks, this embodiment employs a model self-consistency data augmentation approach to determine the core information required for the model to perform foreign key association detection. In the first stage, the target large language model analyzes the target data table and determines the target information (i.e., core information) required for subsequent foreign key association detection. Then, in the second stage, the target information is used to enhance the understanding of the target large language model. A second prompt word is constructed based on the target information and input into the target large language model to enhance the accuracy of the model's inference results, balancing the processing performance of a single interaction with the recognition effect of foreign key associations.

[0047] Specifically, in one embodiment, a two-stage foreign key association detection method based on model self-consistency is employed. In the first stage, a target large language model analyzes the target data table to determine the type of target information required for foreign key association detection of each column pair. Based on the type of target information required by the model output, the corresponding target information is obtained from the target data table. The types of target information include: maximum / minimum value, row number, column number, average character length, etc. After obtaining the target information, in the second stage, the target information is used to assist the target large language model in foreign key association detection. Specifically, based on the target information and each column pair, a second hint word is constructed for each column pair. Based on each second hint word, the target large language model is guided to infer the existence of foreign key associations in the column pairs by incorporating the target information.

[0048] This embodiment employs a two-stage foreign key association detection method. First, the target large language model analyzes the target data table, outputting the types of target information it needs in subsequent foreign key association detection. Then, the corresponding target information is extracted from the target data table and used as input for the second stage to achieve information augmentation, providing the target large language model with more effective information to support its analysis and reasoning. Through self-consistent data augmentation, the effective payload of a single model interaction is improved, enabling the model to enhance its processing performance in a single interaction and the accuracy and consistency of foreign key association identification through self-feedback and information association.

[0049] As one embodiment of this application, the type of target information required to reconstruct foreign key relationships for the multiple target data tables is determined through the target large language model, including: Based on all column pairs in the second set, and the table data at the beginning of the target data table to which two columns in the column pairs belong, a first data example is constructed; The first data example is input into the target large language model so that the target large language model can analyze the column pair and the corresponding partial table data to obtain the type of target information required for foreign key association detection of the column pair; Based on the type of the target information, the multiple target data tables are queried to obtain the corresponding target information, and the obtained target information is associated with the corresponding column pairs in the second set.

[0050] In one embodiment, the type of target information required for foreign key association detection is determined by a target large language model. First, a column pair from a second set is selected, along with the first portion of the table data (e.g., the first 5 rows) of the target data table corresponding to that column pair, to construct a first data example. This first data example is then input into the target large language model, allowing it to analyze and determine the type of target information required for subsequent foreign key association detection of the column pair. In addition to the table data and column pairs, the target information includes the following: (1) Schema information, such as database name, table name, column name, etc.; (2) Column information: column position in the table, data type of the column, average length of the column, number of distinct values ​​of the column, maximum value, minimum value, etc.; (3) Table information: number of rows in the table, etc.; (4) Potential dependency information between tables: coverage, table size ratio, out of range, etc.

[0051] After the target large language model outputs the type of target information (e.g., minimum value) required to identify the foreign key association of column pairs, the corresponding target data table is queried based on the type of target information to obtain the corresponding target information, which is used as auxiliary information for subsequent foreign key association detection.

[0052] As one embodiment of this application, based on the target information and each column pair in the second set, a corresponding second prompt word is constructed, including: Based on each column pair and its associated target information, a second cue word based on the thought chain is constructed; the second cue word includes the following analysis rules: Rule 1: Determine the semantic validity of foreign key relationships between column pairs based on the semantics of the data table. The higher the semantic validity, the greater the likelihood of a foreign key relationship. Rule 2: Based on syntax rules, determine the similarity between the strings in two columns, and whether the strings have id, key, or no as suffixes. The higher the similarity between the strings, and the greater the possibility of a foreign key relationship if the strings have id, key, or no as suffixes. Rule 3: Based on the data inclusion relationship, coverage, average length difference, and out-of-range ratio of the two columns in the column pair, the possibility of a foreign key relationship is greater if the data in the two columns are parent and child sets, the higher the coverage, the smaller the average length difference, and the lower the out-of-range ratio. Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results, including: The second prompt word for each column pair is input into the target large language model, so that the target large language model can perform a comprehensive analysis of the column pair based on the analysis rules and the associated target information, and generate the corresponding detection results.

[0053] In order to enable the large language model to reason about the foreign key associations of column pairs from a deep semantic perspective, rather than just judging from the surface features of the tabular data, in one embodiment, the target large language model analyzes and reasons about each column pair in the second set in a thought chain manner. Figure 2 This is a flowchart illustrating foreign key association detection using a target large language model in one embodiment of this application. For example... Figure 2 As shown, in one embodiment, a first data example is constructed and input into the target large language model. The target large language model generates the type of target information required to identify the foreign key associations of column pairs, based on the data characteristics of the target data table and the requirements of the foreign key association reconstruction task. Then, based on the type of target information output by the model, the target data table is searched to obtain the corresponding target information. After obtaining the target information, a second cue word based on a thought chain is constructed based on each column pair and its associated target information. By constructing the second cue word based on the thought chain, the target large language model can mimic the step-by-step reasoning process of humans, performing progressive analysis and comprehensive deduction of the foreign key associations of column pairs based on analysis rules from multiple perspectives, thereby obtaining accurate detection results.

[0054] In this embodiment of the application, the second prompt word is constructed based on the following analysis rules: (1) Semantic analysis rules: Based on the meaning and relationship of the entities represented by the two data tables (e.g., the relationship between the two data tables is "has-a" or "is-a"), determine whether there is a foreign key relationship between the column pairs in the business logic. Combine the database design principles and the database background to evaluate the semantic validity of the foreign key relationship; (2) Analysis rules from a syntactic perspective: The higher the string similarity between two columns, the greater the likelihood of a foreign key relationship. In this embodiment, the string similarity of the column names of two columns is analyzed, as well as whether the strings have typical suffixes such as "id", "key", and "no", to determine whether they conform to the syntactic rules of foreign key association. Among column pairs that conform to the above syntactic rules, the two columns may have a foreign key relationship; (3) Data analysis rules: 1) Coverage: In column pairs with foreign key relationships, the referencing column should typically cover the vast majority of values ​​in the referenced column; 2) Out-of-range ratio: The values ​​of the referenced column in the column pair should be evenly distributed within the range of values ​​of the referenced column, rather than covering only a narrow interval; 3) Average length difference: The average length of the referencing column and the referenced column is usually similar; 4) Column value inclusion relationship: Determine whether there is a parent-child relationship between the data values ​​of two columns in a column pair.

[0055] After obtaining the detection results for all column pairs in the second set, the column pairs whose detection results indicate that there is no foreign key association are deleted from the second set.

[0056] This embodiment constructs a second cue word based on a thought chain. The target large language model comprehensively considers multiple analysis rules and analyzes and reasones about the column pairs from different perspectives to comprehensively determine whether there is a foreign key association between the column pairs, thereby further improving the accuracy of the detection results. Furthermore, if reasoning from one perspective fails during the analysis process (e.g., the names of data tables or data columns are unclear, making semantic reasoning impossible), results can still be obtained through reasoning from other perspectives, which can greatly reduce the probability of false positives and improve the robustness of foreign key association detection.

[0057] As one implementation of this application, the foreign key conflict includes: multiple reference conflicts and circular reference conflicts; traversing all column pairs with foreign key associations to determine whether there is a foreign key conflict between each column pair includes: Examine all column pairs with foreign key relationships to determine if there is a case where one column references multiple columns; if there is a case where one column references multiple columns, determine that there is a multi-reference conflict between the column pairs; if there is no case where one column references multiple columns, determine that there is no multi-reference conflict between the column pairs. In the absence of multiple reference conflicts, a directed graph of foreign key associations is constructed based on all column pairs with foreign key associations, and it is determined whether the directed graph has a closed cycle. Each node of the directed graph represents a column. Each edge of the directed graph connects two nodes and represents the reference relationship between two columns. If the directed graph has a closed cycle, it is determined that there is a circular reference conflict between column pairs. If the directed graph does not have a closed cycle, it is determined that there is no circular reference conflict between column pairs.

[0058] In the above embodiments, the target large language model is used to detect foreign key associations in each column pair, obtaining the detection results of whether foreign key associations exist between the two columns in each column pair. However, this detection result is a local foreign key association detection result obtained by the target large language model based on the analysis of a single column pair, and does not consider the overall reference relationships between data tables. In practical applications, mutual references between columns of data tables may lead to foreign key conflicts, including: multiple reference conflicts and circular reference conflicts. Reconstructing foreign key associations in the presence of foreign key conflicts will produce meaningless and erroneous reference relationships, causing logical chaos in the database tables and compromising data integrity and consistency.

[0059] Based on this, in this embodiment, the target large language model is used to eliminate foreign key conflicts between column pairs from a holistic perspective, thereby avoiding the generation of redundant foreign key associations that could lead to logical chaos in the database table and ensuring that the target data table has clear referencing relationships and logical consistency after the foreign key associations are reconstructed. First, multi-reference conflicts and circular reference conflicts between column pairs are identified. The principle of database foreign key associations stipulates that a single column cannot reference multiple columns, and the reference relationship between columns cannot form a closed loop. Based on this, this embodiment iterates through all column pairs in the second set and checks whether there is a case where one column references at least two columns simultaneously. If so, it is determined that there is a multi-reference conflict between the column pairs. Circular reference conflicts are determined by constructing a directed graph and judging whether there is a circular reference conflict between column pairs based on whether the directed graph has a closed loop. If the directed graph has a closed loop, it is determined that there is a circular reference conflict between the column pairs. If the directed graph does not have a closed loop, it is determined that there is no circular reference conflict between the column pairs. Based on this, the target large language model is used to score multiple column pairs with foreign key conflicts from a holistic perspective, and the corresponding foreign key conflicts are eliminated by deleting one or more column pairs.

[0060] In this embodiment, the target large language model considers the foreign key relationships of multiple column pairs and the overall relationships between column pairs from a holistic perspective, and scores each column pair. Compared to scoring only the local relationships of a single column pair, this approach enables the model to learn the overall relationships of the data table, thereby further mitigating the illusion problem of large language models and improving the scoring quality of the model. This, in turn, improves the reconstruction quality of foreign key relationships in the target data table.

[0061] As one implementation of this application, in the case of multiple reference conflicts between column pairs, at least one column pair with foreign key associations is deleted through the target large language model to eliminate foreign key conflicts, including: Based on all column pairs corresponding to each multi-reference conflict, a third cue word based on the thought chain is constructed; the third cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The third prompt word and all column pairs with multiple reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with multiple reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pairs other than the column pair with the highest score as the column pairs that need to be deleted, and output them. Remove the column pair that needs to be deleted from the second set.

[0062] In one embodiment, foreign key conflicts are eliminated through a large language model scoring method. Based on all relevant column pairs with multiple reference conflicts, a third cue word based on thought chains is constructed and input into the target large language model. The target large language model uses the analysis rules in the third cue word as scoring indicators to score the foreign key associations of each column pair with multiple reference conflicts, and determines which column pairs need to be deleted and retained based on their scores. Based on the output of the target large language model, the corresponding column pairs are deleted from the second set, completing the multiple reference conflict elimination operation.

[0063] Specifically, the third cue word includes the following analysis rules: (1) Determine the semantic association strength between the two columns in each column pair. The stronger the semantic association strength, the higher the score of the foreign key association. (2) Determine whether the foreign key association between the two columns in the column pair conforms to the database design practice (i.e., basic principles). The more the column pair conforms to the database design principles, the higher its foreign key association score.

[0064] In this embodiment, the method for eliminating multi-reference conflicts is to have the target large language model select the column pair with the highest score from all column pairs with multi-reference conflicts and retain it, while deleting the other column pairs with lower scores. For example, if there are three column pairs with multi-reference conflicts, it is necessary to delete the two column pairs with lower scores and retain the column pair with the highest score.

[0065]

Example 1

[0066] Conflict description: A foreign key in one table can only reference one column in another table, and cannot reference multiple columns simultaneously.

[0067] Conflicting column pair structures: Ra -> Sc / / Column a in data table R references column c in data table S; Ra -> Tb / / Column a in data table R references column b in data table T; Data from related tables: [Example data for each related table]; Analysis steps: Step 1: Assign a score to each potential foreign key relationship based on the semantic association strength and database design best practices. Specifically, determine the semantic association strength between the two columns in each column pair; the stronger the semantic association, the higher the score for the foreign key relationship. Also, determine whether the foreign key relationship between the two columns in the column pair conforms to database design practices (i.e., basic principles); the more conforming the column pair is to the database design principles, the higher the score for the foreign key relationship. Step 2: Sort all column pairs by score from highest to lowest; Step 3: Identify the column pairs that need to be deleted, except for the column pair with the highest score; Step 4: Output the column pairs to be deleted: {"deleted": "source table.source column->target table.target column"}.

[0068] As one implementation of this application, in the case of circular reference conflicts between column pairs, at least one column pair with foreign key associations is deleted through the target large language model to eliminate foreign key conflicts, including: Based on all column pairs corresponding to each circular reference conflict, a fourth cue word based on the thought chain is constructed; the fourth cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The fourth prompt word and all column pairs with circular reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with circular reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pair with the lowest score as the column pair that needs to be deleted, and output it. Remove the column pair that needs to be deleted from the second set; Based on all column pairs in the current second set, reconstruct the directed graph of foreign key associations to determine whether there are circular reference conflicts between the current column pairs.

[0069] In one embodiment, a directed graph is constructed based on the column pairs in the second set, provided there are no multi-reference conflicts between column pairs, or all multi-reference conflicts have been eliminated. Specifically, "table_name.column_name" is treated as a node in the graph, and directed edges represent the reference relationships between two nodes (i.e., columns). The DFS (Depth-First Search) algorithm is used to detect loop closures in the directed graph, finding the nodes currently forming loops, i.e., obtaining all column pairs with circular reference conflicts.

[0070] After obtaining all column pairs with circular reference conflicts, a fourth cue word based on the thought chain is constructed and input into the target large language model. The target large language model uses the analysis rules in the fourth cue word as scoring indicators to score the foreign key associations of each column pair with circular reference conflicts, and determines which column pairs need to be deleted and retained based on the scores of each column pair. Based on the output of the target large language model, the corresponding column pairs are deleted from the second set, completing the circular reference conflict elimination operation.

[0071] Specifically, the fourth cue word includes the following analysis rules: (1) Determine the semantic association strength between the two columns in each column pair. The stronger the semantic association strength, the higher the score of the foreign key association. (2) Determine whether the foreign key association between the two columns in the column pair conforms to the database design practice (i.e., basic principles). The more the column pair conforms to the database design principles, the higher its foreign key association score.

[0072] In this embodiment, the circular reference conflict elimination operation involves having the target large language model determine the column pair with the lowest score, and then deleting that column pair from the second set to break the reference loop of the node. For example, in the case of circular reference conflicts among 5 column pairs, only the column pair with the lowest score needs to be deleted to break the reference loop, while the remaining 4 column pairs are retained.

[0073]

Example 2

[0074] Conflict description: Foreign key references between data tables cannot form a circular reference.

[0075] Conflicting column pair structures: Ra -> Sc / / Column a in data table R references column c in data table S; Sc -> Tb / / Column c in data table S references column b in data table T; Tb -> Ra / / Column B in data table T references column a in data table R; Data from related tables: [Example data for each related table]; Analysis steps: Step 1: Assign a score to each potential foreign key relationship based on the semantic association strength and database design best practices. Specifically, determine the semantic association strength between the two columns in each column pair; the stronger the semantic association, the higher the score for the foreign key relationship. Also, determine whether the foreign key relationship between the two columns in the column pair conforms to database design practices (i.e., basic principles); the more conforming the column pair is to the database design principles, the higher the score for the foreign key relationship. Step 2: Sort all column pairs by score from highest to lowest; Step 3: Identify the column pairs with the lowest scores as the column pairs that need to be deleted; Step 4: Output the column pairs to be deleted: {"deleted": "source table.source column->target table.target column"}.

[0076] Furthermore, due to the potential shared edges of circular references—meaning a column pair may exist in two reference cycles simultaneously—breaking one reference cycle may also break other reference cycles sharing edges. Therefore, to avoid accidentally deleting column pairs that do not conflict with circular references, this embodiment uses an iterative approach to eliminate circular reference conflicts. After the target large language model outputs a column pair that needs to be deleted and removes it from the second set, the directed graph is reconstructed based on the remaining column pairs in the second set, and cycle detection is performed until all circular reference conflicts are eliminated.

[0077] In this embodiment, by eliminating multiple reference conflicts and circular reference conflicts from a holistic perspective, high-quality column pairs with accurate reference relationships are retained in the second set. Finally, by reconstructing the foreign key relationships of the target data table based on the column pairs retained in the second set, a clear, logically consistent, and high-quality data table reference relationship can be obtained.

[0078] Figure 3 This is a flowchart illustrating the process of eliminating foreign key conflicts using a target large language model in one embodiment of this application. Figure 3As shown, the algorithm iterates through all column pairs in the second set, determining from a holistic perspective whether a column currently contains at least two references. If such a situation exists, a multi-reference conflict is identified. After detecting all multi-reference conflicts, the target large language model identifies the column pairs that need to be deleted, and these pairs are removed from the second set to eliminate the multi-reference conflict. If no multi-reference conflicts exist between current column pairs, or if all multi-reference conflicts have been eliminated, a directed graph is constructed based on all column pairs in the current second set to detect circular reference conflicts. Each node in the directed graph represents a column, and each edge represents the reference relationship between two columns. The direction of the edge indicates the "referenced" and "referenced" state of the node. After each construction of the directed graph, the algorithm checks for closed cycles. If a closed cycle exists, a circular reference conflict is identified between current column pairs. Based on this, the target large language model is used to eliminate the circular reference conflict, identifying the column pairs that need to be deleted and removing them from the second set to break the current circular reference. Considering that each deletion of a column pair may change multiple reference cycles simultaneously, the circular reference conflict elimination operation is performed iteratively. After each column pair deletion breaks the circular reference, the directed graph is reconstructed based on all the column pairs retained in the second set, and loop closure detection is performed.

[0079] Based on the same inventive concept, one embodiment of this application provides a database table foreign key association reconstruction device. Figure 4 This is a schematic diagram of a database table foreign key association reconstruction device 100 according to an embodiment of this application. Figure 4 As shown, the device includes: Preprocessing module 101 is configured to combine columns from multiple target data tables in pairs to generate column pairs, construct a first set based on all column pairs, and prune the first set, including: deleting column pairs whose referenced columns are not included in all target unique keys from the first set using a target large language model; the target unique key is: the most likely candidate key to be referenced determined from all candidate keys corresponding to the target data table; and constructing a second set based on the column pairs retained after pruning. The association recognition module 102 is configured to perform foreign key association detection on column pairs in the second set using the target large language model, and obtain a detection result; the detection result is: the column pair has a foreign key association, or the column pair does not have a foreign key association; The conflict handling module 103 is configured to traverse all column pairs with foreign key associations, determine whether there is a foreign key conflict between each column pair, and delete at least one column pair with a foreign key association through the target large language model to eliminate the foreign key conflict. The reconstruction module 104 is configured to rebuild foreign key relationships for the multiple target data tables based on all column pairs that currently have foreign key relationships and do not have foreign key conflicts, provided that there are no foreign key conflicts between column pairs.

[0080] As one embodiment of this application, the preprocessing module 101 is configured to delete column pairs whose referenced columns are not included in all target unique keys from the first set using a target large language model, including: Obtain the minimum unique column combination for each target data table, and determine each minimum unique column combination as a candidate key; the minimum unique column combination is the combination of the smallest columns that can uniquely identify a row of data. Extract the first portion of the table data from each target data table; Based on the minimum unique column combination corresponding to each target data table and the partial table data, a corresponding first prompt word is constructed; the first prompt word includes the following analysis rules: Rule 1: A row of data in a table can be uniquely identified and can be referenced; Rule 2: The earlier a column number appears in the data table, the more likely it is to be referenced; Rule 3: Field names ending with "key" or "id" may be referenced; Rule 4: Data types that are integers or strings may be referenced; Rule 5: Shorter texts, easier for humans to read, are more likely to be cited; Rule Six: The fewer the number of fields, the more likely they are to be referenced; Rule 7: Entities whose business meaning is based on the table they belong to and that conform to the business logic can be referenced; The first prompt word corresponding to each target data table is input into the target large language model, so that the target large language model determines the most likely candidate key to be referenced from all candidate keys corresponding to the target data table based on the analysis rules, and uses it as the target unique key corresponding to the target data table; Iterate through each column pair in the first set and delete column pairs whose referenced columns are not included in all target unique keys.

[0081] In one embodiment of this application, the association identification module 102 is configured to perform foreign key association detection on column pairs in the second set using the target large language model, and obtain detection results, including: The target language model is used to determine the types of target information required to reconstruct foreign key relationships for the multiple target data tables. The types of target information include: maximum value, minimum value, number of rows, column number, and average character length. Based on the type of the target information, query the multiple target data tables to obtain the corresponding target information; Based on the target information and each column pair in the second set, a corresponding second prompt word is constructed; Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results.

[0082] As one embodiment of this application, the association identification module 102 is configured to determine, through the target large language model, the type of target information required to reconstruct foreign key associations for the multiple target data tables, including: Based on all column pairs in the second set, and the table data at the beginning of the target data table to which two columns in the column pairs belong, a first data example is constructed; The first data example is input into the target large language model so that the target large language model can analyze the column pair and the corresponding partial table data to obtain the type of target information required for foreign key association detection of the column pair; Based on the type of the target information, the multiple target data tables are queried to obtain the corresponding target information, and the obtained target information is associated with the corresponding column pairs in the second set.

[0083] As one embodiment of this application, the association recognition module 102 is configured to construct a corresponding second prompt word based on the target information and each column pair in the second set, including: Based on each column pair and its associated target information, a second cue word based on the thought chain is constructed; the second cue word includes the following analysis rules: Rule 1: Determine the semantic validity of foreign key relationships between column pairs based on the semantics of the data table. The higher the semantic validity, the greater the likelihood of a foreign key relationship. Rule 2: Based on syntax rules, determine the similarity between the strings in two columns, and whether the strings have id, key, or no as suffixes. The higher the similarity between the strings, and the greater the possibility of a foreign key relationship if the strings have id, key, or no as suffixes. Rule 3: Based on the data inclusion relationship, coverage, average length difference, and out-of-range ratio of the two columns in the column pair, the possibility of a foreign key relationship is greater if the data in the two columns are parent and child sets, the higher the coverage, the smaller the average length difference, and the lower the out-of-range ratio. Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results, including: The second prompt word for each column pair is input into the target large language model, so that the target large language model can perform a comprehensive analysis of the column pair based on the analysis rules and the associated target information, and generate the corresponding detection results.

[0084] As one embodiment of this application, the foreign key conflict includes: multiple reference conflicts and circular reference conflicts; the conflict handling module 103 is configured to traverse all column pairs with foreign key associations and determine whether there is a foreign key conflict between each column pair, including: Examine all column pairs with foreign key relationships to determine if there is a case where one column references multiple columns; if there is a case where one column references multiple columns, determine that there is a multi-reference conflict between the column pairs; if there is no case where one column references multiple columns, determine that there is no multi-reference conflict between the column pairs. In the absence of multiple reference conflicts, a directed graph of foreign key associations is constructed based on all column pairs with foreign key associations, and it is determined whether the directed graph has a closed cycle. Each node of the directed graph represents a column. Each edge of the directed graph connects two nodes and represents the reference relationship between two columns. If the directed graph has a closed cycle, it is determined that there is a circular reference conflict between column pairs. If the directed graph does not have a closed cycle, it is determined that there is no circular reference conflict between column pairs.

[0085] As one embodiment of this application, the conflict handling module 103 is configured to, in the event of multiple reference conflicts between column pairs, delete at least one column pair with foreign key associations through the target large language model to eliminate foreign key conflicts, including: Based on all column pairs corresponding to each multi-reference conflict, a third cue word based on the thought chain is constructed; the third cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The third prompt word and all column pairs with multiple reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with multiple reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pairs other than the column pair with the highest score as the column pairs that need to be deleted, and output them. Remove the column pair that needs to be deleted from the second set.

[0086] As one embodiment of this application, the conflict handling module 103 is configured to, in the event of a circular reference conflict between column pairs, delete at least one column pair with a foreign key association through the target large language model to eliminate the foreign key conflict, including: Based on all column pairs corresponding to each circular reference conflict, a fourth cue word based on the thought chain is constructed; the fourth cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The fourth prompt word and all column pairs with circular reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with circular reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pair with the lowest score as the column pair that needs to be deleted, and output it. Remove the column pair that needs to be deleted from the second set; Based on all column pairs in the current second set, reconstruct the directed graph of foreign key associations to determine whether there are circular reference conflicts between the current column pairs.

[0087] In one embodiment of this application, the preprocessing module 101 is further configured to perform the following steps: Delete column pairs containing empty columns, and delete column pairs corresponding to columns in data tables whose most recent access time is greater than a first threshold from the current time; Check if the data types of the two columns in each column pair are the same, and delete the column pairs corresponding to the two columns with different data types; Delete column pairs containing the following data types: floating-point numbers, booleans, and binary large objects; Determine whether the two columns in each column pair have a parent-child relationship, and delete the column pairs that do not have a parent-child relationship.

[0088] Regarding the apparatus in the above embodiments, the specific methods by which each module performs its operations have been described in detail in the embodiments related to the method, and will not be elaborated upon here. The database table foreign key association reconstruction apparatus in the embodiments of this application can be an apparatus, or a component, integrated circuit, or chip in a terminal. The apparatus can be a mobile electronic device or a non-mobile electronic device. For example, mobile electronic devices can be mobile phones, tablets, laptops, handheld computers, in-vehicle electronic devices, wearable devices, ultra-mobile personal computers (UMPCs), netbooks, or personal digital assistants (PDAs), etc., while non-mobile electronic devices can be servers, network attached storage (NAS), personal computers (PCs), televisions (TVs), ATMs, or self-service machines, etc., and the embodiments of this application do not specifically limit the scope.

[0089] The above description is merely a preferred embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

[0090] For the sake of simplicity, the method embodiments are described as a series of actions. However, those skilled in the art should understand that this application is not limited to the described order of actions, as some steps may be performed in other orders or simultaneously according to this application. Furthermore, those skilled in the art should also understand that the embodiments described in the specification are preferred embodiments, and the actions and components involved are not necessarily essential to this application.

[0091] Those skilled in the art will understand that embodiments of this application can be provided as methods, apparatus, or computer program products. Therefore, embodiments of this application can take the form of entirely hardware embodiments, entirely software embodiments, or embodiments combining software and hardware aspects. Furthermore, embodiments of this application can take the form of computer program products implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0092] This application describes embodiments with reference to flowchart illustrations and / or block diagrams of methods, terminal devices (systems), and computer program products according to embodiments of this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing terminal device to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing terminal device, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0093] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing terminal device to operate in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0094] These computer program instructions can also be loaded onto a computer or other programmable data processing terminal equipment, causing a series of operational steps to be performed on the computer or other programmable terminal equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable terminal equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0095] Although preferred embodiments of the embodiments of this application have been described, those skilled in the art, once they understand the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, this application is to be interpreted as including the preferred embodiments as well as all changes and modifications falling within the scope of the embodiments of this application.

[0096] Finally, it should be noted that in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or terminal device that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or terminal device. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or terminal device that includes said element.

[0097] The database table foreign key association reconstruction method and apparatus provided in this application have been described in detail above. Specific examples have been used to illustrate the principles and implementation methods of this application. The description of the above embodiments is only for the purpose of helping to understand the method and its core ideas. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this application. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A method for reconstructing foreign key associations in a database table, characterized in that, include: Columns from multiple target data tables are combined in pairs to generate column pairs, and a first set is constructed based on all column pairs; Pruning the first set includes: using the target large language model, deleting column pairs from the first set whose referenced columns are not included in all target unique keys; the target unique key is: the most likely candidate key to be referenced determined from all candidate keys corresponding to the target data table; constructing a second set based on the column pairs retained after pruning; The target large language model is used to detect foreign key associations in the column pairs of the second set, and the detection results are obtained. The detection results are: the column pairs have foreign key associations, or the column pairs do not have foreign key associations. Iterate through all column pairs with foreign key relationships to determine whether there are foreign key conflicts between the column pairs; In the event of foreign key conflicts between column pairs, at least one column pair with a foreign key relationship is deleted through the target large language model to eliminate the foreign key conflict; If there are no foreign key conflicts between column pairs, rebuild the foreign key relationships for the multiple target data tables based on all column pairs that currently have foreign key relationships and do not have foreign key conflicts.

2. The database table foreign key association reconstruction method according to claim 1, characterized in that, Using the target large language model, remove column pairs from the first set whose referenced columns are not included in all target unique keys, including: Obtain the minimum unique column combination for each target data table, and determine each minimum unique column combination as a candidate key; the minimum unique column combination is the combination of the smallest columns that can uniquely identify a row of data. Extract the first portion of the table data from each target data table; Based on the minimum unique column combination corresponding to each target data table and the partial table data, a corresponding first prompt word is constructed; the first prompt word includes the following analysis rules: Rule 1: A row of data in a table can be uniquely identified and can be referenced; Rule 2: The earlier a column number appears in the data table, the more likely it is to be referenced; Rule 3: Field names ending with "key" or "id" may be referenced; Rule 4: Data types that are integers or strings may be referenced; Rule 5: Shorter texts, easier for humans to read, are more likely to be cited; Rule Six: The fewer the number of fields, the more likely they are to be referenced; Rule 7: Entities whose business meaning is based on the table they belong to and that conform to the business logic can be referenced; The first prompt word corresponding to each target data table is input into the target large language model, so that the target large language model determines the most likely candidate key to be referenced from all candidate keys corresponding to the target data table based on the analysis rules, and uses it as the target unique key corresponding to the target data table; Iterate through each column pair in the first set and delete column pairs whose referenced columns are not included in all target unique keys.

3. The database table foreign key association reconstruction method according to claim 1, characterized in that, Foreign key association detection is performed on column pairs in the second set using the target large language model, and the detection results are obtained, including: The target language model is used to determine the types of target information required to reconstruct foreign key relationships for the multiple target data tables. The types of target information include: maximum value, minimum value, number of rows, column number, and average character length. Based on the type of the target information, query the multiple target data tables to obtain the corresponding target information; Based on the target information and each column pair in the second set, a corresponding second prompt word is constructed; Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results.

4. The database table foreign key association reconstruction method according to claim 3, characterized in that, Based on the target large language model, the types of target information required to reconstruct foreign key relationships for the multiple target data tables are determined, including: Based on all column pairs in the second set, and the table data at the beginning of the target data table to which two columns in the column pairs belong, a first data example is constructed; The first data example is input into the target large language model so that the target large language model can analyze the column pair and the corresponding partial table data to obtain the type of target information required for foreign key association detection of the column pair; Based on the type of the target information, the multiple target data tables are queried to obtain the corresponding target information, and the obtained target information is associated with the corresponding column pairs in the second set.

5. The database table foreign key association reconstruction method according to claim 3, characterized in that, Based on the target information and each column pair in the second set, a corresponding second prompt word is constructed, including: Based on each column pair and its associated target information, a second cue word based on the thought chain is constructed; the second cue word includes the following analysis rules: Rule 1: Determine the semantic validity of foreign key relationships between column pairs based on the semantics of the data table. The higher the semantic validity, the greater the likelihood of a foreign key relationship. Rule 2: Based on syntax rules, determine the similarity between the strings in two columns, and whether the strings have id, key, or no as suffixes. The higher the similarity between the strings, and the greater the possibility of a foreign key relationship if the strings have id, key, or no as suffixes. Rule 3: Based on the data inclusion relationship, coverage, average length difference, and out-of-range ratio of the two columns in the column pair, the possibility of a foreign key relationship is greater if the data in the two columns are parent and child sets, the higher the coverage, the smaller the average length difference, and the lower the out-of-range ratio. Based on the second prompt word, foreign key association detection is performed on each column pair using the target large language model to obtain the detection results, including: The second prompt word for each column pair is input into the target large language model, so that the target large language model can perform a comprehensive analysis of the column pair based on the analysis rules and the associated target information, and generate the corresponding detection results.

6. The database table foreign key association reconstruction method according to claim 1, characterized in that, The foreign key conflicts include: multiple reference conflicts and circular reference conflicts; traversing all column pairs with foreign key associations to determine whether there are foreign key conflicts between each column pair, including: Examine all column pairs with foreign key relationships to determine if there is a case where one column references multiple columns; if there is a case where one column references multiple columns, determine that there is a multi-reference conflict between the column pairs; if there is no case where one column references multiple columns, determine that there is no multi-reference conflict between the column pairs. In the absence of multiple reference conflicts, a directed graph of foreign key associations is constructed based on all column pairs with foreign key associations, and it is determined whether the directed graph has a closed cycle. Each node of the directed graph represents a column. Each edge of the directed graph connects two nodes and represents the reference relationship between two columns. If the directed graph has a closed cycle, it is determined that there is a circular reference conflict between column pairs. If the directed graph does not have a closed cycle, it is determined that there is no circular reference conflict between column pairs.

7. The database table foreign key association reconstruction method according to claim 6, characterized in that, In cases where multiple reference conflicts exist between column pairs, at least one column pair with a foreign key relationship is deleted through the target large language model to eliminate the foreign key conflict, including: Based on all column pairs corresponding to each multi-reference conflict, a third cue word based on the thought chain is constructed; the third cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The third prompt word and all column pairs with multiple reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with multiple reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pairs other than the column pair with the highest score as the column pairs that need to be deleted, and output them. Remove the column pair that needs to be deleted from the second set.

8. The database table foreign key association reconstruction method according to claim 6, characterized in that, In the event of a circular reference conflict between column pairs, at least one column pair with a foreign key relationship is deleted through the target large language model to eliminate the foreign key conflict, including: Based on all column pairs corresponding to each circular reference conflict, a fourth cue word based on the thought chain is constructed; the fourth cue word includes the following analysis rules: Rule 1: The score of the column pair is determined based on the semantic relationship strength between the two columns. The stronger the semantic relationship, the higher the score of the column pair. Rule 2: Based on database design practices, the more the foreign key relationship between two columns conforms to database design practices, the higher the score of the column pair. The fourth prompt word and all column pairs with circular reference conflicts are input into the target large language model, so that the target large language model performs the following steps: analyze each column pair with circular reference conflicts and generate corresponding scores; sort each column pair according to the scores from high to low, determine the column pair with the lowest score as the column pair that needs to be deleted, and output it. Remove the column pair that needs to be deleted from the second set; Based on all column pairs in the current second set, reconstruct the directed graph of foreign key associations to determine whether there are circular reference conflicts between the current column pairs.

9. The method for reconstructing foreign key associations in a database table according to claim 1, characterized in that, Pruning the first set also includes: Delete column pairs containing empty columns, and delete column pairs corresponding to columns in data tables whose most recent access time is greater than a first threshold from the current time; Check if the data types of the two columns in each column pair are the same, and delete the column pairs corresponding to the two columns with different data types; Delete column pairs containing the following data types: floating-point numbers, booleans, and binary large objects; Determine whether the two columns in each column pair have a parent-child relationship, and delete the column pairs that do not have a parent-child relationship.

10. A database table foreign key association reconstruction device, characterized in that, For performing the method as described in any one of claims 1-9, comprising: The preprocessing module is configured to combine columns from multiple target data tables in pairs to generate column pairs, construct a first set based on all column pairs, and prune the first set, including: deleting column pairs from the first set whose referenced columns are not included in all target unique keys using the target large language model; the target unique key is: the most likely candidate key to be referenced determined from all candidate keys corresponding to the target data table; and constructing a second set based on the column pairs retained after pruning. The association recognition module is configured to perform foreign key association detection on column pairs in the second set using the target large language model, and obtain the detection result; the detection result is: the column pair has a foreign key association, or the column pair does not have a foreign key association; The conflict handling module is configured to traverse all column pairs with foreign key associations, determine whether there are foreign key conflicts between the column pairs, and delete at least one column pair with foreign key associations through the target large language model to eliminate the foreign key conflict. The reconstruction module is configured to rebuild foreign key relationships for the multiple target data tables based on all existing column pairs that have foreign key relationships and do not have foreign key conflicts, provided that there are no foreign key conflicts between column pairs.

Images