Methods for improved referential integrity in a relational database management system

• US 5,499,359 A
• Filed: 01/18/1994
• Issued: 03/12/1996
• Est. Priority Date: 01/18/1994
• Status: Expired due to Term

• Current Assignee: CodeGear (Idera, Inc.)
• Original Assignee: Borland International, Inc. (Open Text Corporation)
• Inventors: Vijaykumar, Narayanan
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Lintz, Paul R.
• Application Number:
  US08/183,619
• Time in Patent Office: 784 Days
• Field of Search: 395/600
• US Class Current: 707/696
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/24565 Triggers; Constraints
• CPC Class Codes: Y10S 707/917 Text
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A system of the present invention includes a PC-based relational database management system (PC RDBMS) with data integrity facilities. Methods are described for maintaining referential integrity between data tables which may be freely moved to various remote workstations--ones which do not have ready access to a centrally-maintained data dictionary. The system provides each table with descriptors for tracking referential integrity links or relationships among various tables, regardless of a particular situs where a table resides. A preferred interface is also described for permitting end-users to easily define referential integrity links between data tables.

Claims

1. In a computer system, a method for maintaining referential integrity among a plurality of data tables stored in a relational database system, the method comprising:

(a) receiving from a user a request specifying a desired referential integrity link between two data tables, said request designating a master table having at least one field whose values are to serve as a reference, said at least one field serving as a primary key for said master table, and said request designating a detail table having at least one field whose values are required to match reference values in the master table, said at least one field of the detail table serving as a foreign key linking said detail table to the primary key of said master table;

(b) associating with said detail table a foreign key descriptor, said foreign key descriptor storing information specifying which index of the detail table supports said foreign key, which table is said master table for the referential integrity link, and which fields of the detail table contribute to said foreign key, said foreign key descriptor being stored together with said detail table so that a transfer of said detail table to a remote location includes transfer of said foreign key descriptor;

(c) associating with said master table an embedded key descriptor, said embedded key descriptor storing information specifying which fields of said master table contribute to said primary key and further specifying said detail table for the referential integrity link, said embedded key descriptor being stored together with said master table so that a transfer of said master table to a remote location includes transfer of said embedded key descriptor; and

(d) storing with said embedded key descriptor a detail table descriptor, said detail table descriptor storing information specifying said detail table, which index supports said foreign key, and which at least one field of said detail table contributes to said foreign key.

2. The method of claim 1, wherein said foreign key descriptor is stored as a part of said detail table.

3. The method of claim 2, wherein said foreign key descriptor is stored as a part of a header of said detail table.

4. The method of claim 1, wherein said foreign key descriptor is stored on a storage device as an associated file stored together with said detail table.

5. The method of claim 2, wherein each table has a table name and wherein said foreign key descriptor file is associated with said detail table by including the table name of the detail table as a portion of its file name.

6. The method of claim 4, wherein said embedded key descriptor is stored as a part of a header of said master table.

7. The method of claim 1, wherein said embedded key descriptor is stored as a part of said master table.

8. The method of claim 1, wherein said embedded key descriptor is stored on a storage device as an associated file stored together with said master table.

9. The method of claim 4, wherein each table has a table name and wherein said embedded key descriptor file is associated with said master table by including the table name of the master table as a portion of its file name.

10. The method of claim 1, wherein said request for a referential integrity link includes a descriptive name supplied by the user.

11. The method of claim 10, wherein said descriptive name is stored with both the foreign key descriptor and the embedded key descriptor.

12. The method of claim 1, wherein each index supporting a table is assigned an index identifier and wherein said information specifying which index supports said foreign key comprises the index identifier assigned to the index.

13. The method of claim 1, wherein each index supporting a table is assigned an index identifier and wherein said information specifying which fields of said master table contribute to said primary key comprises the index identifier assigned to the index.

14. The method of claim 1, wherein said information specifying the master table includes a name for the master table.

15. The method of claim 1, wherein said foreign key descriptor further includes a modify qualifier specifying whether to restrict or cascade changes made to corresponding reference values in the master table.

16. The method of claim 1, wherein said information specifying which fields of the detail table contribute to said foreign key comprises a number of key fields and a list of key fields.

17. The method of claim 1, wherein said foreign key descriptor further stores a list of at least one field of the master table which contributes to the primary key of the master table.

