System and method for constraint checking bulk data in a database

Abstract

In a relational database management system (RDMS), a method and system for constraint checking of bulk data after storage in a base table. This invention generates a "dummy" INSERT inquiry to cause the compiler constraint rule engine to generate code for checking for constraint violations. After construction of a Query Graph Model by the compiler, a logic module replaces the INSERT inquiry with a SELECT inquiry to create code that when executed will select records from the bulk data table. Constraint violations are handled in several ways by this invention, including merely reporting that a constraint error exists or listing those records that violate constraint conditions. The logic also enforces referential integrity constraints by applying the SQL CASCADE command to the violating records to expand the violation list to include children records that would be orphaned when their parent records are later removed.

Claims

What is claimed is:

1. In a relational database management system (RDMS) having a query compiler with a constraint engine and memory including mass storage for storing a database that has at least one table T.sub.i containing a plurality of data records, each data record being uniquely identifiable in the RDMS, wherein the RDMS system further has a data processor for processing queries represented by a query graph, a method for checking for constraint violations in a data record in the table T.sub.i, the method comprising the steps of:

generating an insert query Q.sub.MAIN for inserting one or more records into the table T.sub.i ;

compiling the insert query Q.sub.MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one or more records specified by the insert query Q.sub.MAIN ;

modifying the query Q.sub.MAIN by replacing the insert code with compiled select commands to produce modified code having select code and the checking code; and,

constraint checking each record in the table T.sub.i with the modified code.

2. The method of claim 1, wherein the modified code reports at least one constraint violation in an error reporting data message.

3. The method of claim 2, wherein the modified code identifies and reports all the records in the table T.sub.i that exhibit constraint violations.

4. The method of claim 3, wherein the modified code reports a string for each record in table T.sub.i exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

5. The method of claim 4, wherein the RDMS further includes an exception table stored in memory and further comprising the step of:

constructing an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E.sub.i and also to place each corresponding string containing concatenated constraint names in the exception table E.sub.i.

6. The method of claim 5, wherein the constructed insert command is adapted to place a timestamp to indicate the time of error in the exception table E.sub.i.

7. The method of claim 5, wherein the constructed insert command is executed, thereby causing the exception table E.sub.i to be populated with the column entries of records exhibiting a constraint violation, and an error reporting message for each record exhibiting a constraint violation.

8. The method of claim 6, wherein the constructed insert command is executed, thereby causing the exception table E.sub.i to be populated with the column entries of records exhibiting a constraint violation, an error reporting message for each record exhibiting a constraint violation, and the timestamp for each record exhibiting a constraint violation.

9. The method of claim 5, and further comprising the steps of fixing the table T.sub.i to eliminate violating records by:

(a) selecting each record in the table T.sub.i that is identified as exhibiting a constraint violation; and

(b) deleting each record selected in step 9(a).

10. The method of claim 9, and further comprising the step of creating a record insert command to enable inserting a copy of any records deleted in step 9(b) in the exception table E.sub.i.

11. The method of claim 8, and further comprising the steps of fixing the table T.sub.i to eliminate violating records by:

(a) selecting each record in the table T.sub.i that is identified as exhibiting a constraint violation; and

(b) deleting each record selected in step 11(a).

12. The method of claim 11, and further comprising the step of executing the record insert command, thereby populating the exception table E.sub.i with a copy of records deleted in step 9(b).

13. The method of claim 12, wherein any deleted parent records from T.sub.1 are used to identify any child record that has been orphaned by the deletion of their parent records in the table T.sub.i and those identified orphaned child records are deleted by issuing a cascade delete command.

14. The method of claim 13, wherein a copy of each deleted orphaned child record is inserted in the exception table E.sub.i.

15. In a relational database management system (RDMS) having a compiler with a constraint engine and memory including mass storage, for storing a database that has at least one table T.sub.i containing a plurality of data records, each data record being uniquely identifiable in the RDMS, wherein the RDMS system further has a data processor for processing queries represented by a query graph, and the RDMS system further includes an exception table E.sub.i stored in memory, a method for checking for constraint violations in a data record in the table T.sub.i, the method comprising the steps of:

generating an insert query Q.sub.MAIN for inserting one or more records into the table T.sub.i ;

compiling the insert query Q.sub.MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one or more records specified by the insert query Q.sub.MAIN ;

modifying the query Q.sub.MAIN by replacing the insert code with compiled select commands producing modified code having select code and the checking code thereby enabling constraint checking for each record in the table T.sub.i,

executing the modified code over the table T.sub.i to report at least one constraint violation and to identify all the records in the table T.sub.i that exhibit constraint violations in an error reporting data message; and

constructing an insert command to place the error reporting message and any column entries of records exhibiting a constraint violation in the exception table E.sub.i.

16. The method of claim 15, wherein the constructed insert command is executed, thereby causing the exception table E.sub.i to be populated with the column entries of records exhibiting a constraint violation, and an error reporting message for each record exhibiting a constraint violation.

17. The method of claim 16, and further comprising the steps of fixing the table T.sub.i to eliminate violating records by:

(a) selecting a record in the table T.sub.i that is identified as exhibiting a constraint violation; and

(b) deleting each record selected in step 16(a).

18. A bulk-data constraint checking system in a relational database management system (RDMS) having memory including mass storage for storing a database that has at least one table T.sub.i containing a plurality of data records, each data record being uniquely identifiable in the RDMS, wherein the RDMS system further has a data processor for processing queries represented by a query graph, the system comprising:

insert query generating means coupled to said data processor for generating an insert query Q.sub.MAIN with one or more records to be inserted into the table T.sub.i ;

a constraint compiler with a constraint engine for compiling the insert query Q.sub.MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one more records specified by the insert query Q.sub.MAIN ;

query modifying means coupled with the data processor for modifying the query Q.sub.MAIN by replacing the insert code with compiled select commands to produce modified code having select code and the checking code; and,

select and constraint checking command execution means coupled with the data processor for executing the modified code over the table T.sub.i to report at least one constraint violation in an error reporting data message.

19. The bulk-data constraint checking system of claim 18, wherein the modified code identifies and reports all the records in the table T.sub.i that exhibit constraint violations.

20. The bulk-data constraint checking system of claim 19, wherein the modified code reports a string for each record in the table T.sub.i exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

21. The bulk-data constraint checking system of claim 20, wherein the RDMS further includes an exception table E.sub.i stored in memory, and the checking system further comprises:

command generating means coupled to the data processor for constructing an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E.sub.i, and also to place each corresponding string containing concatenated constraint names in the exception table E.sub.i.

22. The bulk-data constraint checking system of claim 21, and further comprising means for fixing the table T.sub.i to eliminate violating records including:

(a) selection means coupled to the data processor for selecting each record in the table T.sub.i as exhibiting a constraint violation; and

(b) record deletion means coupled to the data processor for deleting any record selected by the selection means.

23. The bulk-data constraint checking system of claim 22, and further comprising means for creating a record insert command to enable inserting in the exception table E.sub.i a copy of any records deleted by the record deletion means.

24. A database processing system comprising:

memory including a data store for storing at least one table T.sub.i containing a plurality of data records, each data record being uniquely identifiable in the system;

a data processor coupled to said data store for processing queries represented by query graphs;

insert query generating means coupled to said data processor for generating an insert query Q.sub.MAIN with one or more records to be inserted into the table T.sub.i ;

a constraint compiler with a constraint engine for compiling the insert query Q.sub.MAIN to produce compiled insert commands including insert code and checking code generated by the compiler's constraint engine to check for constraint violations in the records of the insert query Q.sub.MAIN ;

query modifying means coupled with the data processor for modifying the query Q.sub.MAIN by replacing the insert code with compiled select commands to produce modified code having select code and the checking code generated by the compiler's constraint engine; and,

select and constraint checking command execution means coupled with the data processor for executing the modified code over the table T.sub.i to report at least one constraint violation in an error reporting data message.

25. The database processing system of claim 24, wherein the modified code identifies and reports all the records in the table T.sub.i that exhibit constraint violations.

26. The database processing system of claim 25, wherein the modified code reports a string for each record exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

27. The database processing system of claim 26, further including:

an exception table E.sub.i stored in memory; and

command generating means coupled to the data processor for constructing an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E.sub.i, and also to place each corresponding string containing concatenated constraint names in the exception table E.sub.i.

28. The database processing system of claim 27, and further comprising fix-up means for fixing the table T.sub.i to eliminate violating records including:

(a) selection means coupled to the data processor for selecting each record in the table T.sub.i identified as exhibiting a constraint violation; and

(b) record deletion means coupled to the data processor for deleting any record selected by the selection means.

29. The database processing system of claim 28, and further comprising means for creating a record insert command to enable inserting a copy of any records deleted from the exception table E.sub.i.

