Skip to main content

Defining and Using Relationships

This topic describes relationships, which are a special kind of property that you can define only in persistent classes.

Overview of Relationships

A relationship is an association between two persistent objects, each of a specific type. To create a relationship between two objects, each must have a relationship property, which defines its half of the relationship. InterSystems IRIS® data platform directly supports two kinds of relationships: one-to-many and parent-child.

InterSystems IRIS relationships have the following characteristics:

  • Relationships are binary — a relationship is defined either between two, and only two, classes or between a class and itself.

  • Relationships can only be defined for persistent classes.

  • Relationships are bidirectional — both sides of a relationship must be defined.

  • Relationships automatically provide referential integrity. They are visible to SQL as foreign keys. See SQL Projection of Relationships.

  • Relationships automatically manage their in-memory and on-disk behavior.

  • Relationships provide superior scaling and concurrency over object collections.

    On the other hand, in an object collection, there is an inherent order of the objects; the same is not true for relationships. If you insert objects A, B, and C, in that order, into a list of objects, that order is retained. If you insert objects A, B, and C, in that order, into a relationship property, that order is not retained.

Note:

It is also possible to define foreign keys between persistent classes, rather than adding relationships. With a foreign key, you have a greater degree of control over what happens when an object in one class is added, updated, or deleted. See Using Triggers.

Note:

Relationships are not supported for sharded classes.

One-to-Many Relationships

In a one-to-many relationship between class A and class B, one instance of class A is associated with zero or more instances of class B.

For example, a company class may define a one-to-many relationship with an employee class. In this case, there may be zero or more employee objects associated with each company object.

These classes are independent of each other as follows:

  • When an instance of either class is created, it may or may not be associated with an instance of the other class.

  • If an instance of class B is associated with a given instance of class A, this association can be removed or changed. The instance of class B can be associated with a different instance of class A. The instance of class B does not have to have any association with an instance of class A (and vice versa).

There can be a one-to-many relationship within a single class. One instance of that class can be associated with zero or more other instances of that class. For example, the Employee class might define a relationship between an employee and any employees who directly report that employee.

Parent-Child Relationships

In a parent-child relationship between class A and class B, one instance of class A is associated with zero or more instances of class B. Also, the child table is dependent on the parent table, as follows:

  • When an instance of the class B is saved, it must be associated with an instance of class A. If you attempt to save the instance, and that association is not defined, the save action fails.

  • The association cannot be changed. That is, you cannot associate the instance of class B with a different instance of class A.

  • If the instance of class A is deleted, all associated instances of class B are deleted as well.

  • You can delete an instance of class B. Class A is not required to have associated instances of class B.

For an example, an invoice class may define a parent-child relationship with a line item class. In this case, an invoice consists of zero or more line items. Those line items cannot be moved to a different invoice. Nor do they have meaning on their own.

Important:

Also, in the child table (class B), the IDs are not purely numeric. As a consequence, it is not possible to add a bitmap index to the relationship property in this class, although other forms of index are permitted (and are useful, as shown later in this topic).

Parent-Child Relationships and Storage

If you define a parent-child relationship before compiling the classes, the data for both classes is stored in the same global. The data for the children is subordinate to that of the parent, in a structure similar to the following:

^Inv(1)
^Inv(1, "invoice", 1)
^Inv(1, "invoice", 2)
^Inv(1, "invoice", 3)
...

As a result, InterSystems IRIS can read and write these related objects more quickly.

Common Relationship Terminology

This section explains, by example, phrases that are in common use for convenience when discussing relationships.

Consider a one-to-many relationship between a company and its employees; that is, one company has multiple employees. In this scenario, the company is called the one side and the employee is called the many side.

Similarly, consider a parent-child relationship between a company and its products; that is, the company is the parent, and the products are the children. In this scenario, the company is called the parent side and the product is called the children side or the child side.

Defining a Relationship

To create a relationship between the records of two classes, you create a pair of complementary relationship properties, one in each class. To create a relationship between records of the same class, you create a pair of complementary relationship properties in that class.

The following subsections describe the general syntax and then discuss how to define one-to-many relationships and parent-child relationships.

General Syntax

