List Relationships in SharePoint 2010

SharePoint 2010 allows you to create relationships between lists in the same site collection. List instances are related through lookup columns (as known as lookup fields). The real benefit of this functionality is that SharePoint 2010 allows you to use join statements, in LINQ to SharePoint or in collaborative application markup language (CAML), to query across lists where lookup column relationships are defined. By default, SharePoint permits a maximum of eight joins per query, although administrators can change this limit through the Central Administration Web site or by using PowerShell. However, queries that contain large numbers of join statements are resource-intensive, and exceeding eight joins per query is likely to have a significant detrimental effect on performance.

Although this newly introduced ability to query across lists brings the capabilities of SharePoint data models closer to those of relational databases, SharePoint support join predicates only where a lookup column relationship exists between the lists. In this regard, the join functionality in SharePoint is less powerful than the JOIN predicate in SQL.

Lookup Columns Explained

Suppose you use a relational database to manage goods orders received from your customers. The database might include a table named Orders, which stores the orders you've received, and a table named OrderLines, which stores the individual items that comprise an order.

Unrelated database tables

To relate the tables, you could add an OrderID column to the OrderLines table, and use this column to define a foreign key relationship between the tables.

Database tables linked by foreign key constraint (primary key)

Alternatively, you could add an OrderNo column to the OrderLines table, and use this column to define the foreign key relationship (providing that the OrderNo column in the Orders table is subject to a unique values constraint).

Database tables linked by foreign key constraint

Defining the foreign key relationship helps to ensure referential integrity—in the first example, the OrderID column of the OrderLines table can only contain values that are found in the ID column in the Orders table. You can also impose further conditions on the foreign key relationship. For example, when an item is deleted from the Orders table, you can force the database engine to remove corresponding rows from the OrderLines table. Conversely, you can prohibit the deletion of OrderLines items that are linked to an active row in the Orders table.

Lookup column relationships in SharePoint are conceptually similar to foreign key constraints in relational databases, but there are key differences. Suppose you want to implement the previous example in a SharePoint data model. First, you create the Orders list. Next, you define a site lookup column that retrieves values from the Orders list. Finally, you create the OrderLines list and you add the lookup column that retrieves values from Orders. When a user enters a new order line to the OrderLines list, they would select the associated order using the lookup column. You don't get to choose which columns in the Orders or OrderLines lists drive the foreign key constraint—in SharePoint lists, you can view the built in ID column as a permanent, unchangeable primary key; and this is the value that drives the relationship. Instead, you choose the column in the target list that you want to display in the source list, by setting the ShowField attribute. When a user adds data to the source list, he or she can select from a list of values in the column you selected on the target list. The following illustration shows this.

Lookup column relationship between SharePoint lists

Another key difference is that in a relational database, you can apply a foreign key constraint to existing data. This is not always good practice, and you would need to take care to remedy any existing data rows that violate the constraint. However, in SharePoint, you do not have this option—you cannot convert an existing column to a lookup column. You must create the lookup column first, and then the user must populate the data by selecting values from the target list. Note that a lookup column definition in itself does not define a relationship until you add it to a list. For example, you can create a lookup field as a site column. The lookup column definition effectively defines one half of the relationship.

Lookup Column Definition

Whenever you add the site column to a list, you effectively create a unique foreign key relationship between the source list and the target list. In the case of lookup columns, the relationship between lists is managed by SharePoint, not by the underlying database. You can also leave a lookup column blank unless it is marked as a required field, whereas a foreign key constraint always requires a value.

If you want to model a many-to-many relationship using SharePoint lists, you must create an intermediate list to normalize the relationship. This is conceptually similar to the normalization process in database design, where you would also use an intermediate table to model a many-to-many relationship. For example, suppose you want to model the relationship between parts and machines. A part can be found in many machines, and a machine can contain many parts. To normalize the relationship, you would create an intermediate list named PartMachine, as shown in the following illustration.

Using an intermediate list to model a many-to-many relationship

In this example, the intermediate list, PartMachine, contains lookup columns that link to both lists. To create a relationship between a part and a machine, you would need to create an entry in the PartMachine list. To navigate from a part to a machine, or vice versa, you would have to browse through the PartMachine list. From a user experience point of view, this is less than ideal, so at this point, you would probably add custom logic and custom user interface components to maintain associations between parts and machines.

Relationships between SharePoint lists can be navigated programmatically using either CAML or LINQ to SharePoint. For more information, see Data Access in SharePoint 2010.

Creating and Using Lookup Columns

You can create lookup columns in three different ways—interactively through the SharePoint site settings user interface (UI), programmatically through the SPFieldLookup class or declaratively through CAML. For example, the following CAML code declaratively defines a lookup column named Projects Lookup that returns items from a list named Projects.