30. A computer program product, for use with a relational database processing system having a compiler with a constraint engine and memory including mass storage, and also having a database stored in the mass storage that has at least one table T.sub.i containing a plurality of data records, each data record being uniquely identifiable in the system, wherein the relational database processing system further has a data processor for processing queries represented by a query graph, a method for checking for constraint violations in a data record of the plurality of data records in the table T.sub.i by using the compiler constraint engine, the computer program product comprising:

a recording medium;

means, recorded on the recording medium, for directing the data processor to generate an insert query Q.sub.MAIN with one or more records to be inserted into the table T.sub.i ;

means, recorded on the recording medium, for directing the compiler to compile the insert query Q.sub.MAIN to produce compiled insert commands including insert code and checking code to check for constraint violations in one or more records specified by the insert query Q.sub.MAIN ;

means, recorded on the recording medium, for directing the data processor to modify the query Q.sub.MAIN by replacing the insert code with compiled select commands to produce modified code having checking code generated by the compiler's constraint engine; and,

means, recorded on the recording medium, for directing the data processor to execute the modified code over the table T.sub.i to report at least one constraint violation in an error reporting data message.

31. The computer program product of claim 30, wherein the modified code identifies and reports all the records in the table T.sub.i that exhibit constraint violations.

32. The computer program product of claim 30, wherein the modified code reports a string for each record exhibiting a constraint violation, the string containing concatenated constraint names, each constraint name of the concatenated constraint names corresponding to a constraint violation exhibited by the record.

33. The computer program product of claim 31, wherein the system further includes an exception table E.sub.i in memory, and the computer program product further comprises:

insert command generating means recorded on the recording medium for directing the data processor to construct an insert command to place the record identifier for each record exhibiting a constraint violation in the exception table E.sub.i, and also to place each corresponding string containing concatenated constraint names in the exception table E.sub.i.

34. The computer program product of claim 33, and further comprising means, recorded on the recording medium for fixing the table T.sub.i to eliminate violating records including:

(a) selection means for directing the data processor to select each record in the table T.sub.i identified as exhibiting a constraint violation; and

(b) record deletion means for directing the data processor to delete any record selected by the selection means.

35. The computer program product of claim 33, and further comprising means, recorded on the recording medium for deleting records from the exception table, and for directing the data processor to create a record insert command to enable inserting a copy of any records deleted from the exception table E.sub.i.

Description

BACKGROUND OF THE INVENTION

1. Field of the Invention

This invention relates generally to Relational Database Processing Systems, and in particular, to constraint checking and violation capture for bulk data stored in a relational database.

2. Description of the Related Art

A relational database management system (RDMS) uses relational techniques for storing and retrieving data. Relational databases are computerized information storage and retrieval systems in which data in the form of tables ("relations") are typically stored for use on disk drives or similar mass data stores. A "relation" includes a set of rows ("tuples" or "records") spanning one or more columns. A "record" expresses a mathematical relation between its column elements. Reference is made to C. J. Date, An Introduction to Database Systems, vol. 1, 4th edition, Addison-Wesley Publishing Co. Reading, Mass. (1986) for a description of a relational database management system.

An RDMS receives and executes commands to store, retrieve and delete data using high-level query languages such as the Structured Query Language (SQL). The term "query" means a set of commands for acting on data in a stored database. An SQL standard has been maintained by the International Standards Organization (ISO) since 1986. Reference is also made to the SQL-92 standard "Database Language SQL" published by the American National Standards Institute (ANSI) as ANSI X3.135-1992 and published by the ISO as ISO/IEC 9075:1992 for the official specification of the 1992 version of the Structured Query Language. See also James R. Groff et al. (LAN Times Guide to SQL, Osborne McGraw-Hill, Berkeley, Calif. 1994) for a description of SQL-92.

A table in an RDMS is partitioned into rows and columns such that there is one value at each intersection of a row and column. All of the values in a column are of the same data type. The only exception to this rule is that a value could be NULL. A NULL is a marker used to fill a place in a column where data is missing for some reason.

Tables are created explicitly by using the SQL CREATE TABLE command. A table may be created as "permanent", "temporary", or "virtual". Permanent tables include the base tables that contain the fundamental data that is permanently stored in the database. Fundamental data refers to the data for which the database is created to manage in the first place, for example, records of a group such as employees or students. Virtual tables--also called "view"--are tables derived from base tables using queries. The view does not exist in the database as a stored set of values like a base table. Instead the rows and columns of data visible through the view are the query results produced by the query that defines the view. The definition of the view is stored in the database. Temporary tables are not permanently stored, but are used for handling intermediate results, similar to program variables. Temporary tables are automatically flushed at the end of a working session. A table may be created, yet not have any data in it. Such a table, referred to as "empty", is typically created for receiving data at a later time.

"Constraints" define conditions that data must meet to be entered into a permanent table of fundamental data. Constraints may apply to columns or to tables; they are checked by an RDMS. A constraint can be checked at any of the following times:

(1) after every statement that affects a table (e.g., after an INSERT query);

(2) at the end of a transaction executing one or more statements that affect a table; and

(3) at any time between 1 and 2.

Frequently, in large commercial database systems, data must be entered quickly and in bulk. Bulk-loading facilities, available for this purpose, load database tables at high speed from files outside an RDMS.

Because bulk-loading delivers massive amounts of data in a short amount of time, constraint checking can impose a severe bottleneck if not deferred until all of the data are loaded.

Even if deferred, constraint checking that must check each record one time for one constraint violation, flag the violation, and then check the same record again for each remaining constraint will consume a large amount of time, compounding the cost of bulk loading. For example, if 10,000 records are entered and there are 10 constraints to check, each constraint check of each record consuming one-half second, it will take several hours to ensure the integrity of the bulk-loaded data. The check of the first constraint for each record of 10,000 records will consume 5,000 seconds, and the 5,000 second process will have to be repeated for each constraint to be checked. Further, if constraints are checked at the end of a transaction, rollback will add more time for those records which violate constraints. Clearly, there is a long-felt need in the art for a utility that can check all constraints simultaneously for each record in a given table.

Recently, bulk loading tools have been provided which do not perform constraint checking. A table receiving bulk-loaded data is placed in a "pending" state, meaning its data cannot be used until checked for constraints. What is needed is a tool for checking for constraints of such bulk-loaded data that can do so speedily and which also includes the capability of repairing such tables to remove violating records.

In any tool that performs constraint checking of bulk-loaded data the problem of ensuring that no constraints are violated is complicated by the need to ensure "referential integrity" at the database. Referential integrity ensures soundness of an entire database. Relatedly, consider the example of an employee database with a table that groups employees by department and a table that contains all possible departments of an employing organization. In this case, the table of employees would include a column representing the respective employees' department numbers. The employee department number value is a "foreign key" that references an unique identifying column in the table containing all the departments in an employing organization. The second table, in this case, is the "parent table". The unique identifying column in the parent table identifying department titles is referred to as a "primary key". Referential integrity is the state when all foreign key values are present in their parent keys. If an employee's department is eliminated and its corresponding record is deleted from the parent table, then the foreign key in the employee record, representing that department, is invalid. In such a case, the system would lack referential integrity. Although the above simple example shows a foreign and primary key having only one column, referential integrity can be assured using multi-column keys.

In the above example, the record for the employee having no department is said to be "orphaned" because the foreign key has no parent table. A typical SQL technique for dealing with orphans is to eliminate them when their parent references are eliminated. A function known as CASCADE is available in SQL for ensuring that records having foreign keys are eliminated when their referenced primary keys are eliminated.

What is needed is a suitable tool and technique for constraint-checking bulk-loaded data in a pending table that includes a violation capture mechanism that also ensures referential integrity.

SUMMARY OF THE INVENTION

To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent, this invention discloses a method and tool for constraint-checking of bulk-loaded data after storage in a relational database, that also ensures referential integrity.

In the method of this invention, a data processor generates an INSERT query for inserting one or more records into a bulk-loaded table in an RDMS. The processor then calls an SQL compiler to compile an SQL INSERT command. The code generated to execute the INSERT includes code to check for constraint violations in the records to be inserted. However, before compiling, optimizing, and executing the compiled code, the method of this invention replaces an INSERT command with a SELECT command, thereby enabling the resulting code to "constraint-check" the bulk-loaded data by selecting records that are in violation of the column constraints. This invention further includes an environment in which the above-described method may be carried out.

BRIEF DESCRIPTION OF THE DRAWING

FIG. 1 shows a schematic representation of the query translation process using the method of this invention;

FIG. 2 shows a Query Graph Model (QGM) diagram from the prior art for an exemplary SQL query;

FIG. 3 is a functional block diagram of a relational database system suitable for application of the bulk data constraint checking system of this invention;