The syntax for a relationship property is as follows:

Relationship Name As classname [ Cardinality = cardinality_type, Inverse = inverseProp ];

Where:

  • classname is the class to which this relationship refers. This must be a persistent class.

  • cardinality_type (required) defines how the relationship appears from this side as well as whether it is an independent relationship (one-to-many) or a dependent relationship (parent-child). cardinality_type can be one, many, parent, or children.

  • inverseProp (required) is the name of the complementary relationship property, which is defined in the other class.

In the complementary relationship property, the cardinality_type keyword must be the complement of the cardinality_type keyword here. The values one and many are complements of each other. Similarly, the values parent and children are complements of each other.

Because a relationship is a kind of property, other property keywords are available for use in them, including Final, Required, SqlFieldName, and Private. Some property keywords, such as MultiDimensional, do not apply. See the Class Definition Reference for more information.

Defining a One-to-Many Relationship

This section describes how define a one-to-many relationship between classA and classB, where one instance of classA is associated with zero or more instances of classB.

Note:

It is possible to have a one-to-many relationship between records of a single class. That is, in the following discussion, classA and classB can be the same class.

Class A must have a relationship property of the following form:

Relationship manyProp As classB [ Cardinality = many, Inverse = oneProp ];

Where oneProp is the name of the complementary relationship property, which is defined in classB.

Class B must have a relationship property of the following form:

Relationship oneProp As classA [ Cardinality = one, Inverse = manyProp ];

Where manyProp is the name of the complementary relationship property, which is defined in classA.

Important:

On the one side (class A), the relationship uses a query to populate the relationship object. You can improve the performance of this query in almost all cases by adding an index on the complementary relationship property (that is, adding an index on the many side, class B).

Defining a Parent-Child Relationship

This section describes how define a parent-child relationship between classA and classB, where one instance of classA is the parent of zero or more instances of classB. These cannot be the same class.

Class A must have a relationship property of the following form:

Relationship childProp As classB [ Cardinality = children, Inverse = parentProp ];

Where parentProp is the name of the complementary relationship property, which is defined in classB.

Class B must have a relationship property of the following form:

Relationship parentProp As classA [ Cardinality = parent, Inverse = childProp ];

Where childProp is the name of the complementary relationship property, which is defined in classA.

Important:

On the parent side (class A), the relationship uses a query to populate the relationship object. You can improve the performance of this query in almost all cases by adding an index on the complementary relationship property (that is, adding an index on the child side, class B).

Parent-Child Relationships and Compilation

For a parent-child relationship, InterSystems IRIS can generate a storage definition that stores the data for the parent and child objects within a single global, as shown earlier. Such a storage definition improves the speed with which you can access these related objects.

If you add a relationship after compiling the classes, InterSystems IRIS does not generate this optimized storage definition. In such a case, you can delete any test data you might have, delete the storage definitions of the two classes, and then recompile.

Examples

This section presents examples of a one-to-many relationship and a parent-child relationship.

Example One-to-Many Relationship

This example represents a one-to-many relationship between a company and its employees. The company class is as follows:

Class MyApp.Company Extends %Persistent
{

Property Name As %String;

Property Location As %String;

Relationship Employees As MyApp.Employee [ Cardinality = many, Inverse = Employer ];

}

And the employee class is as follows:

Class MyApp.Employee Extends (%Persistent, %Populate)
{

Property FirstName As %String;

Property LastName As %String;

Relationship Employer As MyApp.Company [ Cardinality = one, Inverse = Employees ];

Index EmployerIndex On Employer;

}

Example Parent-Child Relationship

This example represents a parent-child relationship between an invoice and its line items. The invoice class is as follows:

Class MyApp.Invoice Extends %Persistent
{

Property Buyer As %String;

Property InvoiceDate As %TimeStamp;

Relationship LineItems As MyApp.LineItem [ Cardinality = children, Inverse = Invoice ];

}

And the line item class is as follows:

Class MyApp.LineItem Extends %Persistent
{

Property ProductSKU As %String;

Property UnitPrice As %Numeric;

Relationship Invoice As MyApp.Invoice [ Cardinality = parent, Inverse = LineItems ];

Index InvoiceIndex On Invoice;

}

