blog.dennus.net A reflection on Microsoft Office SharePoint Server 2007 (and other stuff…)

SP2010 – Quick writeup about list referential integrity

There's a new and much requested feature in SP2010 and that's referential integrity on (custom) lists. This blog post is a quick writeup of that new feature just to document what I've found so far. Hopefully it's helpful for someone else as well.

Right, we have our SharePoint site which contains two custom lists:

- ParentList
- ChildList

ParentList has the following columns: Id, Title, Name, Address and City
ChildList has the following columns: Id, Title

We're going to use a lookup column to relate the two lists together, just like it works in SharePoint 2007.

Parent List with some sample data

The screenshot above shows the parent list, including some sample data we're going to display in our child list. First, we're going to add the lookup column to the client list, so we can relate the two lists.

Child List - New Lookup column

Nothing new here

Lookup Column Additional Info

The thing that's new here, if I recall correctly, is that you can now select multiple columns from the parent last that you want to include in the child list. I've selected to display the Title, Name, Address and City of the parent list, as you can see in the screenshot. The screenshot shows a relationship on ParentList:ID, but I've changed that to Title in my own setup.

The new feature is depicted in the screenshot below. This feature allows you to specify whether you want to enforce a relationship on this data and what kind of action should be taken when an item in the parent list is deleted. In our example I'm going to restrict the deletion, which I'll demonstrate later. You could select cascade delete, and the item in the child list will be deleted upon deletion of the parent item, just like in a relational database. 

Lookup Column Relationship

However, when you try to add the column, SharePoint will likely come up with an alert, saying that the parent column needs to be indexed before the relationship can be enforced.

Lookup column index

Just click OK here, to index the column. So, now that we've added the lookup column, the additional columns we've selected, are also displayed in the child list, like this:

Client list after adding the lookup column

You can see here that the additional columns have been added in a format of <lookup column name>:<parent column name>, so this allows for easy distinction between columns that are coming from the parent list and the ones that exist on the child list.

Adding a new item will result in the following form:

New client item

 
As you can see here, you can select the parent item, which is nothing new obviously. The client list will look like this once the item has been added.

Client list - item added

So, there you see the info that comes from the parent list. Now, let's try to delete the linked item in the parent list to see what will happen.

Delete parent item

As expected, you will get an error message, indicating that you can't delete the item because of a relationship constraint between the items. The error could have been a bit more user friendly I suppose, but I've learned to expect stuff like this from SharePoint

Parent list item delete error

So, there you go, some quick information about relational integrity in SharePoint 2010. I think this should help us all utilize custom lists for scenarios where you'd rather use a database. Obviously, using a regular database brings a lot more advantages, but it's really nice to have this functionality in SharePoint now, without having to code your own event receivers and stuff.