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 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.
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.
Nothing new here
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.Â
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.
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:
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:
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.
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.
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
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.