FIGS. 4-8 each provide a flow chart illustrating the steps employed in executing the method of the present invention;

FIG. 9A shows an example of data to be bulk-loaded into empty tables in a database;

FIG. 9B shows the example of the tables of the database of FIG. 9A that are bulk-loaded with data and placed in a pending state such that the bulk data constraints checking method of this invention may be applied by the system of FIG. 3 according to the flow chart of FIGS. 4-8;

FIG. 9C shows the example of the pending database tables of FIG. 9B after the respective bulk data in each table has been checked for constraints, according to the method of this invention and with the system of this invention;

FIG. 10 shows command syntax useful for implementing this invention; and

FIG. 11 shows a computer program product recording medium useful for storing the software embodiment of this invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Referring now to the drawings in which like reference numbers represent corresponding parts throughout, in FIG. 1 a query translation process used in the known IBM Starburst relational database management system (Starburst RDMS) is shown with modifications necessitated for enabling the present invention. The IBM Starburst RDMS is described in detail in L. M. Hass, et at., "An Extensible Processor for an Extended Relational Query Language", IBM Research Report, RJ 6182, IBM Almaden Research Center, San Jose, Calif., April 1988.

Overview of Query Translation Process with SET CONSTRAINTS

Queries written in SQL, or the like, are processed in phases as shown in FIG. 1. An SQL query is first lexed at 13, parsed and checked semantically at 14, and converted into an internal representation denoted as the Query Graph Model (QGM) 15. The QGM is a well-known data structure that summarizes the semantic relationships of the query for use by all components of the query compiler. A QGM optimization procedure 16 then rewrites the QGM in canonical form by iteratively transforming the QGM into a semantically equivalent QGM 15. Reference for explanation regarding such optimization is made to W. Hasen, et al., "Query Rewrite Optimization in Starburst", IBM Research Report, RJ 6367, IBM Almaden Research Center, San Jose, Calif., August 1988.

The purpose of QGM optimization 16 is to simplify QGM 15 to help the subsequent plan optimization process to produce improved Query Execution Plans (QEPs). A plan optimization procedure 18 generates alternative QEPs, and chooses the best QEP 20, based on estimated costs. The plan refinement procedure 22 transforms the best QEP 20 further by adding information necessary at execution time to make QEP 20 suitable for efficient execution. QGM optimization step 16 is separate and distinct from QEP optimization step 18. For instance, reference is made to U.S. Pat. No. 5,345,585 issued to Iyer et al., entirely incorporated herein by this reference, for a discussion of a useful join optimization method suitable for use in QEP optimization step 18. Reference is also made to U.S. Pat. No. 5,301,317 issued to Lohman et al., entirely incorporated herein by the reference, for a description of an adaptive QEP optimization procedure suitable for step 18.

This invention is a constraint checking improvement that enables constraint checking of bulk-loaded data by using a QGM 15, the known constraint engine combined with a known SQL compiler, this combination being denoted as the constraint compiler 24, and a new module 26 described below. For an understanding of QGM 15 characteristics, reference is made to Hamid Pirahesh, et al., "Extensible/Rule Based Query Rewrite Optimization in Starburst," Proceedings of ACM SIGMOD '92 International Conference on Management of Data, San Diego, Calif., 1992. Among other characteristics, QGM 15 embraces the concepts of (a) quantifiers or record variables, (b) predicates, and (c) SELECT operations. This invention extends these concepts in a new and useful way to achieve its objectives.

One aspect of this invention is embodied as a particular program object module coupled with computer hardware, shown in FIG. 3 and discussed in detail below. This module is represented schematically in FIG. 1 at 26, and is denominated as the "SET CONSTRAINTS MODULE". Preferably, the method of this invention performed partly by this module is implemented in software, but it may also be embodied as hardware logic that implements the rules described below in connection with the flow charts in FIGS. 4-8. Further, this invention may be embodied as software code stored in any useful recording medium, including being recorded magnetically on the computer disk, shown in FIG. 12.

Because this invention extends RDMS features such as SQL query processing and QGM optimization to enable constraint checking of bulk-loaded data stored in a table residing in the database, it is necessary to discuss the QGM. Additionally, the constraint checking system of this invention also captures violations including those offending referential constraints, so that it is necessary to discuss referential integrity.

The QGM

A useful QGM known in the art is now described. FIG. 2 provides a QGM representation of the following SQL query:

SELECT DISTINCT Q1.PARTNO, Q1.DESCP, Q2.PRICE

FROM INVENTORY Q1, QUOTATIONS Q2

WHERE Q1.PARTNO=Q2.PARTNO

AND Q2.PRICE>100

A SELECT box 24 is shown with a body 26 and a head 28. Body 26 includes dataflow arcs 30 and 32, which are also shown as the internal vertices 34 and 36. Vertex 36 is a set-former that ranges on (reads from) the box 38, which provides records on arc 32. Similarly, vertex 34 ranges on box 40, which flows records on dataflow arc 30. The attributes to be retrieved from the query, PARTNO 46, DESC 48 and PRICE 50, are in head 28. Boxes 38 and 40 represent the base-relations accessed by the query, INVENTORY 42 and QUOTATIONS 44, respectively. Box 24 embraces the operations to be performed on the query to identify the PARTNOs that match in the two base-relations, as required by the join predicate 52 represented as an internal predicate edge joining vertices 34 and 36. Vertex 34 also includes a self-referencing predicate 54 to identify prices of those PARTNOs that exceed 100.

For the purposes of this invention, note that each box or node (formally denominated "quantifier node") in FIG. 2 is coupled to one or more other nodes by dataflow arcs (formally denominated "quantifier columns"). For instance, base-relation node 38 is coupled to select node 24 by data-flow arc 32 and base-relation node 40 is connected to select node 24 by data flow arc 30. The activities inside select node 24 produce a new stream of data records that are coupled to the TOP node 56 along a dataflow arc 58. TOP node 56 represents the data output table requested by the query.

The object of several known QGM optimization procedures is to merge one or more nodes where possible by eliminating (collapsing) dataflow arcs. For instance, the above-cited Pirahesh et al. reference describes a set of rules for merging any number of nodes into a single SELECT node, with certain restrictions on non-existential or non-Boolean factor subqueries, set operators, aggregates and user-defined extension operators such as OUTER JOIN. Thus those skilled in the art know that QGM optimization step 16 usually rewrites the QGM to eliminate numerous nodes and data-flow arcs even before considering useful query execution plans in plan optimization step 18 (FIG. 1).

Ensuring Referential Integrity (RI)

One or more columns in a base-relation may be assigned a "key attribute", such as "primary key" or "foreign key". The primary key of a base-relation uniquely identifies each record (row) in the relation. A foreign key establishes a "referential" relationship between the base-table (child) and another (parent) table designated by the foreign key column attribute. An unique key is a set of columns whose values uniquely determine a record in the table, so a primary key is, by definition, an unique key. Thus, a parent/child relationship may be established between two relations by adding a foreign-key attribute to one and relating it to an unique key attribute in another, as is well-known in the art. Such relationships may be added without limit and each key attribute may span several columns.

Generally speaking, a referential constraint relates a possibly multi-column foreign key from the child table to an unique key in the parent table. There is only one primary key in a table, but there may be many unique keys.

Disadvantageously, there are four types of database updates that can corrupt the referential integrity of the parent/child relationships in a database. These include (a) inserting a new child record, (b) updating the foreign key in a child record, (c) deleting a parent record, and (d) updating the primary key in a parent record.

When a new row is inserted into the child table, its foreign key value must match one of the primary key values in the parent table, according to the column attribute. If the foreign key value does not match any primary key, then an attempt to insert the row is rejected since it will corrupt the database to allow a child to exist without a parent. Inserting a row in a parent table never gives this problem because it simply becomes a parent without children. Updating the foreign key in a child record is a different form of this problem. If the foreign key is modified by an UPDATE statement, the new value must match some primary key value in the parent relation to avoid "orphaning" the updated record.

Upon deletion of a parent record that has one or more children, the child records are orphaned because their foreign key values no longer match any existing primary key value in the parent relation. Deleting a record from the child relation is no problem because the parent merely loses a child. Updating the primary key in a parent record is a different form of this problem. If the primary key of a parent record is modified, all existing children of that record become orphans because their foreign key values no longer match an existing primary key value.

For each parent/child relationship created by a foreign key, SQL provides for an associated delete rule and an associated update rule. The delete rule specifies DBMS action when a user tries to delete a parent record. Available rules include RESTRICT, CASCADE, SET NULL, and SET DEFAULT. The update rule also specifies one of these DBMS actions when the user tries to update the value of one of the primary key columns in the parent relation. The usual RESTRICT rule merely rejects the attempted operation. The CASCADE rule automatically deletes and updates records from the children responsive to a command to delete or update a parent record.