Connecting Objects

A relationship is bidirectional. Specifically, if you update the value of the relationship property in one object, that immediately affects the value of the corresponding relationship property in the related object. As a consequence, you can specify the value for a relationship property in one object, and the effect appears in both objects.

Because the nature of the relationship property is different in the two classes, there are two general scenarios for updating any relationship:

  • Scenario 1: The relationship property is a simple reference property. Set the property equal to the appropriate object.

  • Scenario 2: The relationship property is an instance of %RelationshipObjectOpens in a new tab, which has an array-like interface. Use methods of that interface to insert objects into the relationship. Note that the objects in the relationship are not ordered; the relationship does not retain the order in which you inserted objects into it.

The following subsections give the details. The third subsection describes a variation of Scenario 1 that is especially suitable when you have a large number of objects in the relationship.

The information here describes how to add objects to relationships. The process of modifying objects is similar, with an important exception (by design) in the case of parent-child relationships: Once associated with a particular parent object (and then saved), a child object can never be associated with a different parent.

Scenario 1: Updating the Many or Child Side

On the many side or the child side (ObjA), the relationship property is a simple reference property that points to ObjB. To connect the objects from this side:

  1. Obtain an OREF (ObjB) for an instance of the other class. (Either create a new object or open an existing object, as appropriate.)

  2. Set the relationship property of ObjA equal to ObjB.

For an example, consider the example parent-child classes shown earlier. The following steps would update the relationship from the MyApp.LineItem side:

  //obtain an OREF to the invoice class
 set invoice=##class(MyApp.Invoice).%New()
 //...specify invoice date and so on

 set item=##class(MyApp.LineItem).%New()
 //...set some properties of this object such as the product name and sale price...

 //connect the objects
 set item.Invoice=invoice

When you call the %Save() method for the item object, the system saves both objects (item and invoice).

Also see the last subsection for a variation of this technique.

Scenario 2: Updating the One or Parent Side

On the one side or the parent side, the relationship property is an instance of %RelationshipObjectOpens in a new tab. On this side, you can do the following to connect the objects:

  1. Obtain an OREF for an instance of the other object. (Either create a new object or open an existing object, as appropriate.)

  2. Call the Insert() method of the relationship property on this side and pass that OREF as the argument.

Consider the example parent-child classes shown earlier. For those classes, the following steps would update the relationship from the MyApp.Invoice side:

 set invoice=##class(MyApp.Invoice).%OpenId(100034)
 //set some properties such as the customer name and invoice date

 set item=##class(MyApp.LineItem).%New()
 //...set some properties of this object such as the product name and sale price...

 //connect the objects
 do invoice.LineItems.Insert(item)

When you call the %Save() method for the invoice object, the system saves both objects (item and invoice).

Important:

InterSystems IRIS does not maintain information about the order in which objects are added into the relationship. That is, if you open a previously saved object and use GetNext() or similar methods to iterate through a relationship, the order of objects in that relationship is different from when the objects were created.

Fastest Way to Connect Objects

