Implementing Relationships between SharePoint Lists

  • Article

In SharePoint 2010, lookup columns form the basis for relationships between lists. In the SharePoint List Data Models reference implementation, the Site Columns and Content Types feature manifest defines several lookup columns as site columns. For example, the following code defines a lookup field on the Machines list.

<Field Type="Lookup" 
       DisplayName="Machine" 
       Required="TRUE" 
       EnforceUniqueValues="FALSE" 
       List="Lists/Machines" 
       WebId="" 
       ShowField="Title" 
       UnlimitedLengthInDocumentLibrary="FALSE"
       Group="Parts Database Columns" 
       ID="{322e5c46-da10-4948-b3ab-dc657bc51a4a}" 
       Name="MachineLookup" 
       Overwrite="TRUE" />

Clearly the site column alone will not define a relationship until you add it to a list. If you refer to the entity-relationship diagram in Solution Overview, you can see that both the MachineParts list and the MachineDepartments list use this lookup column to relate to the Machines list. When you define a lookup field as a site column, the list that the column refers to—the Machines list in this case—must already exist at the point at which you add the lookup column to the site collection. This can be confusing, as although the list exists, it will contain only the default Title field as we have yet to create and deploy the content types that define the fields of interest. For this reason, the order in which you deploy the data model components is important:

  1. Provision the list instances. At this point, all list instances will contain only the default Title field.
  2. Provision the site columns.
  3. Provision the content types that reference the site columns.
  4. Bind the content types to the appropriate lists.

Modeling Many-to-Many Relationships

You can use lookup columns to model one-to-many relationships or one-to-one relationships between lists. However, if you require a many-to-many relationship between entities in your data model, lookup columns alone are insufficient for defining the relationship. While you could use custom user interface components to manage the relationship, a better approach is to normalize the data model. Just as you would add a join table to normalize a many-to-many relationship in a relational database, you can add a join list to normalize a many-to-many relationship between SharePoint lists. For example, in our parts management data model, there is a many-to-many relationship between Parts and Machines. A part can be found in many machines, and a machine contains many parts. To normalize the relationship, we add a join list, MachineParts, which links the two tables through one-to-many relationships. The MachineParts list does not represent a physical entity—it exists purely to link items in the Parts list to items in the Machines list, by providing lookup columns to both lists. The following code sample shows how these lookup columns are referenced in the Machine Part content type.

<ContentType ID="0x0100220B06426A421E41A0CA50F1FA1F421F" 
             Name="Machine Part" 
             Group="Parts Database" 
             Overwrite="TRUE" 
             xmlns="https://schemas.microsoft.com/sharepoint/">
  <Folder TargetName="_cts/Machine Part" />
    <FieldRefs>
      <FieldRef ID="{c042a256-787d-4a6f-8a8a-cf6ab767f12d}" 
                Name="ContentType" />
      <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" 
                Name="Title" Hidden="TRUE" 
                ShowInNewForm="TRUE" ShowInEditForm="TRUE" ReadOnly="FALSE" 
                PITarget="" PrimaryPITarget="" PIAttribute="" 
                PrimaryPIAttribute="" Aggregation="" Node="" />
      <FieldRef ID="{322e5c46-da10-4948-b3ab-dc657bc51a4a}" 
                Name="MachineLookup" 
                Required="TRUE" Hidden="FALSE" ReadOnly="FALSE" 
                PITarget="" PrimaryPITarget="" PIAttribute="" 
                PrimaryPIAttribute="" Aggregation="" Node="" />
      <FieldRef ID="{4962bb01-d4a4-409d-895c-fd412baa8293}" 
                Name="PartSKULookup" 
                Required="TRUE" Hidden="FALSE" ReadOnly="FALSE" 
                PITarget="" PrimaryPITarget="" PIAttribute="" 
                PrimaryPIAttribute="" Aggregation="" Node="" />
    </FieldRefs>
    ...
</ContentType>

Note

While you could allow end users to define relationships between parts and machines by manually populating the MachineParts list, this approach provides a poor user experience. A better approach is to develop custom user interface components, which we describe in more detail later in this documentation.

Each entry in the MachineParts list represents a relationship between a specific part and a specific machine. To maintain the integrity of these relationships, each entry in the MachineParts list must be unique. As such, the Machine Part content type registers a list event receiver class for the ItemAdding and ItemUpdating events, which ensures that new or updated list items are unique. The following code example shows the relevant part of the content type definition.