System Overview

FIG. 3 shows a functional block diagram of a computer-implemented database processing system 68 suitable for practicing the procedure of this invention. This exemplary configuration is described for illustrative purposes only and it should be appreciated that the process and system of this invention can be embodied within system 68 in many different useful fashions, including the arrangement depicted in FIG. 3. System 68 includes a central processing unit (CPU) 70, which is coupled to a parallel bus 72. The query input terminal 74 allows the user to enter queries into system 68, either from a remote terminal or through any other useful method known in the art. As used herein, a "user query" includes a combination of SQL commands intended to produce one or more output data tables according to specification included in the query. The data output terminal 76 displays the query results to the user and may be physically co-located with query input terminal 74.

System 68 includes the address space 78, which is shown schematically as containing program objects and data objects. The base table 80 is an example of a data object pulled into address space 78 from the external mass store 82 by way of bus 72. The view definition 84 is another data object representing a "virtual table" made up of elements from one or more base tables in accordance with a VIEW definition statement. External mass store 82 includes a generally large plurality of base tables (also denominated base relations), exemplified by base tables 86 and 88. These base tables are moved partially or entirely between memory space 78 and external mass store 82 in a manner well-known in the art for database management systems.

Address space 78 also includes the control program object 90, which manages the other components of system 68. These components include the query parser 14 for accepting the query input from terminal 74 and forwarding it to the Query Graph Model (QGM) optimizer 16. The Constraint Compiler (C.C.) 24 and the Set Constraints (S.C.) Module 26 interact between the parser and the QGM in accordance with the method of this invention.

The QGM optimizer rewrites the QGM representation of the user query to provide a "canonical form" of the QGM for output to the query optimizer 16. For instance, a QGM canonical form may include a large cyclical join graph organized within a single select node having dataflow arcs from many base-tables, subject to the restrictions of primary and referential integrity. After identifying an "optimal" query execution plan, optimizer 16 produces this plan as a program object, depicted as query execution plan 22 in address space 78. Plan 22 is finally executed with the assistance of control program 90 and the resulting relation is forwarded to data output of display 76 upon completion. It can be appreciated by those skilled in the art that the description of system 68 in FIG. 3 is exemplary and that the system and process of this invention, represented as the SET CONSTRAINTS processing module 26, may be incorporated in any RDMS that uses a query optimization process.

Operation of the Invention

General Syntax

This invention employs known SQL statements in novel ways to solve the problems of the prior art. Therefore, a brief discussion of important SQL statements are included below.

In SQL, a SELECT statement is used to retrieve data and generally comprises the format: "SELECT<clause>FROM<clause>WHERE<clause>GROUP BY<clause>HAVING<clause>ORDER BY<clause>." The clauses generally must follow this sequence, but only the SELECT statement and FROM clauses are required. The result of executing a SELECT statement is a subset of data retrieved by the RDMS software from one or more existing tables or views stored in the relational database, with the FROM clause telling the RDMS software the name of the table or view from which the data is being selected. The subset of data is treated as a new table, termed the "result table", which typically comprises a temporary table. In general, the items specified in the SELECT clause of the SELECT statement determine the columns that will be returned in the result table from the table(s) identified in the FROM clause.

The WHERE clause determines which rows should be returned in the result table. Generally, the WHERE clause contains a "search condition" that must be satisfied by each row returned in the result table. The rows that satisfy the search condition form an intermediate set, which is then processed further according to specifications in the SELECT clause. The search condition may include one or more predicates, each of which specify a comparison between two or more column values, constants or correlated values. Multiple predicates in the WHERE clause are themselves connected by Boolean operators.

A JOIN operation combines tables or views by appending rows from one table or view to another table or view. The rows, or portions of rows, from the different tables or views are concatenated horizontally through common columns. The JOIN operation is not provided explicitly as an SQL statement; instead it may be implied by naming more than one table or view in the FROM clause of the SELECT statement. Although not required, JOIN operations normally include a WHERE clause that identifies the columns through which the rows can be combined. The WHERE clause may also include one or more predicates in a subquery with conditional operators that are used to select the rows to be joined.

An INSERT statement adds data to a database according to the format: "INSERT INTO table.name VALUES (constant ›, . . . ! .linevert split. NULL)." INSERT fills one entire row in a single execution using the constants provided in the VALUES clause. Constraints are set for each table column when it is created, and a failure to comply with the table or column constraints will cause the INSERT to fail with an error. The SQL compiler has means for generating an internal process that compiles in the code to check constraints against the data modified by the SQL command.

The DELETE statement may be used to remove data according to the format: "DELETE FROM table.name ›WHERE<condition clause>!." DELETE erases all records of the table which satisfy the WHERE clause's predicates.

A predicate is an expression that is used in certain statements to test values and determine whether the effects of the statement should be applied to their values. A predicate may have a Boolean value of TRUE, FALSE, or UNKNOWN. The UNKNOWN value results when NULLS are used in comparisons. In terms of constraint checking, NOT NULL means that a column being checked may not contain the NULL value.

Understanding of the process described below will be improved by referring to the symbols table included below. The symbol tables include brief definitions of the symbols used in a preferred embodiment of pseudocode useful for implementing this invention.

Description of Symbols

The pseudocode is designed with the mathematical assumption that it is given a set of tables S={T.sub.i, 1<=i<=n } where T.sub.i :