When you need to add a comparatively large number of objects to a relationship, use a variation of the technique given in Scenario 1. In this variation:

  1. Obtain an OREF (ObjA) for Class A.

  2. Obtain the ID for an instance of ClassB.

  3. Use the property setter method of the relationship property of ObjA, passing the ID as the argument.

    If the relationship property is named MyRel, the property setter method is named MyRelSetObjectId().

    (For details on property setter methods, see Using and Overriding Property Methods.

Consider the example classes described in Scenario 1. For those classes, the following steps would insert a large number of invoice items into an invoice (and would do so more rapidly than the technique given in that section):

 set invoice=##class(MyApp.Invoice).%New()
 //set some properties such as the customer name and invoice date
 do invoice.%Save()
 set id=invoice.%Id()
 kill invoice  //OREF is no longer needed
 
 for index = 1:1:(1000)
  {
    set Item=##class(MyApp.LineItem).%New()
    //set properties of the invoice item
    
    //connect to the invoice
    do Item.InvoiceSetObjectId(id)
    do Item.%Save()
  } 

Removing a Relationship

In the case of a one-to-many relationship, it is possible to remove a relationship between two objects. One way to do so is as follows:

  1. Open the instance of the child object (or the object on the many side).

  2. Set the applicable property of this object equal to null.

For example, there is a one-to-many relationship between Sample.Company and Sample.Employee. The following demonstrates that the employee whose ID is 101 works for the company whose ID is 5. Notice that this company has four employees:

MYNAMESPACE>set e=##class(Sample.Employee).%OpenId(101)
 
MYNAMESPACE>w e.Company.%Id()
5
MYNAMESPACE>set c=##class(Sample.Company).%OpenId(5)
 
MYNAMESPACE>w c.Employees.Count()
4

Next for this employee, we set the Company property equal to null. Notice that this company now has three employees:

MYNAMESPACE>set e.Company=""
 
MYNAMESPACE>w c.Employees.Count()
3

It is also possible to remove the relationship by modifying the other object. In this case, we use the RemoveAt() method of the collection property. For example, the following demonstrates that for the company whose ID is 17, the first employee is employee ID 102:

MYNAMESPACE>set e=##class(Sample.Employee).%OpenId(102)
 
MYNAMESPACE>w e.Company.%Id()
17
MYNAMESPACE>set c=##class(Sample.Company).%OpenId(17)
 
MYNAMESPACE>w c.Employees.Count()
4
MYNAMESPACE>w c.Employees.GetAt(1).%Id()
102

To remove the relationship between this company and this employee, we use the RemoveAt() method, passing the value 1 as the argument, to remove the first collection item. Notice that after we do so, this company has three employees:

MYNAMESPACE>do c.Employees.RemoveAt(1)
 
MYNAMESPACE>w c.Employees.Count()
3

In the case of a parent-child relationship, it is not possible to remove a relationship between two objects. You can, however, delete a child object.

Deleting Objects from Relationships

For a one-to-many relationship, the following rules govern what occurs when you attempt to delete objects:

  • The relationship prevents you from deleting an object on the one side, if there are any objects on the many side that reference this object. For example, if you try to delete a company, and the employee table has records that point to that company, the delete operation fails.

    Thus it is necessary to first delete the records on the many side.

  • The relationship does not prevent you from deleting an object on the many side (the employee table).

For a parent-child relationship, the rules are different:

  • The relationship causes a deletion on the parent side to affect the child side. Specifically, if you delete an object on the parent side, the associated objects on the child side are automatically deleted.

    For example, if there is a parent-child relationship between invoices and line items, if you delete an invoice, its line items are deleted.

  • The relationship does not prevent you from deleting an object on the child side (the line item table).

For both one-to-many and parent-child relationships, you can change the default behavior of deleting an object on the one side or the parent side by using the OnDelete property keyword.

Working with Relationships

Relationships are properties. Relationships with a cardinality of one or parent behave like atomic (non-collection) reference properties. Relationships with a cardinality of many or children are instances of the %RelationshipObjectOpens in a new tab class, which has an array-like interface.

For example, you could use the Company and Employee objects defined above in the following way:

 // create a new instance of Company
 Set company = ##class(MyApp.Company).%New()
 Set company.Name = "Chiaroscuro LLC"

 // create a new instance of Employee
 Set emp = ##class(MyApp.Employee).%New()
 Set emp.LastName = "Weiss"
 Set emp.FirstName = "Melanie"

 // Now associate Employee with Company
 Set emp.Employer = company

 // Save the Company (this will save emp as well)
 Do company.%Save()

 // Close the newly created objects 
 Set company = ""
 Set emp = ""

Relationships are fully bidirectional in memory; any operation on either side is immediately visible on the other side. Hence, the code above is equivalent to the following, which instead operates on the company:

 Do company.Employees.Insert(emp)

 Write emp.Employer.Name 
 // this will print out "Chiaroscuro LLC"

You can load relationships from disk and use them as you would any other property. When you refer to the object on the one side of a relationship from any object on the many side, the object on the one side is automatically swizzled into memory in the same way as a reference (object-valued) property. When you refer to any of the objects on the many side of a relationship from the object on the one side, the objects on the many side are not swizzled immediately; instead a transient %RelationshipObjectOpens in a new tab collection object is created. As soon as any methods are called on this collection, it builds a list containing the ID values of the objects within the relationship. It is only when you refer to one of the objects within this collection that the actual related object is swizzled into memory.

Here is an example that displays all Employee objects related to a specific Company:

 // open an instance of Company
 Set company = ##class(Company).%OpenId(id)

 // iterate over the employees; print their names
 Set key = ""

 Do {
    Set employee = company.Employees.GetNext(.key)
    If (employee '= "") {
        Write employee.Name,!
    }
 } While (key '= "")

In this example, closing company removes the Company object and all of its related Employee objects from memory. Note, however, that every Employee object contained in the relationship will be swizzled into memory by the time the loop completes. To reduce the amount of memory that this operation uses—perhaps there are thousands of Employee objects—then modify the loop to unswizzle the Employee object after displaying the name, by calling the %UnSwizzleAt() method:

 Do {
    Set employee = company.Employees.GetNext(.key)
    If (employee '= "") {
        Write employee.Name,!
        // remove employee from memory
        Do company.Employees.%UnSwizzleAt(key)
    }
 } While (key '= "")
Important:

Relationships do not support the list interface. That means you cannot get the count of related objects and iterate over the relationship by incrementing a pointer from one (1) by one (1) up to the number of related objects; instead, you must use array-collection style iteration. For more information on iterating through objects in a relationship, see the reference page for %Library.RelationshipObjectOpens in a new tab.

SQL Projection of Relationships

A persistent class is projected as an SQL table. This section describes how relationships of such a class are projected to SQL.

Note:

Although you can modify the projection of the other properties of the classes involved, it is not possible to modify the SQL projection of relationships per se. For example, it is not supported to specify the CLASSNAME property parameter for the relationship. This parameter is mentioned in Defining Object-Valued Properties.

SQL Projection of One-to-Many Relationships

This section describes the SQL projection of a one-to-many relationship. As an example, consider the example one-to-many classes shown earlier. In this case, the classes are projected as follows:

  • On the one side (that is, in the company class), there is no field that represents the relationship. The company table has fields for other properties, but there is no field that holds the employees.

  • On the many side (that is, in the employee class), the relationship is a simple reference property, and that is projected to SQL in the same way as other reference properties. The employee table has a field named Employer, which points to the company table.

To query these tables together, you can query the employee table and use arrow syntax, as in the following example:

SELECT Employer->Name, LastName,FirstName FROM MyApp.Employee

Or you can perform an explicit join, as in the following example:

SELECT c.Name, e.LastName, e.FirstName FROM MyApp.Company c, MyApp.Employee e WHERE e.Employer = c.ID 

Also, this pair of relationship properties implicitly adds a foreign key to the employee table; the foreign key has UPDATE and DELETE both specified as NOACTION.

SQL Projection of Parent-Child Relationships

Similarly, consider the example parent-child classes shown earlier, which have a parent-child relationship between an invoice and its line items. In this case, the classes are projected as follows:

  • On the parent side (that is, in the invoice class), there is no field that represents the relationship. The invoice table has fields for other properties, but there is no field that holds the line items.

  • On the child side (that is, in the line item class), the relationship is a simple reference property, and that is projected to SQL in the same way as other reference properties. The line item table has a field named Invoice, which points to the invoice table.

  • Also on the child side, the IDs always include the ID of the parent record, even if you explicitly attempt to create an IDKey based exclusively on the child. Also, if the definition of the IDKey in the child class explicitly includes the parent relationship, the compiler recognizes this and does not add it again; this allows you to alter the sequence in which the parent reference appears as a subscript in the generated global references.

    As a consequence, it is not possible to add a bitmap index to this property, although other forms of index are permitted.

To query these tables together, you can query the invoice table and use arrow syntax, as in the following example:

SELECT 
Invoice->Buyer, Invoice->InvoiceDate, ID, ProductSKU, UnitPrice
FROM MyApp.LineItem

Or you can perform an explicit join, as in the following example:

SELECT 
i.Buyer, i.InvoiceDate, l.ProductSKU,l.UnitPrice 
FROM MyApp.Invoice i, MyApp.LineItem l 
WHERE i.ID = l.Invoice 

Also, for the class on the child side, the projected table is adopted as a child table of the other table.

Creating Many-to-Many Relationships

InterSystems IRIS does not directly support many-to-many relationships, but this section describes how to model such a relationship indirectly.

To establish a many-to-many relationship between class A and class B, do the following:

  1. Create a intermediate class that will define each relationship.

  2. Define a one-to-many relationship between that class and class A.

  3. Define a one-to-many relationship between that class and class B.

Then, create a record in the intermediate class for each relationship between an instance of class A and an instance of class B.

For example, suppose that class A defines doctors; this class defines the properties Name and Specialty. Class B defines patients; this class defines the properties Name and Address. To model the many-to-many relationship between doctors and patients, we could define an intermediate class as follows:

/// Bridge class between MN.Doctor and MN.Patient
Class MN.DoctorPatient Extends %Persistent
{

Relationship Doctor As MN.Doctor [ Cardinality = one, Inverse = Bridge ];

Index DoctorIndex On Doctor;

Relationship Patient As MN.Patient [ Cardinality = one, Inverse = Bridge ];

Index PatientIndex On Patient;
}

Then the doctor class looks like this:

Class MN.Doctor Extends %Persistent
{

Property Name;

Property Specialty;

Relationship Bridge As MN.DoctorPatient [ Cardinality = many, Inverse = Doctor ];

}

And the patient class looks like this:

Class MN.Patient Extends %Persistent
{

Property Name;

Property Address;

Relationship Bridge As MN.DoctorPatient [ Cardinality = many, Inverse = Patient ];

}

The easiest way to query both doctors and patients is to query the intermediate table. The following shows an example:

SELECT top 20 Doctor->Name as Doctor, Doctor->Specialty, Patient->Name as Patient 
FROM MN.DoctorPatient order by doctor
 
Doctor  Specialty       Patient
Davis,Joshua M. Dermatologist   Wilson,Josephine J.
Davis,Joshua M. Dermatologist   LaRocca,William O.
Davis,Joshua M. Dermatologist   Dunlap,Joe K.
Davis,Joshua M. Dermatologist   Rotterman,Edward T.
Davis,Joshua M. Dermatologist   Gibbs,Keith W.
Davis,Joshua M. Dermatologist   Black,Charlotte P.
Davis,Joshua M. Dermatologist   Dunlap,Joe K.
Davis,Joshua M. Dermatologist   Rotterman,Edward T.
Li,Umberto R.   Internist       Smith,Wolfgang J.
Li,Umberto R.   Internist       Ulman,Mo O.
Li,Umberto R.   Internist       Gibbs,Keith W.
Li,Umberto R.   Internist       Dunlap,Joe K.
Quixote,William Q.      Surgeon Black,Charlotte P.
Quixote,William Q.      Surgeon LaRocca,William O.
Quixote,William Q.      Surgeon Black,Charlotte P.
Quixote,William Q.      Surgeon Smith,Wolfgang J.
Quixote,William Q.      Surgeon LaRocca,William O.
Quixote,William Q.      Surgeon LaRocca,William O.
Quixote,William Q.      Surgeon Black,Charlotte P.
Salm,Jocelyn Q. Allergist       Tsatsulin,Mark S.

As a variation, you can use a parent-child relationship in place of one of the one-to-many relationships. This provides the physical clustering of the data, but it means that you cannot use a bitmap index on that relationship.

Variation with Foreign Keys

Rather than defining relationships between the intermediate class and classes A and B, you can use reference properties and foreign keys, so that the intermediate class MN.DoctorPatient looks like this instead of the version shown previously:

Class MN.DoctorPatient Extends %Persistent
{

Property Doctor As MN.Doctor;

ForeignKey DoctorFK(Doctor) References MN.Doctor();

Property Patient As MN.Patient;

ForeignKey PatientFK(Patient) References MN.Patient();

}

Foreign keys are discussed in more detail in Using Foreign Keys and Foreign Key Definitions.

One advantage to using a simple foreign key model is that no inadvertent swizzling of large numbers of objects will occur. One disadvantage is that no automatic swizzling is available.

See Also

FeedbackOpens in a new tab