<XmlDocument NamespaceURI="https://schemas.microsoft.com/sharepoint/events">
  <spe:Receivers xmlns:spe="https://schemas.microsoft.com/sharepoint/events">
    <Receiver>
      <Name>List Item Adding</Name>
      <Type>ItemAdding</Type>
      <SequenceNumber>1</SequenceNumber>
      <Assembly>DataModels.SharePointList.Model, Version=1.0.0.0, Culture=neutral, 
                PublicKeyToken=acb84d90d3a9b0ad</Assembly>
      <Class>DataModels.SharePointList.Model.ListEventReceivers
             .UniqueListItemEventReceiver</Class>
      <Data>MachineLookup;PartLookup</Data>
      <Filter />
    </Receiver>
    <Receiver>
      <Name>List Item Updating</Name>
      <Type>ItemUpdating</Type>
      <SequenceNumber>1</SequenceNumber>
      <Assembly>DataModels.SharePointList.Model, Version=1.0.0.0, Culture=neutral, 
                PublicKeyToken=acb84d90d3a9b0ad</Assembly>
      <Class>DataModels.SharePointList.Model.ListEventReceivers
             .UniqueListItemEventReceiver</Class>
      <Data>MachineLookup;PartLookup</Data>
      <Filter />
    </Receiver>
  </spe:Receivers>
</XmlDocument>

When you work with a database, you can impose a uniqueness constraint on a composite key. This constraint is applied automatically if the composite key is the primary key for the table. However, SharePoint lists have no equivalent notion of a uniqueness constraint on a composite key. To ensure that duplicate entries are not made between the machine and parts table, the UniqueListItemEventReceiver class ensures that the composite key of the new or amended MachineParts list item—in other words, a concatenation of the part SKU and the machine ID—does not already exist in the list. If it does, the event receiver will cancel the operation and return an error message.

Note

For more information on modeling relationships between SharePoint lists, see List Relationships in SharePoint 2010.

Maintaining Referential Integrity in a SharePoint List Data Model

In the same way that you can use foreign key constraints to specify delete behavior across tables in a relational database, SharePoint 2010 allows you to specify delete behavior across list relationships. You can configure lookup columns to manage deletions in two ways.

  • You can apply a cascade delete rule. In this case, if a user deletes an item from a parent list, related items in the child list are also deleted. This helps to prevent orphaned items in the child list.
  • You can apply a restrict delete rule. In this case, users are prevented from deleting an item that is referenced by items in a related list. This helps to prevent broken lookup links in the data model.

Because list relationships are formed between two specific list instances, you cannot declaratively specify delete behavior when you define a lookup field as a site column. If you wanted to declaratively specify delete behavior, you would need to define a custom schema.xml file for each list instance, which substantially increases the complexity of the development process. In the SharePoint List Data Models reference implementation, we use a feature receiver class to programmatically define the delete behavior for list relationships. The feature receiver class is associated with the Initialize Model (CT2LI) feature, so that we set the delete behavior after we bind each content type to its associated list. In the CT2LI.EventReceiver class, the FeatureActivated method applies a restrict delete rule to lookup columns in several list instances, as shown by the following code example.

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
  try
  {
    ...

    //Restrict deletion of list items that would create a broken lookup
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.InventoryLocations, 
                                Constants.Fields.Guids.Part);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.Machines, 
                                Constants.Fields.Guids.Manufacturer);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.Machines, 
                                Constants.Fields.Guids.Category);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.MachineDepartments, 
                                Constants.Fields.Guids.Department);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.MachineDepartments, 
                                Constants.Fields.Guids.Machine);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.MachineParts, 
                                Constants.Fields.Guids.Machine);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.MachineParts, 
                                Constants.Fields.Guids.Part);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.PartSuppliers, 
                                Constants.Fields.Guids.Part);
    RestrictDeleteOnLookupField(rootWeb, Constants.ListUrls.PartSuppliers, 
                                Constants.Fields.Guids.Supplier);
    ...
  }
  catch (Exception e) { System.Diagnostics.Trace.WriteLine(e.ToString()); }
}

The RestrictDeleteOnLookupField helper method retrieves the SPField instance that represents the lookup column, and then sets the RelationshipDeleteBehavior property to the SPRelationshipDeleteBehavior.Restrict enumeration value.

private void RestrictDeleteOnLookupField(SPWeb web, string listUrl, 
                                         Guid fieldGuid)
{
  SPList list = web.GetList(GetListUrl(web.ServerRelativeUrl, listUrl));
  SPField field = list.Fields[fieldGuid];
  SPFieldLookup fieldLookup = (SPFieldLookup)field;
  fieldLookup.Indexed = true;
  fieldLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
  fieldLookup.Update();
}

Note

The SPRelationshipDeleteBehavior enumeration provides three values—None, Cascade, and Restrict. The default value is None. For more information on specifying delete rules, see List Relationships in SharePoint 2010.