<Field ID="{3F55B8CF-3537-4488-B250-02914EE6B4A8}"
Name="ProjectsLookup"
       DisplayName="Projects Lookup"
       StaticName="ProjectsLookup"
       DisplaceOnUpgrade="TRUE"
Group="SiteColumns" 
       ShowField="Title"
       WebId=""
List="Lists/Projects"
Type="Lookup"
Required="TRUE">
</Field>

The attributes of interest for a lookup column are as follows:

• The value of the Type attribute must be set to Lookup.
• The WebId attribute specifies the internal name of the site that contains the target list. If the attribute is omitted or set to an empty string, SharePoint will assume that the list is on the root site of the site collection.
• The List attribute specifies the site-relative URL of the target list. This list instance must exist before the field is defined.
• The ShowField attribute specifies the column in the target list that contains the values that you want to display in the lookup column.

Note

Consider picking a meaningful ShowField value for the lookup column that is unlikely to change. For example, choosing a product SKU or a model number is a better foundation for a relationship than a description field.

List Columns, Site Columns, and Content Types

You can define a lookup column as a site column or a list column. If you define a lookup column declaratively or programmatically, you must take care to ensure that the target list (in other words, the list that the column refers to) is in place at the appropriate point in the column provisioning process. Regardless of whether you define a lookup column as a site column or a list column, the target list must already exist at the point at which the column is created—otherwise, the column will be unusable. Similarly, if you define a lookup column as part of a list definition, the target list must already exist at the point at which you provision the list containing the lookup column.

Because a lookup column defines a relationship between two list instances, it can often make sense to define your lookup columns at the list level. There are some circumstances where it makes sense to use site columns and content types. For example, if many similar lists within the same site collection will include a relationship to a list on the root site, you can define a site column for the lookup, include it in a content type, and provision the content type to multiple lists within the site collection.

Projected Fields

In addition to the column you identify in the ShowField attribute, SharePoint 2010 enables you to display additional columns from the target list in the view of the list that contains the lookup column. These additional columns are known as projected fields. For example, suppose you use SharePoint lists to model the relationship between employees and their departments. You create a Department lookup column for the Employees list. You might also want to display the name of the department manager in the list of employees, as shown in the following illustration.

Projected fields in SharePoint lists

Note

This is a somewhat contrived example, because, in reality, the department manager may not be the employee's manager, and the department manager would also be a member of the employees table. However, it serves to illustrate the concept of projected fields.

Enforcing List Relationships

SharePoint 2010 can help you to maintain referential integrity in your data model by enforcing the relationships defined by lookup columns. Just like foreign key constraints in a relational database, SharePoint allows you to configure restrict delete and cascade delete rules on lookup column relationships:

• Cascade delete rules automatically delete items that reference a record when you delete that record. This rule is typically used to enforce parent-child relationships.
• Restrict delete rules prevent you from deleting a record that is referenced by a lookup column in another list. This rule is typically used to enforce peer-to-peer relationships.

Parent-Child Relationships and Cascade Delete Rules

In a parent-child relationship, some records are children of other records. The child records are meaningless without a valid parent record. For example, an invoicing system might model a relationship between invoices and invoice line items. The invoice line items, which describe the product purchased, quantity ordered, and unit cost, are child records of the invoice item, which describes the customer, the shipping address, the total cost, and so on.

To create the relationship between invoices and invoice line items, you would add a lookup column to the InvoiceLineItems list that retrieves an invoice number from the Invoices list. Because an invoice line item is meaningless without a parent invoice, it would be appropriate to configure the lookup column to cascade deletes. This ensures that when a user deletes an invoice, SharePoint will delete the corresponding line items.

Child items without a valid parent are often referred to as orphans. Cascade delete behavior is designed to prevent orphans from occurring.

Peer-to-Peer Relationships and Restrict Delete Rules

In a peer-to-peer relationship, there is no clear hierarchy between the related entities. For example, in our invoice line items list, you might want to include a lookup column that retrieves an item from a list of products. In this case, the product is neither child to nor parent of the invoice line item—the product lookup simply provides additional information on the invoice line.

Suppose you delete a product record because you no longer sell that product. This damages the referential integrity of your data model, because historical invoice line items will now reference product records that do not exist. A better approach is to mark the product record as inactive if it is no longer available for distribution. In this case, it would be appropriate to configure the product lookup column in the InvoiceLineItem list to restrict deletes. This ensures that a product record cannot be deleted if it has been referenced in an invoice line item.

Making a record inactive while ensuring that it is still accessible for consistency with historical records is often referred to as a soft delete.

You can configure list relationship behavior interactively through the user interface or programmatically in a feature receiver class. Because list relationship behavior is specific to individual list instances, you cannot configure this behavior declaratively in column definitions or content types. For details of how to configure cascade delete and restrict delete rules programmatically, see How to: Programmatically Set the Delete Behavior on a Lookup Field.