18. In an information processing system, a method for maintaining referential integrity of data stored in database tables, the method comprising:

(a) receiving from a user a request for a desired referential integrity constraint between at least one corresponding field of first and second database tables, said first table being a master table where its at least one corresponding field forms a primary key, and said second table being a detail table where its at least one corresponding field forms a foreign key matching said primary key;

(b) storing with the detail table a foreign key descriptor sufficient to characterize said foreign key and said primary key, said information including information identifying said at least one field of the detail table forming said foreign key and identifying said at least one field of the master table forming said primary key, said foreign key descriptor being stored together with said detail table so that a transfer of said detail table to a remote location includes transfer of said foreign key descriptor;

(c) storing with the master table an embedded key descriptor sufficient to characterize the detail table which the master table supports, said information including information identifying said at least one field of the detail table forming said foreign key and identifying said at least one field of the master table forming said primary key, said embedded key descriptor being stored together with said master table so that a transfer of said master table to a remote location includes transfer of said embedded key descriptor; and

(d) for each request received to modify or delete a record in the detail table, determining whether the request to modify would violate corresponding values stored in the master table, by examining said foreign key and embedded key descriptors.

19. The method of claim 18, wherein said request received from the user in step (a) includes a constraint name comprising descriptive text, said constraint name being stored with said foreign key descriptor in step (b) and being stored with said embedded key descriptor in step (c).

20. The method of claim 18, wherein said foreign key descriptor is stored as a part of said detail table.

21. The method of claim 18, wherein said foreign key descriptor is stored on a storage device as a separate file associated with said detail table.

22. The method of claim 18, wherein said embedded key descriptor is stored as a part of said master table.

23. The method of claim 18, wherein said embedded key descriptor is stored on a storage device as an associated file stored together with said master table.

24. The method of claim 18, wherein said foreign key descriptor further specifies a foreign key index file, said foreign key index file specifying an order of said detail table based on values stored in said at least one field of the detail table which forms said foreign key.

25. The method of claim 18, wherein said embedded key descriptor further specifies a primary key index file, said primary key index file specifying an order of said master table based on values stored in said at least one field of the master table which forms said primary key.

26. The method of claim 18, wherein said first table is a master table for a plurality of detail tables, and wherein a plurality of embedded key descriptors sufficient to characterize each detail table which the master table supports is stored with the master table, each embedded key descriptor sufficient to characterize a particular detail table which the master table supports.

27. The method of claim 18, wherein said request received from the user in step (a) includes information specifying whether modifications to values stored in said at least one corresponding field of the master table cascade to said at least one corresponding field of the detail table.

28. The method of claim 18, wherein said request received from the user in step (a) includes information specifying whether deletion of values stored in said at least one corresponding field of the master table are permitted if identical values are stored in said at least one corresponding field of the detail table.

29. A data processing system comprising:

means for storing a plurality of database tables, each table storing records comprising a plurality of fields;

means for requesting a data integrity constraint between corresponding fields of first and second tables, said first table having at least one field whose values must match values stored in at least one corresponding field of said second table;

means for storing with said first table a first descriptor specifying said at least one field of the first table whose values must match values stored in at least one corresponding field of said second table and further specifying said at least one corresponding field of said second table, so that a transfer of said first table to another location includes transfer of said first descriptor;

means for storing with said second table a second descriptor specifying said at least one field of the first table whose values must match values stored in at least one corresponding field of said second table and further specifying said at least one corresponding field of said second table, so that a transfer of said second table to another location includes transfer of said second descriptor; and

means, responsive to said first and second descriptors, for determining whether a database operation modifying either said first table or said second table may be performed without violating said data integrity constraint.

30. The system of claim 29, wherein said first descriptor comprises a foreign key descriptor storing information sufficient to characterize one or more fields of said first table whose values must match values stored in at least one corresponding field of said second table.

31. The system of claim 29, wherein said second descriptor comprises an embedded key descriptor storing information sufficient to characterize one or more fields of said second table whose values must be matched by values stored in said at least one corresponding field of said first table.

32. The system of claim 29, wherein said one or more fields of said first table form a primary key for said first table, and wherein said at least one corresponding field of said second table forms a foreign key for linking said first table with said primary key of said second table.