|Publication number||US7873671 B2|
|Application number||US 10/793,526|
|Publication date||Jan 18, 2011|
|Priority date||Mar 4, 2004|
|Also published as||US20050198048|
|Publication number||10793526, 793526, US 7873671 B2, US 7873671B2, US-B2-7873671, US7873671 B2, US7873671B2|
|Inventors||Eric Lawrence Barsness, John Matthew Santosuosso|
|Original Assignee||International Business Machines Corporation|
|Export Citation||BiBTeX, EndNote, RefMan|
|Patent Citations (7), Non-Patent Citations (5), Referenced by (2), Classifications (7), Legal Events (4)|
|External Links: USPTO, USPTO Assignment, Espacenet|
This patent application is related to U.S. patent application entitled “DATABASE RANGE CONSTRAINT THAT IS DYNAMICALLY LIMITED BY DATA IN THE DATABASE”, Ser. No. 10/733,752, filed on Dec. 11, 2003, which is incorporated herein by reference.
1. Technical Field
This invention generally relates to computer systems, and more specifically relates to database constraints.
2. Background Art
Since the dawn of the computer age, computers have evolved and become more and more powerful. In our present day, computers have become indispensable in many fields of human endeavor including engineering design, machine and process control, information storage and retrieval, and office computing. One of the primary uses of computers is for information storage and retrieval.
Database systems have been developed that allow a computer to store a large amount of information in a way that allows a user to search for and retrieve specific information in the database. For example, an insurance company may have a database that includes all of its policy holders and their current account information, including payment history, premium amount, policy number, policy type, exclusions to coverage, etc. A database system allows the insurance company to retrieve the account information for a single policy holder among the thousands and perhaps millions of policy holders in its database.
Database constraints are known in the art, and are used to limit data that may be entered into a database column. For example, a range constraint for a column that includes the age of people may have a range constraint that limits the entries to integer values from 1 to 99. If someone tries to enter a negative number, or zero, or 110, the database manager will detect that the data specified for the age column does not satisfy the limits specified in the range constraint for the age column, and will reject the attempt to enter data that is not within the specified range. In this manner, data may be limited to expected ranges.
Another known database constraint is a foreign key constraint, which limits allowable values for a column to data entered into the same column in a different table. This concept is powerful and useful when tables are related. Thus, a foreign key constraint may be specified that will not allow entry of a record into a table unless a corresponding record in a different table exists. The foreign key constraints are limited, however, in constraining a column based on allowable values in a different column that is of the same data type. A foreign key constraint assures the same data is entered into two columns of different database tables. As a result, the foreign key constraints are necessarily limited to specifying allowable values for a column based on a column of the same data type.
Table check constraints are also known in DB2 that allow specifying conditions across multiple columns for a record to be entered into the table. For example, a table check constraint could be defined for the table that assures that every employee who was hired prior to 1986 must make more than $40,000 per year. For example, such a table check constraint would only allow entry of data into the table if data in a HIREDATE column is less than 1986 and data in a SALARY column is less than $40,000. While table check constraints are useful, they are constraints on a table, not on a specific column within the table.
Known database constraints do not allow specifying allowable values for one column based on data in a second column when the second column has a different data type than the first column. Without a way for specifying allowable values in one column based on data in a second column that may be of a different type than data in the first column, the computer industry will continue to suffer from constraints that do not provide the desired flexibility.
According to the preferred embodiments, a database relationship constraint is defined for a first column that specifies a set of allowable values for the first column that depend on a set of data values in a different column that may be of a different type than data in the first column. In a first embodiment, a database relationship constraint specifies allowable values for one column in a database table based on one or more data values in a different column in the same database table. In a second embodiment, a database relationship constraint specifies a set of allowable values in a first column based on one or more data values in a second column in a different database table. In both cases, the data in the second column may be of a different type than data in the second column, and the set of allowable values for the relationship constraint may additionally depend on data in a third database column. In this manner a relationship constraint for a column may limit the set of allowable values according to one or more data values in one or more other columns that are not necessarily of the same data type.
The foregoing and other features and advantages of the invention will be apparent from the following more particular description of preferred embodiments of the invention, as illustrated in the accompanying drawings.
The preferred embodiments of the present invention will hereinafter be described in conjunction with the appended drawings, where like designations denote like elements, and:
The preferred embodiments define a relationship constraint that specifies one or more allowable values for a database column based on one or more data values in a different database column. In a first embodiment, a relationship constraint for a database column has a set of allowable values defined by data in a different column of the same table. In a second embodiment, a relationship constraint for a database column has a set of allowable values defined by data in a column in a different database table. Examples in accordance with the preferred embodiments are presented below.
Main memory 120 in accordance with the preferred embodiments contains data 121, an operating system 122, a database 123, a database manager 125, and database constraints 126. Data 121 represents any data that serves as input to or output from any program in computer system 100. Operating system 122 is a multitasking operating system known in the industry as OS/400; however, those skilled in the art will appreciate that the spirit and scope of the present invention is not limited to any one operating system. Database 123 is any suitable database, whether currently known or developed in the future. Database 123 preferably includes one or more database tables 124. Each database table 124 preferably includes data organized in columns and rows, with each row in the database table 124 defining an entry (or record) in the corresponding database table 124. Database manager 125 provides an interface to database 123 that allows reading data from and writing data to the database table(s) 124. Database constraints 126 include known database constraints, such as primary key constraints, unique constraints, foreign key constraints, and check constraints, which include range constraints. Note, however, that database constraints 126 additionally include one or more relationship constraints 127 in accordance with the preferred embodiments. A relationship constraint 127 specifies a set of allowable values for a first column that depends on data in a second column that may be of a different type than data in the first column. In a first embodiment, the different column is in the same database table. In a second embodiment, the different column is in a different database table. By providing a relationship constraint on a column that specifies a set of allowable values for a column that are a function of data in a different column, the relationship constraints 127 are data-driven from data in the database. Note that the term “set” when used in the phrase “set of allowable values” herein is used in a mathematical sense, and may include no values (empty set), one value (e.g., 20), multiple values (e.g., 20, 30), or an infinite number of values (e.g., real number between 20.0 and 30.0).
Computer system 100 utilizes well known virtual addressing mechanisms that allow the programs of computer system 100 to behave as if they only have access to a large, single storage entity instead of access to multiple, smaller storage entities such as main memory 120 and DASD device 155. Therefore, while data 121, operating system 122, database 123, database manager 125, and database constraints 126 are shown to reside in main memory 120, those skilled in the art will recognize that these items are not necessarily all completely contained in main memory 120 at the same time. It should also be noted that the term “memory” is used herein to generically refer to the entire virtual memory of computer system 100, and may include the virtual memory of other computer systems coupled to computer system 100.
Processor 110 may be constructed from one or more microprocessors and/or integrated circuits. Processor 110 executes program instructions stored in main memory 120. Main memory 120 stores programs and data that processor 110 may access. When computer system 100 starts up, processor 110 initially executes the program instructions that make up operating system 122. Operating system 122 is a sophisticated program that manages the resources of computer system 100. Some of these resources are processor 110, main memory 120, mass storage interface 130, display interface 140, network interface 150, and system bus 160.
Although computer system 100 is shown to contain only a single processor and a single system bus, those skilled in the art will appreciate that the present invention may be practiced using a computer system that has multiple processors and/or multiple buses. In addition, the interfaces that are used in the preferred embodiment each include separate, fully programmed microprocessors that are used to off-load compute-intensive processing from processor 110. However, those skilled in the art will appreciate that the present invention applies equally to computer systems that simply use I/O adapters to perform similar functions.
Display interface 140 is used to directly connect one or more displays 165 to computer system 100. These displays 165, which may be non-intelligent (i.e., dumb) terminals or fully programmable workstations, are used to allow system administrators and users to communicate with computer system 100. Note, however, that while display interface 140 is provided to support communication with one or more displays 165, computer system 100 does not necessarily require a display 165, because all needed interaction with users and other processes may occur via network interface 150.
Network interface 150 is used to connect other computer systems and/or workstations (e.g., 175 in
At this point, it is important to note that while the present invention has been and will continue to be described in the context of a fully functional computer system, those skilled in the art will appreciate that the present invention is capable of being distributed as a program product in a variety of forms, and that the present invention applies equally regardless of the particular type of computer-readable signal bearing media used to actually carry out the distribution. Examples of suitable computer-readable signal bearing media include: recordable type media such as floppy disks and CD RW (e.g., 195 of
With table T as shown in
Another prior art check constraint is shown in
Note that the set of allowable values for all three prior art constraints in
Foreign key constraints are also known in the art. A foreign key constraint is defined on a column that is a duplicate of a column in a different database table. The foreign key constraint restricts the set of allowable values of a first column to the values actually entered into a second column in a different database table, where the first and second columns have identical data types. The preferred embodiments provide much broader functionality by allowing a relationship constraint to be defined for a first column that depends on data in a second column, where the data in the second column may be of a different type than data in the first column.
In a first embodiment, a relationship constraint is defined for a selected column with a set of allowable values that depend on data in a different column in the same table. In a second embodiment, a relationship constraint is defined for a selected column with a set of allowable values that depend on data in a different column in a different table that may be of a different type than data in the selected column. In both cases, the database manager analyzes data in a database to dynamically determine a set of allowable values for the relationship constraint.
If a database entry operator attempted to enter record #2 in
Another relationship constraint within the scope of the first embodiment for the table T shown in
If a database entry operator attempted to enter record #2 in
Two tables X and Y are shown in
If a database entry operator attempted to enter record #2 in Table Y in
The second embodiment provides a relationship constraint for a first column in a first database table that has a set of allowable values that are defined by data in a second column in a second database table. This concept may be extended to any suitable number of columns in any table. Thus, a relationship constraint in accordance with the preferred embodiments could specify a set of allowable values for one column in a first database table based on two or more columns in one or more other database tables. The preferred embodiments expressly extend to relationship constraints that define a set of allowable values as a function of any suitable number of data values in any suitable number of columns in any suitable number of tables.
The specific examples shown in the figures illustrate some very simple and basic examples of relationship constraints within the scope of the preferred embodiments. Of course, more complex relationship constraints could be defined. The present invention expressly extends to any and all methods for defining a set of allowable values as a function of data in one or more other columns in the same or in a different database table that may be of a different type. The preferred embodiments provide relationship constraints that specify a set of allowable values that depend on data in the database. The set of allowable values for a relationship constraint is thus data-driven.
One skilled in the art will appreciate that many variations are possible within the scope of the present invention. Thus, while the invention has been particularly shown and described with reference to preferred embodiments thereof, it will be understood by those skilled in the art that these and other changes in form and details may be made therein without departing from the spirit and scope of the invention.
|Cited Patent||Filing date||Publication date||Applicant||Title|
|US5680614||May 20, 1993||Oct 21, 1997||Nec Corporation||Relational database management system|
|US6351742 *||Mar 18, 1999||Feb 26, 2002||Oracle Corporation||Method and mechanism for database statement optimization|
|US6463429 *||Apr 12, 2000||Oct 8, 2002||International Business Machines Corporation||System and method for consistency constraint management in database middleware|
|US7171408||Oct 30, 2001||Jan 30, 2007||International Business Machines Corporation||Method of cardinality estimation using statistical soft constraints|
|US7272591 *||Jan 22, 2004||Sep 18, 2007||Ncr Corporation||Method and system for updating value correlation optimizations|
|US20030078923 *||May 31, 2001||Apr 24, 2003||Douglas Voss||Generalized method for modeling complex ordered check constraints in a relational database system|
|US20050131913 *||Dec 11, 2003||Jun 16, 2005||International Business Machines Corporation||Database range constraint that is dynamically limited by data in the database|
|1||"Constraints and Triggers", pp. 1-3, http://www.cs.toronto.edu/db/courses/db2/constraints.html.|
|2||*||Advance Table Techniques, pp. 1-11.|
|3||*||Carole Goble, Integrity Constraints in Oracle 8i, Feb. 23, 2001, pp. 1-7.|
|4||*||Oracle, Data Integrity, 1996, pp. 1-16.|
|5||US patent application filed Dec. 11, 2003, U.S. Appl. No. 10/733,752; "Database Range Constraint that is Dynamically Limited by Data in the Database".|
|Citing Patent||Filing date||Publication date||Applicant||Title|
|US9152627 *||Sep 20, 2012||Oct 6, 2015||Business Objects Software Ltd||Automatic rule generation|
|US20140081931 *||Sep 20, 2012||Mar 20, 2014||Business Objects Software Ltd.||Automatic Rule Generation|
|U.S. Classification||707/802, 707/803, 707/804|
|International Classification||G06F7/00, G06F17/00|
|Mar 4, 2004||AS||Assignment|
Owner name: INTERNATIONAL BUSINESS MACHINES CORPORATION, NEW Y
Free format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:BARSNESS, ERIC LAWRENCE;SANTOSUOSSO, JOHN MATTHEW;REEL/FRAME:015052/0987
Effective date: 20040303
|Aug 29, 2014||REMI||Maintenance fee reminder mailed|
|Jan 18, 2015||LAPS||Lapse for failure to pay maintenance fees|
|Mar 10, 2015||FP||Expired due to failure to pay maintenance fee|
Effective date: 20150118