Column constraints based on arbitrary sets of objects

• US 20070233641A1
• Filed: 03/31/2006
• Published: 10/04/2007
• Est. Priority Date: 03/31/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Das, Souripriya, Eadon, George, Srinivasan, Jagannathan, Chong, Eugene
• Granted Patent: US 8,214,354 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2435 Active constructs
• CPC Class Codes: G06F 16/24565 Triggers; Constraints
• CPC Class Codes: G06F 16/367 Ontology

Abstract

Techniques for using arbitrary sets of objects to constrain objects in database systems. The constraint set of objects is associated with the constrained object in the database system and the database system will perform an operation which adds an object to the constrained object only if the object being added belongs to the constraint set. In a preferred embodiment, the techniques are employed to obtain constraint sets of terms from ontologies which are then used to constrain columns containing those terms. An implementation of the techniques makes a materialized view out of the objects in the constraint set and uses the materialized view to define a referential integrity constraint on the constrained column.

Claims

1. A method of enforcing a constraint on a constrained object in a database system, the method comprising the steps performed in the database system of:

receiving a specification in the database system of the constrained object and a specification of an arbitrary constraint set of objects, the objects in the constraint set being possible values of the constrained object;

associating the constrained object with the constraint set; and

when an operation in the database system adds a value to the constrained object, permitting the operation only if the added value is a member of the constraint set.

2. The method set forth in claim 1 wherein:

the specification of the arbitrary constraint set is a query that is executed in the database system.

3. The method set forth in claim 2 wherein:

the query employs a table function.

4. The method set forth in claim 1 wherein:

the constrained object is a column that is defined in the database system and the step of associating the constrained object with the constraint set comprises the steps of;

making a materialized view with a column whose values are the members of the constraint set; and

using the column of the materialized view to define a referential integrity constraint for the constrained column.

5. The method set forth in claim 1 wherein:

an operation performed in the database system may result in a different constraint set and the method further includes the step of;

if required by the different constraint set, altering one or more values in the constrained object.

6. The method set forth in claim 5 wherein:

in the step of altering one or more values, when a value in the constrained object is no longer contained in the different constraint set, giving the value in the constrained object a null value.

7. The method set forth in claim 5 wherein:

in the step of altering one or more values, when a value in the constrained object is no longer contained in the different constraint set, giving the value in the constrained object a default value.

8. The method set forth in claim 5 wherein:

a transformation function is associated with the constraint set; and

in the step of altering one or more values, when a value in the constrained object is no longer contained in the different constraint set, using the transformation function to replace the value in the constrained object with one of the values in the different constraint set.

9. The method set forth in claim 8 wherein

the transformation function returns more than one value in the different constraint set that may replace the value in the constrained object and

the method further includes the step of:

selecting one of the more than one values to replace the value in the constrained object.

10. The method set forth in claim 9 wherein:

in the step of selecting one of the more than one values, the selection is made on the basis of input from a user of the database system.

11. The method set forth in claim 1 wherein:

an operation performed in the database system may result in a different constraint set; and

the step of associating may include the step of specifying that no operation that results in a different constraint set will be performed in the database system.

12. A storage device that is accessible to a processor, the storage device being characterized in that:

the storage device contains code which, when executed by the processor, implements the method set forth in claim 1.

32. The method set forth in claim 9 wherein:

in the step of selecting one of the more than one terms, the selection is made on the basis of input from a user of the database system.

13. An apparatus for enforcing a constraint on a constrained object in a database system, the apparatus comprising:

an association in the database system between the constrained object and an arbitrary constraint set of objects, the objects in the constraint set being possible values of the constrained object; and

a constraint enforcer in the data base system which, when an operation in the database system adds a value to the constrained object, permits the operation only if the added value is a member of the constraint set.

14. The apparatus set forth in claim 13 wherein:

the arbitrary constraint set is obtained by executing a query in the database system.

15. The apparatus set forth in claim 14 wherein:

the query employs a table function.

16. The apparatus set forth in claim 13 wherein

the constrained object is a column that is defined in the database system;

the constraint set associated with the constrained object is a materialized view with a column whose values are the members of the constraint set; and

the apparatus further comprises;

a referential integrity constraint for the constrained column which references the column in the materialized view.

17. The apparatus set forth in claim 13 wherein

an operation performed in the database system may result in a different constraint set and the database system further comprises:

apparatus that alters one or more values in the constrained object if so required by the different constraint set.

18. The apparatus set forth in claim 17 wherein:

when a value in the constrained object is no longer contained in the different constraint set, the apparatus that alters one or more values sets the one or more values to a null value.

19. The apparatus set forth in claim 17 wherein:

when a value in the constrained object is no longer contained in the different constraint set, the apparatus that alters one or more values sets the one or more values to a default value.

20. The apparatus set forth in claim 17 further comprising:

a transformation function that is associated with the constraint set; and

