Generalized method for modeling complex ordered check constraints in a relational database system

• US 7,328,212 B2
• Filed: 05/31/2001
• Issued: 02/05/2008
• Est. Priority Date: 05/31/2001
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Eadon, George, Voss, Douglas
• Primary Examiner(s): Ali; Mohammad
• Assistant Examiner(s): Lu; Kuen S.
• Application Number:
  US09/871,338
• Publication Number:
  US 20030078923A1
• Time in Patent Office: 2,441 Days
• Field of Search: 707/3, 707/7
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99937 Sorting

Abstract

A system for enabling a user of a relational database to define and enforce ordered check constraints to insure that defined logical relationships are maintained between data values in pairs of adjacent rows when adjacency is defined by some ordering of the data. These inter-row conditions, here called “ordered check constraints,” are expressed by a logical expressions which define a required relationship between the attributes of a given row and its adjacent row or rows. Arbitrarily complex expressions involving these sets of attributes can be formed to model the constraints of interest. These expressions can be created by the database in support of, for example, traditional primary key or uniqueness constraints, or they can be provided by the database user to model new more complex constraints such as a requirement that there be no gaps in a list of serial numbers. These constraints can then be efficiently supported in the presence of a b-tree or another ordered index structure by identifying any inserted, updated or deleted rows and evaluating the expression in the context of those rows and any associated adjacent rows. This approach is similar to that typically used in existing database systems to support primary key and uniqueness constraints, providing the user with the ability to model a much richer set of constraints using a generalized expression which extends and enhances the functionality of check constraints to permit relationships of data in different rows to be defined and enforced.

Claims

1. In a relational database system for storing data in multiple rows of a relational database table, each of said rows consisting of one or more columns, apparatus for defining and enforcing one or more ordered check constraints for said table, said apparatus comprising, in combination,

means for accepting the definition of an ordered check constraint from a user, said definition consisting of the specification of a sort order in which said rows may be sequenced and the specification of a logical relationship between data values stored in at least one column in a given row of said table and data value stored in at least one column of the row which is adjacent to said given row when said given row and said row adjacent to said given row are sequenced in said sort order,

processing means for evaluating the data values in adjacent pairs of rows of said table to determine whether the data values in said adjacent rows satisfy said logical relationship, and

means for generating a failed constraint indication whenever said processing means determines that a pair of adjacent rows in said table do not satisfy said logical relationship.

2. The apparatus set forth in claim 1 further including an index data structure for rapidly accessing said rows of said table in said sort order.

3. The apparatus set forth in claim 2 wherein said processing means for evaluating the data values in adjacent pairs of rows in said table employs said index data structure to identify said adjacent pairs of rows.

4. The apparatus set forth in claim 1 wherein said definition is a constraint definition expressed in Structured Query Language.

5. The apparatus set forth in claim 4 wherein said constraint definition expressed in Structured Query Language includes said specification of said sort order and said specification of a logical relationship.

6. The apparatus set forth in claim 5 wherein said specification of said logical relationship takes the form of a Structured Query Language expression that refers to said data values stored in at least one column in said given row of said table and to said data value stored in at least one column of the row which is adjacent to said given row in said sort order.

7. In a relational database system for storing data in multiple rows of a relational database table, each of said rows consisting of one or more columns, the method for defining and enforcing one or more ordered check constraints for said table, said method comprising, in combination, the steps of

accepting the definition of an ordered check constraint from a user, said definition consisting of the specification of a sort order in which said rows may be sequenced and the specification of a logical relationship between data values stored in at least one column in a given row of said table and data value stored in at least one column of the row which is adjacent to said given row in said sort order,

evaluating the data values in adjacent pairs of rows of said table to determine whether the data values in said adjacent rows satisfy said logical relationship, and

generating a failed constraint indication whenever a pair of adjacent rows in said table do not satisfy said logical relationship.

8. The method set forth in claim 7 further including the step, performed prior to said step of evaluating said data valued, of creating an index data structure for rapidly accessing said rows of said table in said sort order.

9. The method set forth in claim 8 wherein said step of evaluating said data values in adjacent pairs of rows in said table employs said index data structure to identify said adjacent pairs of rows.

10. The method set forth in claim 7 wherein said definition is a constraint definition expressed in Structured Query Language.

11. The method set forth in claim 10 wherein said constraint definition expressed in Structured Query Language includes an ordered check clause specifying said sort order and said logical relationship.

12. The method set forth in claim 7 wherein said logical relation ship takes the form of a condition expressed in Structured Query Language that refers to said data values stored in at least one column in said given row of said table and said data value stored in at least one column of the row which is adjacent to said given row in said sort order.

13. The method of defining and enforcing adherence to a specified logical relationship between the data content in different rows of a relational database table which comprises, in combination, the steps of:

specifying a predetermined ordering sequence for the rows of said table,

specifying a condition which defines a logical relationship that must exist between the data content of adjacent rows in said table when said rows are ordered in accordance with said predetermined ordering sequence,

accepting changes to the data in said table in the form of one or more additions or modifications to the data contained in a given row of said table,

in response to the acceptance of said changes, comparing the data content in one or more pairs of adjacent rows of said table which include said given row,

generating a fault indication whenever said step of comparing determines that said condition is not satisfied by the data content in one of said pairs of rows.

14. The method of claim 13 further comprising the step of generating an index data structure for rapidly accessing said data content of said one or more pairs of adjacent rows.

15. The method of claim 13 wherein said step of specifying a predetermined sort order is performed by accepting an ordering specification expressed in Structured Query Language from a database user.

16. The method of claim 15 wherein said step of specifying said condition is performed by accepting an expression defining said logical relationship expressed in Structured Query Language from a database user.