has m.sub.T.sbsb.i columns, ##STR1## participates as the child in x.sub.T.sbsb.i referential constraints, ##STR2## and participates in y.sub.T.sbsb.i check constraints, ##STR3## The following Tables S-1 through S-3 define the symbols used in the pseudocode embodiments shown in Tables 1-5.

                  TABLE S-1
    ______________________________________
    Symbol      Definition
    ______________________________________
    R.sub.(T.sbsb.i .sub.,j)
                Referential Constraint
    .sup.z R.sub.(T.sbsb.i .sub.,j)
                Number of Columns of Referential Constraint
    T.sub.i     Table
    T.sub.i.R.sub.(T.sbsb.i .sub.,j) ›k!
                Column in T.sub.i corresponding to k.sup.th Column of
                Referential Constraint R(T.sbsb.i.sub.,j) (foreign key)
    P.sub.(T.sbsb.i .sub.,j)
                Parent Table in Referential Constraint
    P.sub.(T.sbsb.i .sub.,j).R.sub.(T.sbsb.i .sub.,j) ›k!
                Column in Parent Table corresponding to k.sup.th
                column of unique key (i.e., corresponding to
                Referential Constraint R.sub.(T.sbsb.i .sub.,j))
    ______________________________________


              TABLE S-2
    ______________________________________
    Symbol    Definition
    ______________________________________
    C.sub.(T.sbsb.i .sub.,j)
              Check Constraint
    .sup.z C.sub.(T.sbsb.i .sub.,j)
              Number of Columns from T.sub.i used in Constraint
              C.sub.(T.sbsb.i .sub.,j)
    C.sub.(T.sbsb.i .sub.,j) T.sub.i.C(T.sub.i,.sub.j)
              Evaluation of Constraint C.sub.(T.sbsb.i .sub.,j) on a
    ›.sup.z C.sub.(T.sbsb.i .sub.,j) !
              given record
    ______________________________________


              TABLE S-3
    ______________________________________
    Symbol         Definition
    ______________________________________
    E.sub.i        Exception Table for T.sub.i where 1<=i<=n
    C.sub.1, C.sub.2, . . . , .sup.C m.sub.T.sbsb.i
                   Columns of E.sub.i matching those of Table T.sub.i
    .sup.C m.sub.T.sbsb.i +1
                   Timestamp Field for E.sub.i
    .sup.C m.sub.T.sbsb.i +2
                   Message Field for E.sub.i
    ______________________________________

                  TABLE 1
    ______________________________________
    1.      begin
    2.                 insert into T.sub.1 select * from T.sub.1 ;
    3.                 insert into T.sub.2 select * from T.sub.2 ;
    4.                 . . .
    5.                 insert into T.sub.n select * from T.sub.n ;
    6.      end
    ______________________________________

                  TABLE 2
    ______________________________________
     1.   begin
     2.     with I.sub.1 as insert into T.sub.1 select * from T.sub.1
     3.     select 1
     4.     from I.sub.1
     5.     where 1=case
     6.     when I.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›1! is not null and
     7.       . . .
     8.       I.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›.sup.z R.sub.(T.sbsb.1
              .sub.,1) !is not null and
     9.       not exists
    10.           (select 1 from P.sub.(T.sbsb.1 .sub.,1)
    11.           where P.sub.(T.sbsb.1 .sub.,1).R.sub.(T.sbsb.1 .sub.,1)
                  ›1!=I.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›1!
    12.           and P.sub.(T.sbsb.1 .sub.,1).R.sub.(T.sbsb.1 .sub.,1)
                  ›2!=I.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›2!
    13.           . . .
    14.           and P.sub.(T.sbsb.1 .sub.,1).R.sub.(T.sbsb.1 .sub.,1)
                  ›.sup.z R.sub.(T.sbsb.1 .sub.,1) !=
                  I.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›.sup.z R.sub.(T.sbsb.1
                  .sub.,1) !)
    15.     then raise.sub.-- error(`-3603`, R.sub.(T.sbsb.1 .sub.,1).name)
    16.     . . .
    17.     when .sup.I 1.sup..R (T.sub.1,x.sub.T.sbsb.1)›1! is not null and
    18.     . . .
    19.     .sup.I 1.sup..R (T.sub.1,x.sub.T.sbsb.1).sup.›.spsp.z .sup.R
            (T.sub.1,x.sub.T.sbsb.1).sup.!
            is not null and
    20.     not exists
    21.     (select 1 from.sup..P (T.sub.1,x.sub.T.sbsb.1)
    22.     where .sup..P (T.sub.1,x.sub.T.sbsb.1).sup..R (T.sub.1,x.sub.T.sbs
            b.1)›1!=
            .sup.I 1.sup..R (T.sub.1,x.sub.T.sbsb.1)›1!
    23.     and .sup..P (T.sub.1,x.sub.T.sbsb.1).sup..R (T.sub.1,x.sub.T.sbsb.
            1)›2!=
            .sup.I 1.sup..R (T.sub.1,x.sub.T.sbsb.1)›2!
    24.     . . .
    25.     and.sup..P (T.sub.1,x.sub.T.sbsb.1).sup..R (T.sub.1,x.sub.T.sbsb.1
            ).sup.›.spsp.z .sup.R (T.sub.1,x.sub.T.sbsb.1).sup.! =
            .sup.I 1.sup..R (T.sub.1,x.sub.T.sbsb.1).sup.›.spsp.z .sup.R
            (T.sub.1,x.sub.T.sbsb.1).sup.!)
    26      then raise.sub.-- error(`-3603`, .sup..R (T.sub.1,x.sub.T.sbsb.1).
            name)
    27.     when not C.sub.(T.sbsb.1 .sub.,1) (I.sub.1.C.sub.(T.sbsb.1
            .sub.,1) ›1!, . . . ,
            I.sub.1.C.sub.(T.sbsb.1 .sub.,1) ›.sup.z C.sub.(T.sbsb.1 .sub.,1)
            !)
    28.     then raise.sub.-- error(`-3603`, C.sub.(T.sbsb.1 .sub.,1).name)
    29.     . . .
    30.     when not .sup..C (T.sub.1,y.sub.T.sbsb.1).sup.I 1.sup..C (T.sub.1,
            y.sub.T.sbsb.1)›1!, . . . ,
            .sup.I 1.sup..C (T.sub.1,y.sub.T.sbsb.1).sup.›.spsp.z .sup.R
            (T.sub.1,y.sub.T.sbsb.1).sup.!)
    31.     then raise.sub.-- error(`-3603`, .sup..C (T.sub.1,x.sub.T.sbsb.1).
            name)
    32.     else 2
    33.     with I.sub.n as insert into T.sub.n select * from T.sub.n
    34.     select 1
    35.     from I.sub.n
    36.     where 1=case
    37.     when I.sub.n.R.sub.(T.sbsb.n .sub.,1) ›1!is not null and
    38.       . . .
    39.       I.sub.n.R.sub.(T.sbsb.n .sub.,1) ›.sup.z R.sub.(T.sbsb.n
              .sub.,1) !is not null and
    40.       not exists
    41.           (select 1 from P.sub.(T.sbsb.n .sub.,1)
    42.           where P.sub.(T.sbsb.n .sub.,1).R.sub.(T.sbsb.n .sub.,1)
                  ›1!=
                  I.sub.n.R.sub.(T.sbsb.n .sub.,1) ›1!
    43.           and P.sub.(T.sbsb.n .sub.,1).R.sub.(T.sbsb.n .sub.,1) ›2!=
                  I.sub.n.R.sub.(T.sbsb.n .sub.,1) ›2!
    44.           . . .
    45.           and P..sub.(T.sbsb.n .sub.,1).R.sub.(T.sbsb.n .sub.,1)
                  ›.sup.z R.sub.(T.sbsb.1 .sub.,1) !=
                  I.sub.n.R.sub.(T.sbsb.n .sub.,1) ›.sup.z R.sub.(T.sbsb.1
                  .sub.,1) !)
    46.     then raise.sub.-- error(`-3603`, R.sub.(T.sbsb.n .sub.,1).name)
    47.     . . .
    48.     when .sup.I n.sup..R (T.sub.n,x.sub.T.sbsb.n)›1! is not null and
    49.       . . .
    50.       .sup.I n.sup..R (T.sub.n,x.sub.T.sbsb.n).sup.›.spsp.z .sup.R
              (T.sub.n,x.sub.T.sbsb.n).sup.!
              is not null and
    51.       not exists
    52.           (select 1 from .sup..P (T.sub.n,x.sub.T.sbsb.n)
    53.           where .sup..P (T.sub.n,x.sub.T.sbsb.n).sup..R (T.sub.n,x.sub
                  .T.sbsb.n)›1!=
                  .sup.I n.sup..R (T.sub.n,x.sub.T.sbsb.n)›1!
    54.           and .sup.P (T.sub.n,x.sub.T.sbsb.n).sup..R (T.sub.n,x.sub.T.
                  sbsb.n)›2!=
                  .sup.I n.sup..R (T.sub.n,x.sub.T.sbsb.n)›2!
    55.           . . .
    56.           and .sup.P (T.sub.n,x.sub.T.sbsb.n).sup..R (T.sub.n,x.sub.T.
                  sbsb.n).sup.›.spsp.z .sup.R (T.sub.n,x.sub.T.sbsb.n).sup.!=
                  .sup.I n.sup..R (T.sub.n,x.sub.T.sbsb.n).sup.›.spsp.z
                  .sup.R (T.sub.n,x.sub.T.sbsb.n).sup.!)
    57.     then raise.sub.-- error(`-3603`, .sup.R (T.sub.n,x.sub.T.sbsb.n).n
            ame)
    58.     when not C.sub.(T.sbsb.n .sub.,1) (I.sub.n.C.sub.(T.sbsb.n
            .sub.,1) ›1!, . . . ,
            I.sub.n.C.sub.(T.sbsb.n .sub.,1) ›.sup.z C.sub.(T.sbsb.n .sub.,1)
            !)
    59.     then raise.sub.-- error(`-3603`, C.sub.(T.sbsb.n .sub.,1).name)
    60.     . . .
    61.     when not .sup.C (T.sub.n,y.sub.T.sbsb.n).sup.I n.sup..C (T.sub.n,y
            .sub.T.sbsb.n .sub.) ›1!, . . . ,
            .sup.I n.sup..C (T.sub.n,y.sub.T.sbsb.n).sup.›.spsp.z .sup.R
            (T.sub.n,y.sub.T.sbsb.n .sub.) .sup.!)
    62.     then raise.sub.-- error(`-3603`, .sup..C (T.sub.n,y.sub.T.sbsb.n
            .sub.).name)
    63.     else 2
    64.   end
    65. end
    ______________________________________

                  TABLE 3
    ______________________________________
     1.   begin
     2.     select 1
     3.     from T.sub.1
     4.     where 1=CASE
     5.     when T.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›1! is not null and
     6.       . . .
     7.       T.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›.sup.z R.sub.(T.sbsb.1
              .sub.,1) !is not null and
     8.       not exists
     9.           (select 1 from P.sub.(T.sbsb.1 .sub.,1)
    10.           where P.sub.(T.sbsb.1 .sub.,1).R.sub.(T.sbsb.1 .sub.,1)
                  ›1!=T.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›1!
    11.           and P.sub.(T.sbsb.1 .sub.,1).R.sub.(T.sbsb.1 .sub.,1)
                  ›2!=T.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›2!
    12.           . . .
    13.           and P.sub.(T.sbsb.1 .sub.,1).R.sub.(T.sbsb.1 .sub.,1)
                  ›.sup.z R.sub.(T.sbsb.1 .sub.,1) !=
                  T.sub.1.R.sub.(T.sbsb.1 .sub.,1) ›.sup.z R.sub.(T.sbsb.1
                  .sub.,1) !)
    14.     then raise.sub.-- error(`-3603`, R.sub.(T.sbsb.1 .sub.,1).name)
    15.     . . .
    16.     when .sup.T 1.sup..R (T.sub.1,x.sub.T.sbsb.1)›1! is not null and
    17.     . . .
    18.     .sup.T 1.sup..R (T.sub.1,x.sub.T.sbsb.1).sup.›.spsp.z .sup.R
            (T.sub.1,x.sub.T.sbsb.1).sup.! is not null and
    19.     not exists
    20.     (select 1 from .sup.P (T.sub.1,x.sub.T.sbsb.1)
    21.     where .sup.P (T.sub.1,x.sub.T.sbsb.1).sup..R (T.sub.1,x.sub.T.sbsb
            .1)›1!=
            .sup.T 1.sup..R (T.sub.1,x.sub.T.sbsb.1)›1!
    22.     and .sup.P (T.sub.1,x.sub.T.sbsb.1).sup..R (T.sub.1,x.sub.T.sbsb.1
            )›2!=
            .sup.T 1.sup..R (T.sub.1,x.sub.T.sbsb.1)›2!
    23.     . . .
    24.     and .sup.P (T.sub.1,x.sub.T.sbsb.1).sup..R (T.sub.1,x.sub.T.sbsb.1
            ).sup.›.spsp.z .sup.R (T.sub.1,x.sub.T.sbsb.1).sup.! =
            .sup.T 1.sup..R (T.sub.1,x.sub.T.sbsb.1).sup.›.spsp.z .sup.R
            (T.sub.1,x.sub.T.sbsb.1).sup.!)
    25.     then raise.sub.-- error(`-3603`, .sup.R (T.sub.1,x.sub.T.sbsb.1).n
            ame)
    26.     when not C.sub.(T.sbsb.1 .sub.,1) (T.sub.1.C.sub.(T.sbsb.1
            .sub.,1) ›1!, . . . ,
            T.sub.1.C.sub.(T.sbsb.1 .sub.,1) ›.sup.z C.sub.(T.sbsb.1 .sub.,1)
            !)
    27.     then raise.sub.-- error(`-3603`, C.sub.(T.sbsb.1 .sub.,1).name)
    28.     . . .
    29.     when not .sup..C (T.sub.1,y.sub.T.sbsb.1).sup.(T 1.sup..C
            (T.sub.1,y.sub.T.sbsb.1)›1!, . . . ,
            .sup.T 1.sup..C (T.sub.1,y.sub.T.sbsb.1).sup.›.spsp.z .sup.C
            (T.sub.1,y.sub.T.sbsb.1).sup.!)
    30.     then raise.sub.-- error(`-3603`, .sup.C (T.sub.1,y.sub.T.sbsb.1).n
            ame)
    31.     else 2
    32.     . . .
    33.     select 1
    34.     from T.sub.n
    35.     where 1=case
    36.     when T.sub.n.R.sub.(T.sbsb.n .sub.,1) ›1!is not null and
    37.       . . .
    38.       T.sub.n.R.sub.(T.sbsb.n .sub.,1) ›.sup.z R.sub.(T.sbsb.n
              .sub.,1) !is not null and
    39.       not exists
    40.           (select 1 from P.sub.(T.sbsb.n .sub.,1)
    41.           where P.sub.(T.sbsb.n .sub.,1).R.sub.(T.sbsb.n .sub.,1)
                  ›1!=T.sub.n.R.sub.(T.sbsb.n .sub.,1) ›1!
    42.           and P.sub.(T.sbsb.n .sub.,1).R.sub.(T.sbsb.n .sub.,1)
                  ›2!=T.sub.n.R.sub.(T.sbsb.n .sub.,1) ›2!
    43.           . . .
    44.           and P.sub.(T.sbsb.n .sub.,1).R.sub.(T.sbsb.n .sub.,1)
                  ›.sup.z R.sub.(T.sbsb.n .sub.,1) !)=
                  T.sub.n.R.sub.(T.sbsb.n .sub.,1) ›.sup.z R.sub.(T.sbsb.n
                  .sub.,1) !
    45.     then raise.sub.-- error(`-3603`, R.sub.(T.sbsb.n .sub.,1).name
    46.     . . .
    47.     when .sup.T n.sup..R (T.sub.n,x.sub.T.sbsb.n)›1!is not null and
    48.       . . .
    49.       .sup.T n.sup..R (T.sub.n,x.sub.T.sbsb.n).sup.›.spsp.z .sup.R
              (T.sub.n,x.sub.T.sbsb.n).sup.! is not null and
    50.       not exists
    51.           (select 1 from .sup.P (T.sub.n,x.sub.T.sbsb.n)
    52.           where .sup.P (T.sub.n,x.sub.T.sbsb.n).sup..R (T.sub.n,x.sub.
                  T.sbsb.n)›1!=
                  .sup.T n.sup..R (T.sub.n,x.sub.T.sbsb.n)›1!
    53.           and .sup.P (T.sub.n,x.sub.T.sbsb.n).sup..R (T.sub.n,x.sub.T.
                  sbsb.n)›2!=
                  .sup.T n.sup..R (T.sub.n,x.sub.T.sbsb.n)›2!
    54.           . . .
    55.           and .sup.P (T.sub.n,x.sub.T.sbsb.n).sup..R (T.sub.n,x.sub.T.
                  sbsb.n).sup.›.spsp.z .sup.R (T.sub.n,x.sub.T.sbsb.n .sub.)
                  .sup.! =
                  .sup.T n.sup..R (T.sub.n,x.sub.T.sbsb.n).sup.›.spsp.z
                  .sup.R (T.sub.n,x.sub.T.sbsb.n).sup.!)
    56.     then raise.sub.-- error(`-3603`, .sup..R (T.sub.n,x.sub.T.sbsb.n).
            name)
    57.     when not C.sub.(T.sbsb.n .sub.,1) (T.sub.n.C.sub.(T.sbsb.n
            .sub.,1) ›1!, . . . ,
            T.sub.n.C.sub.(T.sbsb.n .sub.,1) ›.sup.z R.sub.(T.sbsb.n .sub.,1)
            !)
    58.     then raise.sub.-- error(`-3603`, C.sub.(T.sbsb.n .sub.,1).name)
    59.     . . .
    60.     when not .sup..C (T.sub.n,y.sub.T.sbsb.n).sup.(T n.sup..C
            (T.sub.n,y.sub.T.sbsb.n)›1!, . . . ,
            .sup.T n.sup..C (T.sub.n,y.sub.T.sbsb.n).sup.›.spsp.z .sup.C
            (T.sub.n,y.sub.T.sbsb.n).sup.!)
    61.     then raise.sub.-- error(`-3603`, C.sub.(T.sbsb.n .sub.,1).name
    62.     else 2
    63.   end
    ______________________________________

                  TABLE 4
    ______________________________________
    1.   begin
    2.    create temporary table ONEROW(c.sub.1) as (values (1))
    3.    insert into E.sub.1
    4.     select T.sub.1,c.sub.1,T.sub.1,c.sub.2 . . . T.sub.1.c.sub.m.sbsb.T
          1 current timestamp,
          ##STR4##
    6.        T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›1! is not null and
    7.        . . .
          ##STR5##
    9.       then 'R.sub.(T.sbsb.1.sub.,1).name' else "
    10.     end .parallel.
    11.      . . .
          ##STR6##
          ##STR7##
    14.       . . .
          ##STR8##
          ##STR9##
    17.     end .parallel.
          ##STR10##
    19.      then 'C.sub.(T.sbsb.1.sub.,1).name' else "
    20.     end .parallel.
    21.     . . .
          ##STR11##
          ##STR12##
          ##STR13##
    24.     end
    25.   from T.sub.1 left outer joint
    26.     (select 1
    27.     from ONEROW
    28.     where
    29.     not exists
    30.      (select 1 from P.sub.(T.sbsb.1.sub.,1)
    31.      where P.sub.(T.sbsb.1.sub.,1).R.sub.(T.sbsb.1.sub.,1) ›1! =
         T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›1!
    32.      and P.sub.(T.sbsb.1.sub.,1).R.sub.(T.sbsb.1.sub.,1) ›2! =
         T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›2!
    33.      . . .
          ##STR14##
          ##STR15##
    36.    left outer join . . .
    37.     (select 1
    38.     from ONEROW
    39.     where
    40.     not exists
          ##STR16##
          ##STR17##
    43.      . . .
          ##STR18##
          ##STR19##
          ##STR20##
          ##STR21##
    47.     T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›1! is not null and
    48.     . . .
          ##STR22##
    50.   or
    51.   . . .
          ##STR23##
          ##STR24##
    54.     . . .
          ##STR25##
    56.   or
          ##STR26##
    58.    . . .
          ##STR27##
    60.   . . .
    61.   insert into E.sub.n
          ##STR28##
          ##STR29##
    64.       T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›1! is not null and
    65.       . . .
          ##STR30##
    67.      then 'R.sub.(T.sbsb.n.sub.,1).name' else "
    68.      end .parallel.
    69.     . . .
    70.
          ##STR31##
          ##STR32##
    72.       . . .
          ##STR33##
          ##STR34##
    75.      end .parallel.
          ##STR35##
    77.      then 'C.sub.(T.sbsb.n.sub.,1).name' else "
    78.      end .parallel.
    79.     . . .
    80.
          ##STR36##
          ##STR37##
          ##STR38##
    82.      end
    83.   from T.sub.n left outer join
    84.     (select 1
    85.     from ONEROW
    86.     where
    87.     not exists
    88.      (select 1 from P.sub.(T.sbsb.n.sub.,1)
    89.      where P.sub.(T.sbsb.n.sub.,1).R.sub.(T.sbsb.n.sub.,1) ›1! =
         T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›1!
    90.      and P.sub.(T.sbsb.n.sub.,1).R.sub.(T.sbsb.n.sub.,1) ›2! =
         T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›2!
    91.      . . .
          ##STR39##
          ##STR40##
    94.    left outer join . . .
    95.     (select 1
    96.     from ONEROW
    97.     where
    98.     not exists
          ##STR41##
    100.
          ##STR42##
    101.     . . .
    102.
          ##STR43##
    103.
          ##STR44##
    104.
          ##STR45##
    105.    T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›1! is not null and
    106.    . . .
    107.
          ##STR46##
    108.   or
    109.   . . .
    110.
          ##STR47##
    111.
          ##STR48##
    112.    . . .
    113.
          ##STR49##
    114.   or
    115.
          ##STR50##
    116.   or . . .
    117.
          ##STR51##
    118. end
    ______________________________________

                  TABLE 5
    ______________________________________
    1.   begin
    2.     create temporary table ONEROW(c.sub.1) as (values(1)),
          ##STR52##
          ##STR53##
          ##STR54##
    6.         T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›1! is not null and
    7.         . . .
          ##STR55##
    9.        then 'R.sub.(T.sbsb.1.sub.,1).name' else "
    10.       end .parallel.
    11.     . . .
          ##STR56##
          ##STR57##
    14.        . . .
          ##STR58##
          ##STR59##
    17.       end .parallel.
          ##STR60##
    19.       then 'C.sub.(T.sbsb.1.sub.,1) '.name' else "
    20.       end .parallel.
    21.    . . .
          ##STR61##
          ##STR62##
          ##STR63##
    24.     end
    25.   from T.sub.1 left outer join
    26.    (select 1
    27.    from ONEROW
    28.    where
    29.    not exists
    30.     (select 1 from P.sub.(T.sbsb.1.sub.,1)
    31.     where P.sub.(T.sbsb.1.sub.,1).R.sub.(T.sbsb.1.sub.,1) ›1! =
         T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›1!
    32.     and P.sub.(T.sbsb.1.sub.,1).R.sub.(T.sbsb.1.sub.,1) ›2! =
         T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›2!
    33.     . . .
          ##STR64##
          ##STR65##
    36.    left outer join . . .
    37.    (select 1
    38.    from ONEROW
    39.    where
    40.    not exists
          ##STR66##
          ##STR67##
    43.     . . .
          ##STR68##
          ##STR69##
          ##STR70##
    47.    T.sub.1.R.sub.(T.sbsb.1.sub.,1) ›1! is not null and
    48.    . . .
          ##STR71##
    50.   or
    51.   . . .
          ##STR72##
          ##STR73##
    54.    . . .
          ##STR74##
    56.   or
          ##STR75##
    58.   . . .
          ##STR76##
    60.  . . .
          ##STR77##
          ##STR78##
          ##STR79##
    64.      T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›1! is not null and
    65.      . . .
          ##STR80##
    67.     then 'R.sub.(T.sbsb.n.sub.,1).name' else "
    68.     end .parallel.
    69.    . . .
    70.
          ##STR81##
          ##STR82##
    72.      . . .
          ##STR83##
          ##STR84##
    75.     end .parallel.
          ##STR85##
    77.     then 'C.sub.T.sbsb.n.sub.,1) '.name' else"
    78.     end .parallel.
    79.    . . .
    80.
          ##STR86##
          ##STR87##
          ##STR88##
    82.     end
    83.   from T.sub.n left outer join
    84.    (select 1
    85.    from ONEROW
    86.    where
    87.    not exists
    88.     (select 1 from P.sub.(T.sbsb.n.sub.,1)
    89.     where P.sub.(T.sbsb.n.sub.,1).R.sub.(T.sbsb.n.sub.,1) ›1! =
         T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›1!
    90.     and P.sub.(T.sbsb.n.sub.,1).R.sub.(T.sbsb.n.sub.,1) ›2! =
         T.sub.1.R.sub.(T.sbsb.n.sub.,1) ›2!
    91.     . . .
          ##STR89##
          ##STR90##
    94.    left outer join . . .
    95.    (select 1
    96.    from ONEROW
    97.    where
    98.    not exists
          ##STR91##
    100.
          ##STR92##
    101.    . . .
    102.
          ##STR93##
    103.
          ##STR94##
    104.
          ##STR95##
    105.   T.sub.n.R.sub.(T.sbsb.n.sub.,1) ›1! is not null and
    106.   . . .
    107.
          ##STR96##
    108.  or
    109.  . . .
    110.
          ##STR97##
    111.
          ##STR98##
    112.   . . .
    113.
          ##STR99##
    114.  or
    115.
          ##STR100##
    116.  or . . .
    117.
          ##STR101##
    118.  select * from VIOL.sub.1 ;
    119. . . .
    120.  select * from VIOL.sub.n ;
    121.
          ##STR102##
    122.   (delete VIOL.sub.1.c.sub.1,VIOL.sub.1.c.sub.2,...,
          ##STR103##
    123.   from T.sub.1, VIOL.sub.1
    124.   where T.sub.1.#rid = VIOL.sub.1.rid);
    125.  . . .
    126.
          ##STR104##
    127.   (delete VIOL.sub.n.c.sub.1,VIOL.sub.n.c.sub.2,...,
          ##STR105##
    128.   from T.sub.n, VIOL.sub.n
    129.   where T.sub.n.#rid = VIOL.sub.n.rid);
    130.  insert into E.sub.1
    131.
          ##STR106##
    132.  from DT.sub.1 ;
    133.  . . .
    134.  insert into E.sub.n
    135.
          ##STR107##
    136.  from DT.sub.n ;
    137. end
    ______________________________________

    ______________________________________
    create table EMP
               (empno int not null primary key,
               deptno int,
               age int,
               constraint EMPDEPT foreign key (deptno) refer-
               ences DEPT,
               constraint MINAGE age > 16);
    create table DEPT
               (deptno int not null primary key,
               name varchar(20),
               mgrno int) ; and
    create table PROJ
               projno int not null primary key,
               lead int,
               constraint PROJLEAD foreign key (lead) refer-
               ences EMP).
    ______________________________________

                  TABLE 6
    ______________________________________
    1.         begin
    2.                insert into EMP select * from EMP;
    3.                insert into PROJ select * from PROJ;
    4.         end
    ______________________________________

                  TABLE 7
    ______________________________________
     1.    begin
     2.       with I.sub.EMP as insert into EMP select * from EMP
     3.       select 1
     4.       from I.sub.EMP
     5.       where 1=case
     6.         when I.sub.EMP.deptno is not null and
     7.           not exists
     8.              (select 1 from DEPT.deptno
     9.              where DEPT.deptno = I.sub.EMP.deptno)
    10.         then raise.sub.-- error(`-3603`, EMPDEPT)
    11.         when not (I.sub.EMP.age > 16)
    12.         then raise.sub.-- error(`-3603`, MINAGE)
    13.         else 2
    14.       end
    15.       with I.sub.PROJ as insert into PROJ select * from PROJ
    16.       select 1
    17.       from I.sub.PROJ
    18.       where 1=case
    19.         when I.sub.PROJ.lead is not null and
    20.           not exists
    21.           (select 1 from EMP
    22.           where EMP.empno = I.sub.PROJ.lead)
    23.         then raise.sub.-- error(`-3603`, PROJLEAD)
    24.         else 2
    25.    end
    26.   end
    ______________________________________

                  TABLE 8
    ______________________________________
     1.    begin
     2.       select 1
     3.       from EMP
     4.       where 1=case
     5.         when EMP.deptno is not null and
     6.           not exists
     7.              (select 1 from DEPT.deptno
     8.              where DEPT.deptno = EMP.deptno)
     9. then    raise.sub.-- error(`-3603`, EMPDEPT)
    10.         when not EMP.age > 16)
    11.         then raise.sub.-- error(`-3603`, MINAGE)
    12.         else 2
    13.       end
    14.       select 1
    15.       from PROJ
    16.       where 1 = case
    17.         when PROJ.lead is not null and
    18.           not exists
    19.           (select 1 from EMP
    20.           where EMP.empno = PROJ.lead)
    21.         then raise.sub.-- error(`-3603`, PROJLEAD)
    22.         else 2
    23.    end
    24.   end
    ______________________________________

                  TABLE 9
    ______________________________________
     1. begin
     2.  create temporary table ONEROW(c.sub.1) as (values (1));
     3.  insert into E.sub.EMP
     4    select EMP.empno, EMP.deptno, EMP.age, current timestamp,
     5.     case when TEMP.sub.EMPDEPT.x is not null and
     6        EMP.deptno is not null
     7.      then `EMPDEPT` else "
     8.     end .parallel.
     9.     case when not (age > 16)
    10.      then `MINAGE` else "
    11.     end
    12.   from EMP left outer join
    13.     (select 1
    14.     from ONEROW
    15.     where
    16.     not exists
    17.      (select 1 from DEPT
    18.      where DEPT.deptno = EMP.deptno)) as TEMP.sub.EMPDEPT
    19.   where TEMP.sub.EMPDEPT .x is not null and
    20.     EMP.deptno is not null
    21.    or
    22.    not (age > 16);
    23.  insert into E.sub.PROJ
    24.   select PROJ.projno, PROJ.lead, current timestamp,
    25.     case when TEMP.sub.PROJLEAD.x is not null and
    26.       PROJ.lead is not null
    27.      then `PROJLEAD` else "
    28.     end .parallel.
    29.   from PROJ left outer join
    30.     (select 1
    31.     from ONEROW
    32.     where
    33.     not exists
    34.      (select 1 from EMP
    35.      where EMP.empno = PROJ.lead)) as TEMP.sub.PROJLEAD
    36.   where TEMP.sub.PROJLEAD.x is not null and
    37.     PROJ.lead is not null;
    38. end
    ______________________________________

                  TABLE 10
    ______________________________________
     1. begin
     2.    create temporary table ONEROW(c.sub.1) as (values (1));
     3.    create temporary table with VIOL.sub.EMP (rid, empno,
           deptno, age, ts, msg) as
     4.     (select EMP.#rid, EMP.empno, EMP.deptno,
            EMP.age, current timestamp,
     5.       case when TEMP.sub.EMPDEPT.x is not null and
     6.         EMP.deptno is not null
     7.        then `EMPDEPT` else "
     8.       end .parallel.
     9.       case when not (age > 16)
    10.        then `MINAGE` else "
    11.       end
    12.     from EMP left outer join
    13.       (select 1
    14.       from ONEROW
    15.       where
    16.       not exists
    17.        (select 1 from DEPT
    18.        where DEPT.deptno = EMP.deptno))
               as TEMP.sub.EMPDEPT
    19.     where (TEMP.sub.EMPDEPT.x is not null and
    20.       EMP.deptno is not null)
    21.      or
    22.      not (age > 16));
    23.    create temporary table VIOL.sub.PROJ (rid, projno, lead, ts, msg)
           as
    24.     (select PROJ.projno, PROJ.lead, current timestamp,
    25.       case when TEMP.sub.PROJLEAD.x is not null and
    26.         PROJ.lead is not null
    27.        then `PROJLEAD`else "
    28.       end
    29.     from PROJ left outer join
    30.       (select 1
    31.       from ONEROW
    32.       where
    33.       not exists
    34.        (select 1 from EMP
    35.        where EMP.empno = PROJ.lead))
               as TEMP.sub.PROJLEAD
    36.     where TEMP.sub.PROJLEAD.x is not null and
    37.       PROJ.lead is not null)
    38.    select * from VIOL.sub.EMP ;
    39.    select * from VIOL.sub.PROJ ;
    40.    create temporary table DT.sub.EMP (empno, deptno, age, ts, msg)
           as
    41.     (delete VIOL.sub.EMP.empno, VIOL.sub.EMP.deptno,
            VIOL.sub.EMP.age,
            VIOL.sub.EMP.ts, VIOL.sub.EMP.msg
    42.     from EMP, VIOL.sub.EMP
    43.     where EMP.#rid = VIOL.sub.EMP.rid);
    44.    create temporary table DT.sub.PROJ (projno, lead, ts, msg) as
    45.     (delete VIOL.sub.PROJ.projno, VIOL.sub.PROJ.lead,
            VIOL.sub.PROJ.ts,
            VIOL.sub.PROJ.msg
    46.     from PROJ, VIOL.sub.PROJ
    47.     where PROJ.#rid = VIOL.sub.PROJ.rid);
    48.    insert into E.sub.EMP
    49.    select empno, deptno, age, ts, msg
    50.    from DT.sub.EMP ;
    51.    insert into E.sub.PROJ
    52.    select projno, lead, ts, msg
    53.    from DT.sub.PROJ ;
    54. end
    ______________________________________

                                      TABLE 11
    __________________________________________________________________________
      begin
    2.  create temporary table ONEROW(c.sub.1) as (values (1))
    3.  create temporary table VIOL.sub.EMP (rid, empno, deptno, age, ts,
        msg) as
    4.    (select EMP.#rid, EMP.empno, EMP.deptno, EMP.age, current
          timestamp,
    5.       case when TEMP.sub.EMPDEPT.sup..x is not null and
    6.           EMP.deptno is not null
    7.         then `EMPDEPT`else "
    8.       end .parallel.
    9.       case when not (age > 16)
    10.        then `MINAGE` else "
    11.      end
    12.   from EMP left outer join
    13.      (select 1
    14.      from ONEROW
    15.      where
    16.      not exists
    17.        (select 1 from DEPT
    18.         where DEPT.deptno = EMP.deptno)) as TEMP.sub.EMPDEPT
    19.   where (TEMP.sub.EMPDEPT.x is not null and
    20.      EMP.deptno is not null)
    21.    or
    22.    not (age > 16))
    23. create temporary table VIOL.sub.PROJ (rid, projno, lead, ts, msg) as
    24.   (select PROJ.projno, PROJ.lead, current timestamp,
    25.      case when TEMP.sub.PROJLEAD.x is not null and
    26.          PROJ.lead is not null
    27.        then `PROJLEAD` else "
    28.      end
    29.   from PROJ left outer join
    30.      (select 1
    31.      from ONEROW
    32.      where
    33.      not exists
    34.         (select 1 from EMP
    35.         where EMP.empno = PROJ.lead)) as TEMP.sub.PROJLEAD
    36.   where TEMP.sub.PROJLEAD.x is not null and
    37.      PROJ.lead is not null)
    38. select * from VIOL.sub.EMP ;
    39. select * from VIOL.sub.PROJ ;
    40. create temporary table DT.sub.EMP (empno, deptno, age, ts, msg) as
    41.   (delete VIOL.sub.EMP.empno, VIOL.sub.EMP.deptno, VIOL.sub.EMP.age,
          VIOL.sub.EMP.ts, VIOL.sub.CMP.msg
    42.   from EMP, VIOL.sub.EMP
    43.   where EMP.#rid = VIOL.sub.EMP.rid);
    44. create temporary table DT.sub.PROJ (projno, lead, ts, msg) as
    45.   (delete VIOL.sub.PROJ.projno, VIOL.sub.PROJ.lead, VIOL.sub.PROJ.ts,
          VIOL.sub.PROJ.msg
    46.   from PROJ, VIOL.sub.PROJ
    47.   where PROJ.#rid = VIOL.sub.PROJ.rid);
    48. insert into E.sub.EMP
    49. select empno, deptno, age, ts, msg
    50. from DT.sub.EMP ;
    51. insert into E.sub.PROJ
    52. select projno, lead, ts, msg
    53. from DT.sub.PROJ;
    54. delete from PROJ, DT.sub.EMP
    55. where PROJ.lead = DT.sub.EMP.empno;
      end
    __________________________________________________________________________

• Inventors
  Cochrane, Roberta Jo; Pirahesh, Mir Hamid; Sidle, Richard Sefton; Zuzarte, Calisto Paul;
• Assignee
  International Business Machines Corporation (Armonk, NY)
• Published
  Jan-6-1998
• Current US Classes:
  707/101
  707/2
  707/200
  707/4
• Application #
  387037
• International Classes
  G06F 017/30
• Field of Search
  395/600 395/602 395/604 395/612 395/616 364/DIG.
• Examiner
  Black; Thomas G.
• Agent
  Baker, Maxham, Jester & Meador
• US Patent References:
  4506326
  4933848
  4947320
  5133068
  5201046
  5226158
  5295256
  5301317
  5355474
  5367675
  5386557
  5421008
  5471629
  5499359