when a value in the constrained object is no longer contained in the different constraint set, the apparatus that alters one or more values applies the transformation function to the one or more values.

21. The apparatus set forth in claim 20 wherein:

the transformation function returns more than one value in the different constraint set that may replace the value in the constrained object; and

the apparatus that alters one or more values selects one of the more than one values returned by the transformation function to replace the value in the constrained object.

22. The apparatus set forth in claim 21 wherein:

the apparatus that alters one or more values selects the one value on the basis of input from a user of the database system.

23. The apparatus set forth in claim 17 wherein:

the association between the constrained object and the constraint set may specify that the database system will not perform an operation that results in the different constraint set.

24. A method of enforcing a constraint on a constrained object in a database system, the constrained object having values that are terms of an ontology and the method comprising the steps performed in the database system of:

receiving a specification in the database system of the constrained object and a specification of a query that returns a set of terms of the ontology;

associating the constrained object with the set of terms; and

when an operation in the database system adds a term to the constrained object, permitting the operation only if the added term is a member of the set of terms.

25. The method set forth in claim 24 wherein:

the query uses an ontology function that is executed in the database system.

26. The method set forth in claim 24 wherein:

the constrained object is a column that is defined in the database system and

the step of associating the constrained object with the set of terms comprises the steps of;

making a materialized view with a column whose values are the members of the set of terms and

using the column of the materialized view to define a referential integrity constraint for the constrained column.

27. The method set forth in claim 24 wherein:

an operation performed on the ontology in the database system may result in a different set of terms being returned by the query and the method further includes the step of;

if required by the different set of terms, altering one or more terms in the constrained object.

28. The method set forth in claim 27 wherein:

in the step of altering one or more terms in the constrained object, when a term in the constrained object is no longer contained in the different set of terms, giving the term in the constrained object a null value.

29. The method set forth in claim 27 wherein:

in the step of altering one or more terms in the constrained object, when a term in the constrained object is no longer contained in the different set of terms, giving the term in the constrained object a default value.

30. The method set forth in claim 27 wherein:

a transformation function is associated with the set of terms; and

in the step of altering one or more terms in the constrained object, when a term in the constrained object is no longer contained in the different set of terms using the transformation function to replace the term in the constrained object with one of the terms in the different set of terms.

31. The method set forth in claim 30 wherein

the transformation function returns more than one term in the different set of terms that may replace the term in the constrained object and

the method further includes the step of:

selecting one of the more than one terms to replace the term in the constrained object.

33. The method set forth in claim 24 wherein:

an operation performed in the database system may result in a different set of terms; and

the step of associating may include the step of specifying that no such operation be performed in the database system.

34. A storage device that is accessible to a processor, the storage device being characterized in that:

the storage device contains code which, when executed by the processor, implements the method set forth in claim 24.

35. Apparatus for enforcing a constraint on a constrained object in a database system, the constrained object having values that are terms of an ontology and the apparatus comprising:

an association in the database system between the constrained object and a query that returns a set of terms of the ontology; and

a constraint enforcer in the data base system which, when an operation in the database system adds a term to the constrained object, permits the operation only if the added term is a member of the set of terms.

36. The apparatus set forth in claim 35 wherein:

the query uses an ontology function that is executed in the database system.

37. The apparatus set forth in claim 35 wherein

the constrained object is a column that is defined in the database system;

the set of terms associated with the constrained object is a materialized view with a column whose values are the set of terms; and

the apparatus further comprises;

a referential integrity constraint for the constrained column which references the column in the materialized view.

38. The apparatus set forth in claim 35 wherein

an operation performed on the ontology in the database system may result in a different set of terms being returned by the query and the database system further comprises:

apparatus that alters one or more terms in the constrained object if so required by the different set of terms.

39. The apparatus set forth in claim 38 wherein:

when a term in the constrained object is no longer contained in the different set of terms, the apparatus that alters one or more terms sets the one or more terms to a null value.

40. The apparatus set forth in claim 38 wherein:

when a term in the constrained object is no longer contained in the different set of terms, the apparatus that alters one or more terms sets the one or more terms to a default value.

41. The apparatus set forth in claim 38 further comprising:

a transformation function that is associated with the set of terms; and

when a term in the constrained object is no longer contained in the different set of terms, the apparatus that alters one or more terms applies the transformation function to the one or more terms.

42. The apparatus set forth in claim 41 wherein:

the transformation function returns more than one term in the different set of terms that may replace the term in the constrained object; and

the apparatus that alters one or more terms selects one of the more than one terms returned by the transformation function to replace the term in the constrained object.

43. The apparatus set forth in claim 42 wherein:

the apparatus that alters one or more terms selects the one term on the basis of input from a user of the database system.

44. The apparatus set forth in claim 38 wherein:

the association between the constrained object and the set of terms may specify that the database system will not perform an operation that results in the different set of terms.